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