SQL conditionally returns results from one of two DB columns into a single result column
Hello, I have the following SQL query that I would like to conditionally return the results from one of two DB columns (DEA and DEA_ALT) into one result column, depending on which of those two columns is being evaluated (but not null). The DB is set up where there are two fields that can store a field called DEA #, however both fields have the same purpose, and so I would like to return results from either column, depending on which one is being evaluated (could be either) ...
SELECT Distinct t1.UserName,
t1.[LoginID],
t7.FirstName,
t7.LastName,
t7.Title ,
t7.EndDateOfValidity,
NPI = ISNULL(HStaffIdentifiers.Value,''),
Type= HStaffIdentifiers.Type,
DEA=ISNULL(t5.PrescriberCode,''),
--
--
DEA_ALT = ISNULL((select CASE ISNULL(PC.PrescriberCode,'')
when '' then ISNULL(HS.Value,'')
end
from HStaff PC
left outer join HStaffIdentifiers HS
ON PC.ObjectID = HS.Staff_oid
where HS.Type = 'DEA'
and HS.Staff_oid = HStaffIdentifiers.Staff_oid
),ISNULL(t5.PrescriberCode,'')),
--
--
Speciality = ISNULL(HDataDictionaryItem3.Description,''),
Preference_Group = HDataDictionaryItem1.Name
FROM HSUser t1 with (nolock)
Left Outer JOIN HStaff t5 with (nolock)
ON t1.UserName=t5.MSINumber
LEFT OUTER JOIN HStaffIdentifiers HStaffIdentifiers WITH (NOLOCK)
ON t5.ObjectID = HStaffIdentifiers.Staff_oid
AND HStaffIdentifiers.Type = 'NPI'
JOIN HPerson t6 with (nolock)
ON t5.ObjectID=t6.ObjectID
JOIN HName t7 with (nolock)
ON t6.ObjectID=t7.Person_oid
and (t7.EndDateOfValidity < '2010-01-01 00:00:00.000'
or t7.EndDateOfValidity is null)
JOIN HDataDictionaryItem HDataDictionaryItem1 WITH (NOLOCK)
ON t1.PreferenceGroup_oid = HDataDictionaryItem1.ObjectID
AND HDataDictionaryItem1.ItemType_oid = 98
left outer join HDataDictionaryItem HDataDictionaryItem3 WITH (NOLOCK)
ON t5.PrimarySpeciality_oid = HDataDictionaryItem3.ObjectID
AND HDataDictionaryItem3.ItemType_oid = 43
WHERE
t1.EndTime >= GETDATE()
AND t1.BeginTime<=GETDATE()
AND t5.Active=1
AND t6.IsDeleted=0
AND t5.StaffType = 0
order by t7.LastName, t7.FirstName
An excerpt from the results at the following link: http://s7.postimg.org/i5xlsjhnv/12_31_2014_10_00_13_AM.jpg
As you can see from the query results (highlighted in red) there are cases where the DEA can only be populated in one of these two columns, however I want the results to be returned in one column from the DEA column, if it is appreciated, if not then I want to check if DEA_ALT is being evaluated and if so return the result in that single column. How can i do this?
source to share
As @NoDisplayName pointed out, you should just use coalesce
. From what I can tell, though I think it might be a little easier. Specifically, if you want to combine DEA and DEA_ALT, you can take this section
DEA=ISNULL(t5.PrescriberCode,''),
--
--
DEA_ALT = ISNULL((select CASE ISNULL(PC.PrescriberCode,'')
when '' then ISNULL(HS.Value,'')
end
from HStaff PC
left outer join HStaffIdentifiers HS
ON PC.ObjectID = HS.Staff_oid
where HS.Type = 'DEA'
and HS.Staff_oid = HStaffIdentifiers.Staff_oid
),ISNULL(t5.PrescriberCode,'')),
and write like this:
Coalesce(t5.PrescriberCode, HStaffIden.Value) As DEA,
you will need to add a new table for DEA values like
LEFT OUTER JOIN HStaffIdentifiers HStaffIden WITH (NOLOCK)
ON HStaffIdentifiers.Staff_oid = HStaffIden.Staff_oid
you can take this join condition
LEFT OUTER JOIN HStaffIdentifiers HStaffIdentifiers WITH (NOLOCK)
ON t5.ObjectID = HStaffIdentifiers.Staff_oid
AND HStaffIdentifiers.Type = 'NPI'
And remove the AND condition like this
LEFT OUTER JOIN HStaffIdentifiers HStaffIdentifiers WITH (NOLOCK)
ON t5.ObjectID = HStaffIdentifiers.Staff_oid
Then add this condition to the where clause to complete the changes
AND HStaffIdentifiers.Type = 'NPI'
And HStaffIden = 'DEA'
Edit: Fixed the resultset to match the original query
source to share
It looks like you just need Isnull
orcoalesce
SELECT ...,Isnull(t5.PrescriberCode, (SELECT CASE Isnull(PC.PrescriberCode, '')
WHEN '' THEN Isnull(HS.Value, '')
END
FROM HStaff PC
LEFT OUTER JOIN HStaffIdentifiers HS
ON PC.ObjectID = HS.Staff_oid
WHERE HS.Type = 'DEA'
AND HS.Staff_oid = HStaffIdentifiers.Staff_oid), Isnull(t5.PrescriberCode, ''))
,...
source to share