Обсуждение: CASE not working

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 translation of the error is: column "vence" does not exist).

I don't know what I'm getting wrong here.

-- 11:12:48 up 2 days, 15:43,  2 users,  load average: 0.95, 0.54, 0.56
-----------------------------------------------------------------
Martín Marqués        | select 'mmarques' || '@' || 'unl.edu.ar'
Centro de Telematica  |  DBA, Programador, Administrador            Universidad Nacional                 del Litoral
-----------------------------------------------------------------

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

-- 
Alvaro Herrera (<alvherre[@]dcc.uchile.cl>)
"Java is clearly an example of a money oriented programming"  (A. Stepanov)

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