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
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 |