pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
case, new column not found
4+ messages / 4 participants
[nested] [flat]

* case, new column not found
@ 2001-06-21 17:02 Martín Marqués <martin@bugs.unl.edu.ar>
  2001-06-21 23:46 ` Re: case, new column not found Stephan Szabo <sszabo@megazone23.bigpanda.com>
  2001-06-21 23:49 ` Re: case, new column not found Tom Lane <tgl@sss.pgh.pa.us>
  2001-06-21 23:53 ` Re: case, new column not found Josh Berkus <josh@agliodbs.com>
  0 siblings, 3 replies; 4+ messages in thread

From: Martín Marqués @ 2001-06-21 17:02 UTC (permalink / raw)
  To: pgsql-sql

I'm trying somethings here and I get strange errors:

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.

Any ideas?

Saludos... :-)

-- 
Cualquiera administra un NT.
Ese es el problema, que cualquiera administre.
-----------------------------------------------------------------
Martin Marques                  |        mmarques@unl.edu.ar
Programador, Administrador      |       Centro de Telematica
                       Universidad Nacional
                            del Litoral
-----------------------------------------------------------------



^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: case, new column not found
  2001-06-21 17:02 case, new column not found Martín Marqués <martin@bugs.unl.edu.ar>
@ 2001-06-21 23:46 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
  2 siblings, 0 replies; 4+ messages in thread

From: Stephan Szabo @ 2001-06-21 23:46 UTC (permalink / raw)
  To: Martín Marqués <martin@bugs.unl.edu.ar>; +Cc: pgsql-sql


On Thu, 21 Jun 2001, [iso-8859-1] Martín Marqués wrote:

> I'm trying somethings here and I get strange errors:
> 
> 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.

IIRC, unless "encontrados" is both a pre-existing column of
admin_view and the name you're using in the select, it's only an item in a
select list.  It's not an attribute and since select list processing
happens after where clause processing (since you need to know which rows
to do it to -- imagine expensive computation in select list -- you
only want it to occur on returned rows) it isn't visible to the where
clause.





^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: case, new column not found
  2001-06-21 17:02 case, new column not found Martín Marqués <martin@bugs.unl.edu.ar>
@ 2001-06-21 23:49 ` Tom Lane <tgl@sss.pgh.pa.us>
  2 siblings, 0 replies; 4+ messages in thread

From: Tom Lane @ 2001-06-21 23:49 UTC (permalink / raw)
  To: Martín Marqués <martin@bugs.unl.edu.ar>; +Cc: pgsql-sql

=?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



^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: case, new column not found
  2001-06-21 17:02 case, new column not found Martín Marqués <martin@bugs.unl.edu.ar>
@ 2001-06-21 23:53 ` Josh Berkus <josh@agliodbs.com>
  2 siblings, 0 replies; 4+ messages in thread

From: Josh Berkus @ 2001-06-21 23:53 UTC (permalink / raw)
  To: Martín Marqués <martin@bugs.unl.edu.ar>; pgsql-sql

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



^ permalink  raw  reply  [nested|flat] 4+ messages in thread


end of thread, other threads:[~2001-06-21 23:53 UTC | newest]

Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-06-21 17:02 case, new column not found Martín Marqués <martin@bugs.unl.edu.ar>
2001-06-21 23:46 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2001-06-21 23:49 ` Tom Lane <tgl@sss.pgh.pa.us>
2001-06-21 23:53 ` Josh Berkus <josh@agliodbs.com>

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