CODEWITHSUNDEEP
sqloracle-database
Jericho
Jericho

Reputation: 10953

How to compare with Case in Where clause

Here am having Person and Address Tables .Some Persons may have address or not.If they have address then want to join address table otherwise no need to join.Please help to solve this case.

select p.name,nvl(a.address,'address not available') from person p,address a
where p.id = 2 and case
            when p.addid is not null
              then   p.addid = a.id
             else 0=0 end

Upvotes: 0

Views: 66

Answers (5)

Jackson
Jackson

Reputation: 31

select p.name, nvl(a.address, 'address not available') 
from person p left outer join address a 
   on (p.addid = a.id )
where p.id = 2;

Upvotes: 2

Hamidreza
Hamidreza

Reputation: 3128

try this:

select p.name,nvl(a.address,'address not available') from person p,address a
where p.id = 2 and a.id = case when p.addid is not null then p.addid else a.id end;

Upvotes: 1

lc.
lc.

Reputation: 116518

The general solution - use Boolean logic. You cannot choose between complete expressions using CASE, so you should rewrite it to use a combination of AND and OR. Using the logic from your question, you would rewrite it as:

WHERE p.id = 2
AND 
(
    (p.addid IS NOT NULL AND p.addid = a.id)
    OR (p.addid IS NULL AND 0=0)
)

Which ultimately simplifies down to:

WHERE p.id = 2
AND (p.addid IS NULL OR p.addid = a.id)

The specific solution for your query - use better JOIN syntax, and simply leverage a LEFT JOIN:

SELECT p.name, nvl(a.address,'address not available')
FROM person p
LEFT OUTER JOIN address a ON p.addid = a.id
WHERE p.id = 2

Upvotes: 5

Robert
Robert

Reputation: 25753

Try to use coalesce function as below

select p.name,nvl(a.address,'address not available') from person p,address a
where p.id = 2 
and coalesce(p.addid,a.id)=a.id

Upvotes: 2

SWilk
SWilk

Reputation: 3466

You can't use case, but you can achieve the same effect with combination of ors sand ands

select p.name,
       nvl(a.address,'address not available')
  from person p,
       address a
 where p.id = 2 and 
       ( p.addid is not null AND p.addid = a.id 
         OR 
         p.addid is null
       )

Upvotes: 1

Related Questions