Re: CASE not working

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
-----------------------------------------------------------------