"CASE" is not a variable

From: "Keith Worthington" <keithw(at)narrowpathinc(dot)com>
To: "PostgreSQL SQL" <pgsql-sql(at)postgresql(dot)org>
Subject: "CASE" is not a variable
Date: 2006-06-28 16:00:20
Message-ID: 20060628154728.M33440@narrowpathinc.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

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

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Pedro B. 2006-06-28 16:00:24 generate_series with left join
Previous Message deb.vanni 2006-06-28 15:56:31 unique values of profile in the whole system