Re: CASE not working

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[(at)]dcc(dot)uchile(dot)cl>)
"Java is clearly an example of a money oriented programming" (A. Stepanov)