I have a table with some input values and a table with lookup values like below:
select input.value, coalesce(mapping.value, input.value) result from (
select 'a' union all select 'c'
) input (value) left join (
select 'a', 'z' union all select 'b', 'y'
) mapping (lookupkey, value) on input.value = mapping.lookupkey
which gives:
value | result
--------------
a | z
c | c
i.e. I want to show the original values as well as the mapped value but if there is none then show the original value as the result.
The above works well so far with coalesce to determine if there is a mapped value or not. But now if I allow NULL as a valid mapped value, I want to see NULL as the result and not the original value, since it does find the mapped value, only that the mapped value is NULL. The same code above failed to achieve this:
select input.value, coalesce(mapping.value, input.value) result from (
select 'a' union all select 'c'
) input (value) left join (
select 'a', 'z' union all select 'b', 'y' union all select 'c', null
) mapping (lookupkey, value) on input.value = mapping.lookupkey
which gives the same output as above, but what I want is:
value | result
--------------
a | z
c | NULL
Is there an alternative to coalesce that can achieve what I want?
I think you just want a case expression e.g.
select input.[value]
, coalesce(mapping.[value], input.[value]) result
, case when mapping.lookupkey is not null then mapping.[value] else input.[value] end new_result
from (
select 'a'
union all
select 'c'
) input ([value])
left join (
select 'a', 'z'
union all
select 'b', 'y'
union all
select 'c', null
) mapping (lookupkey, [value]) on input.[value] = mapping.lookupkey
Returns:
value result new_result
a z z
c c NULL