pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feed"CASE" is not a variable
6+ messages / 4 participants
[nested] [flat]
* "CASE" is not a variable
@ 2006-06-28 16:00 Keith Worthington <keithw@narrowpathinc.com>
2006-06-28 16:16 ` Re: "CASE" is not a variable Tom Lane <tgl@sss.pgh.pa.us>
2006-06-29 08:00 ` Re: "CASE" is not a variable Patrick Jacquot <patrick.jacquot@anpe.fr>
0 siblings, 2 replies; 6+ messages in thread
From: Keith Worthington @ 2006-06-28 16:00 UTC (permalink / raw)
To: 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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: "CASE" is not a variable
2006-06-28 16:00 "CASE" is not a variable Keith Worthington <keithw@narrowpathinc.com>
@ 2006-06-28 16:16 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-06-28 17:34 ` Re: "CASE" is not a variable Keith Worthington <keithw@narrowpathinc.com>
1 sibling, 1 reply; 6+ messages in thread
From: Tom Lane @ 2006-06-28 16:16 UTC (permalink / raw)
To: KeithW@narrowpathinc.com; +Cc: pgsql-sql
"Keith Worthington" <keithw@narrowpathinc.com> writes:
> The following is a section of code inside an SQL function.
SQL, or plpgsql? It looks to me like misuse of the plpgsql INTO clause
(there can be only one).
regards, tom lane
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: "CASE" is not a variable
2006-06-28 16:00 "CASE" is not a variable Keith Worthington <keithw@narrowpathinc.com>
2006-06-28 16:16 ` Re: "CASE" is not a variable Tom Lane <tgl@sss.pgh.pa.us>
@ 2006-06-28 17:34 ` Keith Worthington <keithw@narrowpathinc.com>
2006-06-28 17:48 ` Re: "CASE" is not a variable Bricklen Anderson <banderson@presinet.com>
0 siblings, 1 reply; 6+ messages in thread
From: Keith Worthington @ 2006-06-28 17:34 UTC (permalink / raw)
To: pgsql-sql
> > "Keith Worthington" <keithw@narrowpathinc.com> writes:
> > The following is a section of code inside an SQL function.
>
> On Wed, 28 Jun 2006 12:16:29 -0400, Tom Lane wrote
> SQL, or plpgsql? It looks to me like misuse of the plpgsql INTO clause
> (there can be only one).
>
> regards, tom lane
plpgsql
This is part of a function inside a v8.0.2 database.
I didn't realize that the INTO clause was only limited to one instance. I was
trying to accomplish
SELECT col_a INTO var1,
col_b INTO var2,
col_c INTO var3,
...
FROM foo
WHERE fobar;
Kind Regards,
Keith
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: "CASE" is not a variable
2006-06-28 16:00 "CASE" is not a variable Keith Worthington <keithw@narrowpathinc.com>
2006-06-28 16:16 ` Re: "CASE" is not a variable Tom Lane <tgl@sss.pgh.pa.us>
2006-06-28 17:34 ` Re: "CASE" is not a variable Keith Worthington <keithw@narrowpathinc.com>
@ 2006-06-28 17:48 ` Bricklen Anderson <banderson@presinet.com>
2006-07-05 18:08 ` Re: "CASE" is not a variable Keith Worthington <keithw@narrowpathinc.com>
0 siblings, 1 reply; 6+ messages in thread
From: Bricklen Anderson @ 2006-06-28 17:48 UTC (permalink / raw)
To: KeithW@narrowpathinc.com; +Cc: pgsql-sql
Keith Worthington wrote:
>>> "Keith Worthington" <keithw@narrowpathinc.com> writes:
>>> The following is a section of code inside an SQL function.
>> On Wed, 28 Jun 2006 12:16:29 -0400, Tom Lane wrote
>> SQL, or plpgsql? It looks to me like misuse of the plpgsql INTO clause
>> (there can be only one).
>>
>> regards, tom lane
>
> plpgsql
>
> This is part of a function inside a v8.0.2 database.
>
> I didn't realize that the INTO clause was only limited to one instance. I was
> trying to accomplish
>
> SELECT col_a INTO var1,
> col_b INTO var2,
> col_c INTO var3,
> ...
> FROM foo
> WHERE fobar;
>
> Kind Regards,
> Keith
try it like
select col_a,col_b,col_c INTO va1,var2,var3 ....
not sure if 8.0.2 allows you to do that, however.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: "CASE" is not a variable
2006-06-28 16:00 "CASE" is not a variable Keith Worthington <keithw@narrowpathinc.com>
2006-06-28 16:16 ` Re: "CASE" is not a variable Tom Lane <tgl@sss.pgh.pa.us>
2006-06-28 17:34 ` Re: "CASE" is not a variable Keith Worthington <keithw@narrowpathinc.com>
2006-06-28 17:48 ` Re: "CASE" is not a variable Bricklen Anderson <banderson@presinet.com>
@ 2006-07-05 18:08 ` Keith Worthington <keithw@narrowpathinc.com>
0 siblings, 0 replies; 6+ messages in thread
From: Keith Worthington @ 2006-07-05 18:08 UTC (permalink / raw)
To: pgsql-sql
On Wed, 28 Jun 2006 10:48:31 -0700, Bricklen Anderson wrote
> Keith Worthington wrote:
> >>> "Keith Worthington" <keithw@narrowpathinc.com> writes:
> >>> The following is a section of code inside an SQL function.
> >> On Wed, 28 Jun 2006 12:16:29 -0400, Tom Lane wrote
> >> SQL, or plpgsql? It looks to me like misuse of the plpgsql INTO clause
> >> (there can be only one).
> >>
> >> regards, tom lane
> >
> > plpgsql
> >
> > This is part of a function inside a v8.0.2 database.
> >
> > I didn't realize that the INTO clause was only limited to one instance. I was
> > trying to accomplish
> >
> > SELECT col_a INTO var1,
> > col_b INTO var2,
> > col_c INTO var3,
> > ...
> > FROM foo
> > WHERE fobar;
> >
> > Kind Regards,
> > Keith
>
> try it like
> select col_a,col_b,col_c INTO va1,var2,var3 ....
>
> not sure if 8.0.2 allows you to do that, however.
Apparently it does. Just to wrap up this thread and hopefully help the next
person. When SELECTing multiple values into variables only use one instance of
the INTO clause. i.e.
SELECT col_a,
col_b,
col_c
INTO var1,
var2,
var3
FROM foo
WHERE fobar;
Kind Regards,
Keith
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: "CASE" is not a variable
2006-06-28 16:00 "CASE" is not a variable Keith Worthington <keithw@narrowpathinc.com>
@ 2006-06-29 08:00 ` Patrick Jacquot <patrick.jacquot@anpe.fr>
1 sibling, 0 replies; 6+ messages in thread
From: Patrick Jacquot @ 2006-06-29 08:00 UTC (permalink / raw)
To: KeithW@narrowpathinc.com; +Cc: 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
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2006-07-05 18:08 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-06-28 16:00 "CASE" is not a variable Keith Worthington <keithw@narrowpathinc.com>
2006-06-28 16:16 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-06-28 17:34 ` Keith Worthington <keithw@narrowpathinc.com>
2006-06-28 17:48 ` Bricklen Anderson <banderson@presinet.com>
2006-07-05 18:08 ` Keith Worthington <keithw@narrowpathinc.com>
2006-06-29 08:00 ` Patrick Jacquot <patrick.jacquot@anpe.fr>
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