Re: case, new column not found

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