WITH clause
I need to develop a SQL query using mutliple subqueries and finally return a value based on one or more conditions.
I have been trying to use WITH clause for this scenario. It works just fine when all of the subqueries in the query return some notnull value. The result is nothing when even if one of the subquery returns a null value. I have to write another subquery using NVL function to take care of this situation. Is there a better way of handling this situation?
Ex:
WITH
q1 as (select val1 as val_1 from tab1),
q2 as (select val2 as val_2 from tab2)
select case when val_1='some value' then 'result1'
else val_2
end
from q1,q2
As i explained, if any of the values val_1 or val_2 is null then whole query returns nothing. Thanks in advance for your suggestions.