CODEWITHSUNDEEP
sqloracle-databasecoalesce
user2405778
user2405778

Reputation: 497

Not understanding how Coalesce works?

I thought I understood how coalesce works in oracle, but for some reason this simple select statement isnt bring up what its supposed to. My understand of coalesce is that it goes through all the values in the parenthesis until it hits the value that is in the field it is looking at. In my code the two accounts in the select have a rr.rel_type of Customer2. I double checked this by looking at the values in racctrel. When I run the code though, zero results come up. Here is the code:

SELECT lt.ACCOUNT, 
       rr.rel_type,
       lt.transaction_date, 
       lt.amount, 
       lt.transaction_code, 
       ltc.fintran_tran_code, 
       fo.FIRM_NAME AS ATTORNEY, 
       lt.debtor_ssn, 
       REPLACE(rr.NAME,';',',') AS DEBTOR_NAME, 
       lt.description AS COST_DESCRIPTION,
       pd.owner
FROM legal_transactions lt, 
     legal_transaction_codes ltc, 
     firms_onboarded fo, 
     racctrel rr,
     package_details pd,
     raccount ra
WHERE fo.attorney_id = lt.attorney_id
AND rr.ACCOUNT = lt.ACCOUNT
AND ra.ACCOUNT = lt.ACCOUNT
and pd.package_id = ra.user_text_3
AND ltc.transaction_code = lt.transaction_code
AND lt.batch_id = 865
AND upper(rr.rel_type) = coalesce('CUSTOMER1','PRIMDEBTOR','CUSTOMER2')
AND lt.ACCOUNT IN ('17149146','17918854');

SELECT account, rel_type
FROM racctrel
where ACCOUNT IN ('17149146','17918854');

The results are:

17918854    Customer2
17149146    Customer2

Upvotes: 0

Views: 153

Answers (2)

Christoph Walesch
Christoph Walesch

Reputation: 2427

coalesce returns the first not-null argument, or NULL if all arguments are NULL. So coalesce('CUSTOMER1','PRIMDEBTOR','CUSTOMER2') always evaluates to 'CUSTOMER1'. What you might want here is an IN clause. Instead of

AND upper(rr.rel_type) = coalesce('CUSTOMER1','PRIMDEBTOR','CUSTOMER2')

write

AND upper(rr.rel_type) in ('CUSTOMER1','PRIMDEBTOR','CUSTOMER2')

Upvotes: 2

smnbbrv
smnbbrv

Reputation: 24541

Actually, it works as it should:

Here 

AND upper(rr.rel_type) = coalesce('CUSTOMER1','PRIMDEBTOR','CUSTOMER2')

the result of COALESCE is 'CUSTOMER1' - first not null value, and here

17918854    Customer2
17149146    Customer2

you have only 'CUSTOMER2'.

So it's correct: 

AND upper(rr.rel_type) = coalesce('CUSTOMER1','PRIMDEBTOR','CUSTOMER2')

=> 

AND 'CUSTOMER2' = 'CUSTOMER1'

is false.

Upvotes: 4

Related Questions