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