We help IT Professionals succeed at work.

Oracle Outer Join (+) - default value for column when join fails

nohacks
nohacks asked
on
Hi,

Do you know how I return a value in a select statement when an outer join is null....

I want to set a default value for a column when an outer join fails (+)

Select a.id, b.address
 from member a , address b
 where
     a.member_id = 1
 and a.member_id = b.member_id ( + )

default address when outer join fails.

Comment
Watch Question

Select a.id, b.address
 from member a , address b
 where
     a.member_id = 1
 and a.member_id = b.member_id ( + )
--
-- add the following...
--
AND b.member_id IS NULL
Commented:

Select a.id, NVL(b.address ,'Your default address') address 
 from member a , address b 
 where 
     a.member_id = 1
 and a.member_id = b.member_id ( + )

Open in new window

Author

Commented:
Thanks for your comment.

I wanted to set a value for address when the outer join fails ( + )

so if join fails I want to set address value to "No Address on File"

Thanks
Phil

Author

Commented:
Thanks Yannos,


That works great...


Thanks again

Phil

Author

Commented:
Thanks Yannos....