Re: case, new column not found

=?iso-8859-1?q?Mart=EDn=20Marqu=E9s?= <martin(at)bugs(dot)unl(dot)edu(dot)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