Date: Mon, 21 Mar 2005 10:29:31 -0400
From: Alvaro Herrera <alvherre@dcc.uchile.cl>
To: =?iso-8859-1?Q?Mart=EDn_Marqu=E9s?= <martin@bugs.unl.edu.ar>
Cc: pgsql-sql@postgresql.org
Subject: Re: CASE not working
Message-ID: <20050321142931.GA11919@dcc.uchile.cl>
References: <200503211118.39223.martin@bugs.unl.edu.ar>
Mime-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
In-Reply-To: <200503211118.39223.martin@bugs.unl.edu.ar>
User-Agent: Mutt/1.5.6i

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)