Where clause on custom column

select c.base_id,case when c.type is not null then c.type else g.type end as type, c.function_id
from cust c 
left join category cg on c.cat_id=cg.id
where c.type='privelleged'; --- where clause is not working as expected
 

What am I missing in the above query.The where clause is not working as expected. I need to apply the where clause on the derived ‘type’ column. How do I acheive that in Oracle.

Thanks in advance

Answer

I suspect that you just want coalesce() — in both the select and where:

select c.base_id, coalesce(c.type, g.type) as type, c.function_id
from cust c left join
     category cg
     on c.cat_id = cg.id
where coalesce(c.type, g.type) = 'privileged';