Received: from davinci.ethosmedia.com (davinci.ethosmedia.com [209.10.40.250]) by postgresql.org (8.11.3/8.11.1) with ESMTP id f5LNrba27702 for ; Thu, 21 Jun 2001 19:53:37 -0400 (EDT) (envelope-from josh@agliodbs.com) Received: from [209.10.40.250] (account ) by davinci.ethosmedia.com (CommuniGate Pro WebUser 3.4.1) with HTTP id 75619; Thu, 21 Jun 2001 16:53:25 -0700 From: "Josh Berkus" Subject: Re: case, new column not found To: =?ISO-8859-1?Q?Mart=EDn_Marqu=E9s?= , pgsql sql Mailing List X-Mailer: CommuniGate Pro Web Mailer v.3.4.1 Date: Thu, 21 Jun 2001 16:53:25 -0700 Message-ID: In-Reply-To: <01062120022108.16528@bugs> MIME-Version: 1.0 Content-Type: text/plain; charset="ISO-8859-1" Content-Transfer-Encoding: 8bit X-Archive-Number: 200106/346 X-Sequence-Number: 3633 Martín, > 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 The problem is that you're trying to do a WHERE filtering on a calculated column by its alias. The query engine (correct me if I'm wrong) wants to evaluate the WHERE clause before the SELECT columns are returned; as a result, there is no "encontrados" to evaluate as it has not been calculated yet. If you really want the results above, you need to: SELECT admin_view.*, encontrados FROM admin_view, (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, id FROM admin_view) sub_admin WHERE admin_view.nivel=1 AND admin_view.id = sub_admin.id AND encontrados > 0; This forces encontrados to be evaluated first because it's in a subselect. A simpler approach would be: > 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 (titulo LIKE '%Matematica%' OR descripcion LIKE '%Matematica%' OR incumbencia LIKE '%Matematica%'); Which would give you the same results. -Josh ______AGLIO DATABASE SOLUTIONS___________________________ Josh Berkus Complete information technology josh@agliodbs.com and data management solutions (415) 565-7293 for law firms, small businesses fax 621-2533 and non-profit organizations. San Francisco