Oracle Join with operation returning null values-CodePudding

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 :

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

Thanks In advance, Alex

CodePudding user response：

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