2

I do not have data in one of the tables in the following query; my result set is empty. I'm trying to use NVL() FUNCTION, however; it still gives empty set. Can someone help me to understand this behaviour. My expectation was to display '0', however; getting empty result. I'm using oracle 11g.

SELECT NVL(T8.COLLECTION_BALANCE_AMT, 0)
      from BV_COLLECTION_CLAIM T8 JOIN BV_CLAIM_LIABLE_INDV T2
      ON T8.CLAIM_ID = T2.CLAIM_ID
      JOIN BV_CLAIM_RECOVERY T3
      ON T8.CLAIM_ID = T3.CLAIM_ID
Mureinik
  • 297,002
  • 52
  • 306
  • 350
aajmee
  • 25
  • 1
  • 3
  • 1
    Well, is there actually a row coming back? NVL is for null values in a row, not for non-existent rows. – OldProgrammer Jan 29 '15 at 15:38
  • Now row if I understood the op correct. – frlan Jan 29 '15 at 15:39
  • Well, apparently you have no rows where the JOIN operations succeed, and therefore no row is returned. If you change the `NVL()` to `T8.CLAIM_ID` instead, do you get a row back? If there's no data matching your criteria, you won't get any data. – Ken White Jan 29 '15 at 15:43
  • `nvl()` is about replacing a `null` value in a *column*, not about adding rows that aren't there –  Jan 29 '15 at 15:50
  • Thanks everyone, it is now clear to me. – aajmee Jan 29 '15 at 18:28

1 Answers1

1

A join only returns matching rows. You're looking for a left join, which would return null for columns in the joined table where matching do not exist:

SELECT    NVL(T8.COLLECTION_BALANCE_AMT, 0)
FROM      BV_COLLECTION_CLAIM T8 
LEFT JOIN BV_CLAIM_LIABLE_INDV T2 -- here!
       ON T8.CLAIM_ID = T2.CLAIM_ID
LEFT JOIN BV_CLAIM_RECOVERY T3  -- here too!
       ON T8.CLAIM_ID = T3.CLAIM_ID
Mureinik
  • 297,002
  • 52
  • 306
  • 350