Search code examples
sqloracle-databasefunctioncoalesce

Identify what source was used in ORACLE COALESCE

How do I identify which source the COALESCE has returned data for?

I want something like:

SELECT    
COALESCE(SOURCE1, SOURCE2) SOURCE,
SOURCE_LOACTION
FROM
DATATABLE1 D1, DATATABLE2 D2

Where I have a column in the results displaying what source the data come from

Answer:

As I did my usual and didn't word my question well enough so I had to make up what I needed out of what everyone provided:

-- I was using COALESCE on more than one field
COALESCE(D1.FIELD1, D2.FIELD1) FIELD1,
COALESCE(D1.FIELD2, D2.FIELD2) FIELD2,
COALESCE(D1.FIELD3, D2.FIELD3) FIELD3,

CASE WHEN 

    D1.FIELD1 IS NULL AND
    D1.FIELD2 IS NULL AND
    D1.FIELD3 IS NULL AND

THEN 'SOURCE2' ELSE 'SOURCE1' END AS DATASOURCE

Thanks for the help :)

Solution 
  • SELECT    
COALESCE(SOURCE1, SOURCE2) SOURCE,
,COALESCE(nvl2(source1,'source1',null), nvl2(source2,'source2',null)), 
FROM DATATABLE1 D1, DATATABLE2 D2