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

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.

nohacksAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
YANN0SConnect With a Mentor 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

0
 
joebednarzCommented:
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
0
 
nohacksAuthor 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
0
 
nohacksAuthor Commented:
Thanks Yannos,


That works great...


Thanks again

Phil

0
 
nohacksAuthor Commented:
Thanks Yannos....
0
All Courses

From novice to tech pro — start learning today.