Re: CASE Select, referring to selected value

On Mon, 1 Jul 2002, Nick Riemondi wrote:

Just rewrite your case statement in the where clause.

> Hi!
> I've got 2 date fields in table which constitute a term. On select I'm
> calculating that term, BUT I can't figure out how to refer to it in the
> sql - or if it's possible. I'd like to be able to use the case returned
> value for order, comparing, etc. Here's an example:
>
> select case when (current_date > available) then ((end_date -
> current_date) / 30)
> when (current_date < available) then ((end_date - available) /
> 30)
> end
> from listing
>
> This yields something along the line of:
>
> case
> ------
> 2
> 39
> 11
> 64
> ...
>
> which is great. Now, I'd like to be able to do this for example:
>
> select case when (current_date > available) then ((end_date -
> current_date) / 30)
> when (current_date < available) then ((end_date - available) /
> 30)
> end
> from listing
> where case > 4
>
> which yields:
> ERROR: parser: parse error at or near ">"
>
> Does anyone know how I could accomplish this?
>
> Thanks in Advance!
> Nick
>
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly
>
>

--
Achilleus Mantzios
S/W Engineer
IT dept
Dynacom Tankers Mngmt
tel: +30-10-8981112
fax: +30-10-8981877
email: achill(at)matrix(dot)gatewaynet(dot)com
mantzios(at)softlab(dot)ece(dot)ntua(dot)gr