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