X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 10578564EF for ; Mon, 21 Mar 2005 14:41:00 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 11772-01 for ; Mon, 21 Mar 2005 14:40:51 +0000 (GMT) Received: from bugs.unl.edu.ar (bugs.unl.edu.ar [168.96.132.208]) by svr1.postgresql.org (Postfix) with ESMTP id 4582D564A4 for ; Mon, 21 Mar 2005 14:40:47 +0000 (GMT) Received: from localhost (localhost [127.0.0.1]) by bugs.unl.edu.ar (Postfix) with ESMTP id 795391206E; Mon, 21 Mar 2005 11:40:23 -0300 (ART) Received: from bugs.unl.edu.ar ([127.0.0.1]) by localhost (bugs [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 23689-03; Mon, 21 Mar 2005 11:40:22 -0300 (ART) Received: from localhost (localhost [127.0.0.1]) by bugs.unl.edu.ar (Postfix) with ESMTP id BE06C1206D; Mon, 21 Mar 2005 11:40:22 -0300 (ART) From: =?iso-8859-1?q?Mart=EDn_Marqu=E9s?= To: Alvaro Herrera Subject: Re: CASE not working Date: Mon, 21 Mar 2005 11:40:15 -0300 User-Agent: KMail/1.7.1 Cc: pgsql-sql@postgresql.org References: <200503211118.39223.martin@bugs.unl.edu.ar> <20050321142931.GA11919@dcc.uchile.cl> In-Reply-To: <20050321142931.GA11919@dcc.uchile.cl> MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Content-Disposition: inline Message-Id: <200503211140.16151.martin@bugs.unl.edu.ar> X-Virus-Scanned: by amavisd-new-20030616-p10 (Debian) at bugs.unl.edu.ar X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.137 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200503/286 X-Sequence-Number: 20765 El Lun 21 Mar 2005 11:29, Alvaro Herrera escribi=F3: > On Mon, Mar 21, 2005 at 11:18:38AM -0300, Mart=EDn Marqu=E9s wrote: >=20 > Hey Martin, >=20 > > I have this query which has a CASE in the middle to give me special=20 results.=20 > > The problem is that it doesn't interpret my columns as it should. > >=20 > > Here is the porblem: > >=20 > > siprebi=3D> SELECT getvencimientosancion(190) AS vence, (SELECT codigo = =46ROM=20 > > sanciones WHERE persona =3D (SELECT persona FROM usuarios WHERE codigo= =3D=20 190)=20 > > ORDER BY femodif DESC LIMIT 1) AS sancion_original, CASE WHEN=20 vence>=3Dnow()=20 > > THEN 1 ELSE 0 END AS sancionado; > > ERROR: no existe la columna "vence" >=20 > The problem is that the "vence" alias is not available at the time the > CASE is evaluated. You need to use the getvencimientosancion() > function, or put it in a subselect in case it's expensive to compute (or > has side effects). Yes, I was all tied up trying to make the subselect, and didn't see the=20 simplicity of it. :-) siprebi=3D> SELECT *,CASE WHEN s1.vence>=3Dnow() THEN 1 ELSE 0 END AS sanc= ionado=20 =46ROM (SELECT getvencimientosancion(190) AS vence, (SELECT codigo FROM=20 sanciones WHERE persona =3D (SELECT persona FROM usuarios WHERE codigo =3D= 190)=20 ORDER BY femodif DESC LIMIT 1) AS sancion_original) s1; vence | sancion_original | sancionado =2D-----------+------------------+------------ 20/03/2005 | | 0 (1 row) Txs. =2D-=20 11:39:04 up 2 days, 16:09, 3 users, load average: 1.05, 0.81, 0.74 =2D---------------------------------------------------------------- Mart=EDn Marqu=E9s | select 'mmarques' || '@' || 'unl.edu.ar' Centro de Telematica | DBA, Programador, Administrador Universidad Nacional del Litoral =2D----------------------------------------------------------------