Thread: CASE Select, referring to selected value
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
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
> 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
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