pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedCASE not working
3+ messages / 2 participants
[nested] [flat]
* CASE not working
@ 2005-03-21 14:18 Martín Marqués <martin@bugs.unl.edu.ar>
2005-03-21 14:29 ` Re: CASE not working Alvaro Herrera <alvherre@dcc.uchile.cl>
0 siblings, 1 reply; 3+ messages in thread
From: Martín Marqués @ 2005-03-21 14:18 UTC (permalink / raw)
To: pgsql-sql
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
-----------------------------------------------------------------
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: CASE not working
2005-03-21 14:18 CASE not working Martín Marqués <martin@bugs.unl.edu.ar>
@ 2005-03-21 14:29 ` Alvaro Herrera <alvherre@dcc.uchile.cl>
2005-03-21 14:40 ` Re: CASE not working Martín Marqués <martin@bugs.unl.edu.ar>
0 siblings, 1 reply; 3+ messages in thread
From: Alvaro Herrera @ 2005-03-21 14:29 UTC (permalink / raw)
To: Martín Marqués <martin@bugs.unl.edu.ar>; +Cc: pgsql-sql
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)
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: CASE not working
2005-03-21 14:18 CASE not working Martín Marqués <martin@bugs.unl.edu.ar>
2005-03-21 14:29 ` Re: CASE not working Alvaro Herrera <alvherre@dcc.uchile.cl>
@ 2005-03-21 14:40 ` Martín Marqués <martin@bugs.unl.edu.ar>
0 siblings, 0 replies; 3+ messages in thread
From: Martín Marqués @ 2005-03-21 14:40 UTC (permalink / raw)
To: Alvaro Herrera <alvherre@dcc.uchile.cl>; +Cc: pgsql-sql
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
-----------------------------------------------------------------
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2005-03-21 14:40 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-03-21 14:18 CASE not working Martín Marqués <martin@bugs.unl.edu.ar>
2005-03-21 14:29 ` Alvaro Herrera <alvherre@dcc.uchile.cl>
2005-03-21 14:40 ` Martín Marqués <martin@bugs.unl.edu.ar>
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