Thread: "CASE" is not a variable

"CASE" is not a variable

From
"Keith Worthington"
Date:
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                (
CASEWHEN 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_sizeFROM sales_order.tbl_item_bom LEFT JOIN peachtree.tbl_mesh   ON tbl_item_bom.item_id = tbl_mesh.item_idWHERE
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


Re: "CASE" is not a variable

From
Tom Lane
Date:
"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


Re: "CASE" is not a variable

From
"Keith Worthington"
Date:
> > "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 fooWHERE fobar;

Kind Regards,
Keith


Re: "CASE" is not a variable

From
Bricklen Anderson
Date:
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.


Re: "CASE" is not a variable

From
Patrick Jacquot
Date:
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


Re: "CASE" is not a variable

From
"Keith Worthington"
Date:
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 fooWHERE fobar;

Kind Regards,
Keith