I am currently working on an SSRS report that will need to display a Dr's ID based on either their DEA number or NPI number. I need to be able to do a coalesce on DEA and NPI to find the non-unknown one, which I will then add code to the SSRS report that will display a hover over stating whether the "DEA Number" or "NPI Number" is being displayed.
Would the following SQL accomplish this on the back-end? From what I have read online it seems like nullif and coalesce are very similar and was wondering what the underlining difference is and if they could be used together to accomplish this requirement.
coalesce(nullif(convert(varchar,NationalProviderIdentifier),'0'), DEANumber) as 'Dr Id'
They are more or less unrelated.
coalesce() takes a list of values are returns the first non-null value (or
null if all values are
nullif() takes two values and returns the first value, except it returns
null if both values are equal.
If the two were converted to
case statements, they would be:
case when value1 is not null then value1 when value2 is not null then value2 ... etc else null end
case when value1 = value2 then null else value1 end