Received: from sss.pgh.pa.us ([192.204.191.242]) by postgresql.org (8.11.3/8.11.1) with ESMTP id f5LNoCa26814 for ; Thu, 21 Jun 2001 19:50:12 -0400 (EDT) (envelope-from tgl@sss.pgh.pa.us) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.11.4/8.11.4) with ESMTP id f5LNnV328255; Thu, 21 Jun 2001 19:49:32 -0400 (EDT) To: =?iso-8859-1?q?Mart=EDn=20Marqu=E9s?= cc: pgsql sql Mailing List Subject: Re: case, new column not found In-reply-to: <01062120022108.16528@bugs> References: <01062120022108.16528@bugs> Comments: In-reply-to =?iso-8859-1?q?Mart=EDn=20Marqu=E9s?= message dated "Thu, 21 Jun 2001 20:02:21 +0300" Date: Thu, 21 Jun 2001 19:49:31 -0400 Message-ID: <28252.993167371@sss.pgh.pa.us> From: Tom Lane X-Archive-Number: 200106/345 X-Sequence-Number: 3632 =?iso-8859-1?q?Mart=EDn=20Marqu=E9s?= writes: > select *,( > (CASE WHEN titulo LIKE '%Matematica%' THEN 1 ELSE 0 END) + > (CASE WHEN descripcion LIKE '%Matematica%' THEN 1 ELSE 0 END) + > (CASE WHEN incumbencia LIKE '%Matematica%' THEN 1 ELSE 0 END) ) > AS encontrados > FROM admin_view > WHERE admin_view.nivel=1 AND encontrados > 0; > ERROR: Attribute 'encontrados' not found > Why is it? encontrados should be an attribute of type INT with the count of > the rows found. No it shouldn't. Items in the select list are not attributes. Since the WHERE phase logically precedes evaluation of the SELECT output list, you can hardly expect to be able to use SELECT outputs in WHERE. You could work around this with a nested sub-SELECT, viz select * from (select *, (CASE ...) AS encontrados FROM admin_view) subsel WHERE subsel.nivel=1 AND subsel.encontrados > 0; at a possible penalty in performance. regards, tom lane