Here is a simplification of my problem:
SELECT 123 id, 1 toto , NULL titi FROM dual UNION SELECT 123 id, null toto , 2 titi FROM dual -> ID |TOTO|TITI| ---+----+----+ 123| 1| | 123| | 2|
I would like to have a result like this (keeping those 2 selects):
ID |TOTO|TITI| ---+----+----+ 123| 1| 2 |
Not sure how to fusion those 2 lines
One ugly solution is:
SELECT id, min (toto), min(titi) FROM ( SELECT 123 id, 1 toto , NULL titi FROM dual UNION SELECT 123 id, null toto , 2 titi FROM dual ) GROUP BY id
Advertisement
Answer
For me it looks like a join with nvl for me:
with t1 as (SELECT 123 id, 1 toto , NULL titi FROM dual) ,t2 as (SELECT 123 id, null toto , 2 titi FROM dual) select nvl(t1.id , t2.id ) id ,nvl(t1.toto, t2.toto ) toto ,nvl(t1.titi, t2.titi ) titi from t1 full outer join t2 on t1.id=t2.id
I’m not sure about your data, so probably it should be just left/right join or even inner join.