Re: [SQL] "CASE" is not a variable

[Patrick Jacquot]

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

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster