pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedCASE Select, referring to selected value
4+ messages / 3 participants
[nested] [flat]
* CASE Select, referring to selected value
@ 2002-07-01 22:00 Nick Riemondi <nick@outerscape.net>
0 siblings, 2 replies; 4+ messages in thread
From: Nick Riemondi @ 2002-07-01 22:00 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: CASE Select, referring to selected value
@ 2002-07-02 12:16 Achilleus Mantzios <achill@matrix.gatewaynet.com>
parent: Nick Riemondi <nick@outerscape.net>
1 sibling, 1 reply; 4+ messages in thread
From: Achilleus Mantzios @ 2002-07-02 12:16 UTC (permalink / raw)
To: Nick Riemondi <nick@outerscape.net>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: CASE Select, referring to selected value
@ 2002-07-02 13:57 Christopher Kings-Lynne <chriskl@familyhealth.com.au>
parent: Nick Riemondi <nick@outerscape.net>
1 sibling, 0 replies; 4+ messages in thread
From: Christopher Kings-Lynne @ 2002-07-02 13:57 UTC (permalink / raw)
To: pgsql-sql; Nick Riemondi <nick@outerscape.net>
> 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?
Try an alias:
select case when (current_date > available) then ((end_date -
current_date) / 30)
when (current_date < available) then ((end_date - available) /
30)
end as asdf
from listing
where asdf > 4
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: CASE Select, referring to selected value
@ 2002-07-02 14:30 Nick Riemondi <nick@outerscape.net>
parent: Achilleus Mantzios <achill@matrix.gatewaynet.com>
0 siblings, 0 replies; 4+ messages in thread
From: Nick Riemondi @ 2002-07-02 14:30 UTC (permalink / raw)
To: achill@matrix.gatewaynet.com; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2002-07-02 14:30 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-07-01 22:00 CASE Select, referring to selected value Nick Riemondi <nick@outerscape.net>
2002-07-02 12:16 ` Achilleus Mantzios <achill@matrix.gatewaynet.com>
2002-07-02 14:30 ` Nick Riemondi <nick@outerscape.net>
2002-07-02 13:57 ` Christopher Kings-Lynne <chriskl@familyhealth.com.au>
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