Received: from localhost.localdomain (postgresql.org [64.49.215.8]) by localhost (Postfix) with ESMTP id 8950D47627F for ; Tue, 2 Jul 2002 11:22:38 -0400 (EDT) Mailbox-Line: From nick@outerscape.net Tue Jul 2 11:22:38 2002 Received: from fc2.outerscape.net (fc2.outerscape.net [192.207.27.99]) by postgresql.org (Postfix) with ESMTP id 4012D4760C2 for ; Tue, 2 Jul 2002 10:31:07 -0400 (EDT) Message-id: Date: Tue, 02 Jul 2002 09:30:19 -0500 Subject: Re: CASE Select, referring to selected value To: achill@matrix.gatewaynet.com Cc: pgsql-sql@postgresql.org From: "Nick Riemondi" References: In-Reply-To: MIME-Version: 1.0 Content-type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 8bit X-Spam-Status: No, hits=-5.3 required=5.0 tests=IN_REP_TO,X_NOT_PRESENT version=2.30 X-Spam-Level: X-Archive-Number: 200207/19 X-Sequence-Number: 8398 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