From: | Patrick Jacquot <patrick(dot)jacquot(at)anpe(dot)fr> |
---|---|
To: | KeithW(at)narrowpathinc(dot)com |
Cc: | PostgreSQL SQL <pgsql-sql(at)postgresql(dot)org> |
Subject: | Re: "CASE" is not a variable |
Date: | 2006-06-29 08:00:37 |
Message-ID: | 44A388A5.3010803@anpe.fr |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Anthony Kinyage | 2006-06-29 13:40:23 | Data Entry and Query forms |
Previous Message | Penchalaiah P. | 2006-06-29 06:28:23 | can any one solve this problem |