Thread: case, new column not found

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
ELSE0 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
 
-----------------------------------------------------------------

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.

Martín Marqués <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) subselWHERE subsel.nivel=1 AND subsel.encontrados >
0;

at a possible penalty in performance.
        regards, tom lane

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.*, encontradosFROM admin_view,
(SELECT  ((CASE WHEN titulo LIKE '%Matematica%' THEN 1 ELSE 0 END) +  (CASE WHEN descripcion LIKE '%Matematica%' THEN 1
ELSE0 END) + (CASE WHEN incumbencia LIKE '%Matematica%' THEN 1 ELSE 0 END) ) AS encontrados, id FROM admin_view)
sub_adminWHEREadmin_view.nivel=1AND 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
informationtechnology      josh@agliodbs.com  and data management solutions       (415) 565-7293 for law firms, small
businesses       fax 621-2533   and non-profit organizations.      San Francisco