From: Martín Marqués <martin@bugs.unl.edu.ar>
To: Alvaro Herrera <alvherre@dcc.uchile.cl>
Cc: pgsql-sql@postgresql.org
Subject: Re: CASE not working
Date: Mon, 21 Mar 2005 11:40:15 -0300
Message-ID: <200503211140.16151.martin@bugs.unl.edu.ar> (raw)
In-Reply-To: <20050321142931.GA11919@dcc.uchile.cl>
References: <200503211118.39223.martin@bugs.unl.edu.ar>
<20050321142931.GA11919@dcc.uchile.cl>
El Lun 21 Mar 2005 11:29, Alvaro Herrera escribió:
> On Mon, Mar 21, 2005 at 11:18:38AM -0300, Martín Marqués wrote:
>
> Hey Martin,
>
> > I have this query which has a CASE in the middle to give me special
results.
> > The problem is that it doesn't interpret my columns as it should.
> >
> > Here is the porblem:
> >
> > siprebi=> SELECT getvencimientosancion(190) AS vence, (SELECT codigo FROM
> > sanciones WHERE persona = (SELECT persona FROM usuarios WHERE codigo =
190)
> > ORDER BY femodif DESC LIMIT 1) AS sancion_original, CASE WHEN
vence>=now()
> > THEN 1 ELSE 0 END AS sancionado;
> > ERROR: no existe la columna "vence"
>
> 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
simplicity of it. :-)
siprebi=> SELECT *,CASE WHEN s1.vence>=now() THEN 1 ELSE 0 END AS sancionado
FROM (SELECT getvencimientosancion(190) AS vence, (SELECT codigo FROM
sanciones WHERE persona = (SELECT persona FROM usuarios WHERE codigo = 190)
ORDER BY femodif DESC LIMIT 1) AS sancion_original) s1;
vence | sancion_original | sancionado
------------+------------------+------------
20/03/2005 | | 0
(1 row)
Txs.
--
11:39:04 up 2 days, 16:09, 3 users, load average: 1.05, 0.81, 0.74
-----------------------------------------------------------------
Martín Marqués | select 'mmarques' || '@' || 'unl.edu.ar'
Centro de Telematica | DBA, Programador, Administrador
Universidad Nacional
del Litoral
-----------------------------------------------------------------
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: martin@bugs.unl.edu.ar, alvherre@dcc.uchile.cl
Subject: Re: CASE not working
In-Reply-To: <200503211140.16151.martin@bugs.unl.edu.ar>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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