pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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



view thread (4+ messages)  latest in thread

Message-ID: <28252.993167371@sss.pgh.pa.us>
Permalink:  ../28252.993167371@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/28252.993167371@sss.pgh.pa.us

 · 

reply

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