• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 698
  • Last Modified:

Coalesce, a right option?


I have the following code. I want to join the referral_id from AAA to referral_id from BBB.
 If this is not satisfied, then i need to check if the case_id from AAA matches case_id from BBB.
 Will COALESCE be the right option?
 
 Please advise.
 
select *
from aaa, bbb
where coalesce(aaa.referral_id,aaa.case_id) = coalesce(bbb.referral_id,bbb.case_id)

Open in new window

0
pvsbandi
Asked:
pvsbandi
1 Solution
 
momi_sabagCommented:
no
coalesce is used to return the first non-null value,

you need something like


elect *
from aaa, bbb
where aaa.referral_id = bbb.referral_id
or ,aaa.case_id = bbb.case_id
0
 
HainKurtSr. System AnalystCommented:
fixed the code above posted by momi

select *
from aaa, bbb
where aaa.referral_id = bbb.referral_id or aaa.case_id = bbb.case_id
0
 
pvsbandiAuthor Commented:
But i want to consider joining the cases, only when the referral_id is null. How can i do that?
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
HainKurtSr. System AnalystCommented:
try this:

select *
from aaa, bbb
where
(aaa.referral_id is not null and aaa.referral_id = bbb.referral_id)
or
(aaa.referral_id is null and aaa.case_id = bbb.case_id)
0
 
Kent OlsenData Warehouse Architect / DBACommented:
Hi pvsbandi,

You query will work ONLY if there is no possibility of an item in the referral_id column matching an item in the case_id column.  As written, if aaa.referral_id is null and bbb.referral_id is not null, the coalesce functions will return aaa.case_number and bbb.referral id and attempt to join on them.  (Probably not what you want.)


One way is below, but it's kind of ugly.


Kent

select *
from aaa, bbb
where 
  case when aaa.referral_id is null or bbb.referral_id is null then aaa.case_id else aaa.referral_id end =
  case when aaa.referral_id is null or bbb.referral_id is null then bbb.case_id else bbb.referral_id end

Open in new window

0
 
pvsbandiAuthor Commented:
Works!! Thank You!
0
 
tliottaCommented:
where coalesce(aaa.referral_id,aaa.case_id) = coalesce(bbb.referral_id,bbb.case_id)

Interesting thought. The initial logical trouble should arise when aaa.referral_id is null and aaa.case_id matches a bbb.referral_id value. Maybe that can't happen in your database, so you might slip past it.

only when the referral_id is null.

Does that mean that you don't want non-null referral_id rows at all? Or does it mean that you can handle non-null referral_id rows already, but you also want to know how to include rows with null referral_id values?

Should we assume that referral_id and case_id have the same data attributes?

Tom
0
 
pvsbandiAuthor Commented:
Hi Tom,

    By "referral_id is null", i meant in the situations where referral_id is null and case_id is not null..

0

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now