From: Nick Riemondi <nick@outerscape.net>
To: achill@matrix.gatewaynet.com
Cc: pgsql-sql@postgresql.org
Subject: Re: CASE Select, referring to selected value
Date: Tue, 02 Jul 2002 09:30:19 -0500
Message-ID: <fc.009863ef000899b8009863ef00088e10.899c1@fc2.outerscape.net> (raw)
In-Reply-To: <Pine.LNX.4.44.0207021516290.7811-100000@matrix.gatewaynet.com>
References: <Pine.LNX.4.44.0207021516290.7811-100000@matrix.gatewaynet.com>
I finally thought to try this about an hour after posting. Doh! Works
perfectly.
Thanks!
achill@matrix.gatewaynet.com writes:
>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@postgresql.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@matrix.gatewaynet.com
> mantzios@softlab.ece.ntua.gr
>
-=-=-=-=-=-=-=-
Nick Riemondi
OuterScape
nick@outerscape.net
451-0535
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: nick@outerscape.net, achill@matrix.gatewaynet.com
Subject: Re: CASE Select, referring to selected value
In-Reply-To: <fc.009863ef000899b8009863ef00088e10.899c1@fc2.outerscape.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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