Keith Worthington wrote:
>Hi All,
>
>The following is a section of code inside an SQL function. When I attempt to
>run it I get the error message '"CASE" is not a variable'. If I split this into
>two queries (one for each variable) it works fine. Obviously I have a work
>around but I would like to understand what I am doing wrong. TIA
>
>SELECT tbl_item_bom.so_subline INTO v_so_subline,
> CASE WHEN tbl_mesh.mesh_type = 'square' THEN
> ( CASE WHEN tbl_mesh.unit_of_measure = 'in' THEN
>tbl_mesh.mesh_size
> WHEN tbl_mesh.unit_of_measure = 'ft' THEN 12.0 *
>tbl_mesh.mesh_size
> WHEN tbl_mesh.unit_of_measure = 'mm' THEN 25.4 *
>tbl_mesh.mesh_size
> WHEN tbl_mesh.unit_of_measure = 'cm' THEN 2.54 *
>tbl_mesh.mesh_size
> WHEN tbl_mesh.unit_of_measure = 'm' THEN 0.0254 *
>tbl_mesh.mesh_size
> ELSE 0
> END
> )
> WHEN tbl_mesh.mesh_type = 'diamond' THEN
> ( CASE WHEN tbl_mesh.unit_of_measure = 'in' THEN
>tbl_mesh.mesh_size / 2.0
> WHEN tbl_mesh.unit_of_measure = 'ft' THEN 12.0 *
>tbl_mesh.mesh_size / 2.0
> WHEN tbl_mesh.unit_of_measure = 'mm' THEN 25.4 *
>tbl_mesh.mesh_size / 2.0
> WHEN tbl_mesh.unit_of_measure = 'cm' THEN 2.54 *
>tbl_mesh.mesh_size / 2.0
> WHEN tbl_mesh.unit_of_measure = 'm' THEN 0.0254 *
>tbl_mesh.mesh_size / 2.0
> ELSE 0
> END
> )
> ELSE 0
> END INTO v_mesh_size
> FROM sales_order.tbl_item_bom
> LEFT JOIN peachtree.tbl_mesh
> ON tbl_item_bom.item_id = tbl_mesh.item_id
> WHERE tbl_item_bom.so_number = rcrd_line.so_number
> AND tbl_item_bom.so_line = rcrd_line.so_line
> AND tbl_item_bom.component_type = 'net';
>
>Kind Regards,
>Keith
>
>---------------------------(end of broadcast)---------------------------
>TIP 9: In versions below 8.0, the planner will ignore your desire to
> choose an index scan if your joining column's datatypes do not
> match
>
>
>
shouldn't your CASE construct be in the select list, i.e
SELECT tbl_item_bom_so.subline, CASE ... END INTO ... FROM ...WHERE ...
?
HTH
--
Patrick
