CODEWITHSUNDEEP
oracle-databasejoin
Alexandre Barrère
Alexandre Barrère

Reputation: 89

Oracle Join with operation returning null values

I'm trying to Right join two table on a column named "compte" I need to do an addition after. The problem is that some "compte" doesn't exist in one of the table and as a result, the addition return null instead of keeping the based value

Here's the query

SELECT t.compte,t.posdev+x.mnt
FROM (

  SELECT compte,SUM(mntdev) as mnt FROM mvtc22
  WHERE compte IN ('11510198451','00610198451','40010198451','40010198453','00610198461','00101980081','00101980094',
  '00101980111','40010198461','40010198462','40010198466','40010198463')
  AND datoper BETWEEN '01/01/22' AND '06/01/22'
  GROUP BY compte
)x

   RIGHT OUTER JOIN
(
  SELECT c.compte,c.posdev
  FROM v_sldoper c
  WHERE c.compte IN ('11510198451','00610198451','40010198451','40010198453','00610198461','00101980081','00101980094',
  '00101980111','40010198461','40010198462','40010198466','40010198463')
  AND datpos = '31/12/21'
)t

ON  t.compte = x.compte

And the results :

first sub query result second sub query result Final result

I'm expecting to keep the results from the second subquery if there's no "compte" in the first subquery.

Thanks In advance, Alex

Upvotes: 0

Views: 80

Answers (1)

Mahamoutou
Mahamoutou

Reputation: 1555

You are very close, the problem is that in oracle SQL the result of any value + null value is null, so you need to handle potential null values from each column before applying the + operator betwen them. To solve the issue, you can apply functions like NVL or decode or even CASE WHEN for that purpose. Below I use NVL function to solve it (I assume t.posdev column cannot contain null values, otherwise apply nvl function to both columns).

SELECT t.compte, t.posdev + NVL(x.mnt, 0)
FROM (

  SELECT compte,SUM(mntdev) as mnt FROM mvtc22
  WHERE compte IN ('11510198451','00610198451','40010198451','40010198453','00610198461','00101980081','00101980094',
  '00101980111','40010198461','40010198462','40010198466','40010198463')
  AND datoper BETWEEN '01/01/22' AND '06/01/22'
  GROUP BY compte
)x

   RIGHT OUTER JOIN
(
  SELECT c.compte,c.posdev
  FROM v_sldoper c
  WHERE c.compte IN ('11510198451','00610198451','40010198451','40010198453','00610198461','00101980081','00101980094',
  '00101980111','40010198461','40010198462','40010198466','40010198463')
  AND datpos = '31/12/21'
)t

ON  t.compte = x.compte

Upvotes: 2

Related Questions