From: Patrick Jacquot <patrick.jacquot@anpe.fr>
To: KeithW@narrowpathinc.com
Cc: PostgreSQL SQL <pgsql-sql@postgresql.org>
Subject: Re: "CASE" is not a variable
Date: Thu, 29 Jun 2006 10:00:37 +0200
Message-ID: <44A388A5.3010803@anpe.fr> (raw)
In-Reply-To: <20060628154728.M33440@narrowpathinc.com>
References: <20060628154728.M33440@narrowpathinc.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: patrick.jacquot@anpe.fr, KeithW@narrowpathinc.com
Subject: Re: "CASE" is not a variable
In-Reply-To: <44A388A5.3010803@anpe.fr>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox