Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

WITH clause

user12003658May 26 2012 — edited May 29 2012
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.
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jun 26 2012
Added on May 26 2012
6 comments
248 views