Обсуждение: "CASE" is not a variable
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
"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
> > "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
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.
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
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