From: Tom Lane <tgl@sss.pgh.pa.us>
To: Martín Marqués <martin@bugs.unl.edu.ar>
Cc: pgsql sql Mailing List <pgsql-sql@postgresql.org>
Subject: Re: case, new column not found
Date: Thu, 21 Jun 2001 19:49:31 -0400
Message-ID: <28252.993167371@sss.pgh.pa.us> (raw)
In-Reply-To: <01062120022108.16528@bugs>
References: <01062120022108.16528@bugs>
=?iso-8859-1?q?Mart=EDn=20Marqu=E9s?= <martin@bugs.unl.edu.ar> 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: tgl@sss.pgh.pa.us, martin@bugs.unl.edu.ar
Subject: Re: case, new column not found
In-Reply-To: <28252.993167371@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox