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