X-Original-To: pgsql-sql-postgresql.org@postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id 45E159FA50D for ; Thu, 29 Jun 2006 05:00:53 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 26622-07 for ; Thu, 29 Jun 2006 05:00:44 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from smtp3.lc.anpe.fr (smtp3.lc.anpe.fr [80.118.163.18]) by postgresql.org (Postfix) with ESMTP id 3A32F9FA441 for ; Thu, 29 Jun 2006 05:00:43 -0300 (ADT) Received: from HOMERE03.anpe.fr (localhost.localdomain [127.0.0.1]) by smtp3.lc.anpe.fr (antivirus) with ESMTP id 6C20F73D30; Thu, 29 Jun 2006 10:00:40 +0200 (CEST) Received: from [10.0.32.170] ([10.0.32.170]) by HOMERE03.anpe.fr with Microsoft SMTPSVC(6.0.3790.1830); Thu, 29 Jun 2006 10:00:10 +0200 Message-ID: <44A388A5.3010803@anpe.fr> Date: Thu, 29 Jun 2006 10:00:37 +0200 From: Patrick Jacquot User-Agent: Mozilla Thunderbird 1.0 (Windows/20041206) X-Accept-Language: en-us, en MIME-Version: 1.0 To: KeithW@narrowpathinc.com Cc: PostgreSQL SQL Subject: Re: "CASE" is not a variable References: <20060628154728.M33440@narrowpathinc.com> In-Reply-To: <20060628154728.M33440@narrowpathinc.com> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-OriginalArrivalTime: 29 Jun 2006 08:00:10.0582 (UTC) FILETIME=[0B4C6F60:01C69B52] X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200606/312 X-Sequence-Number: 25548 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