Received: from localhost.localdomain (postgresql.org [64.49.215.8]) by localhost (Postfix) with ESMTP id 05B17475DEB for ; Tue, 2 Jul 2002 10:00:45 -0400 (EDT) Mailbox-Line: From achill@matrix.gatewaynet.com Tue Jul 2 10:00:45 2002 Received: from matrix.gatewaynet.com (unknown [217.19.69.50]) by postgresql.org (Postfix) with ESMTP id 08EB4475EF8 for ; Tue, 2 Jul 2002 08:18:45 -0400 (EDT) Received: from localhost (achill@localhost) by matrix.gatewaynet.com (8.11.6/8.11.6) with ESMTP id g62CGrH08667; Tue, 2 Jul 2002 15:16:53 +0300 Date: Tue, 2 Jul 2002 15:16:53 +0300 (EEST) From: Achilleus Mantzios To: Nick Riemondi Cc: pgsql-sql@postgresql.org Subject: Re: CASE Select, referring to selected value In-Reply-To: Message-ID: MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII X-Spam-Status: No, hits=-3.5 required=5.0 tests=IN_REP_TO,X_NOT_PRESENT,NO_MX_FOR_FROM version=2.30 X-Spam-Level: X-Archive-Number: 200207/15 X-Sequence-Number: 8394 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