T-SQL returned adjusted column based on non-exhaustive search
Please forgive what I think should be a fairly simple question, but I am struggling to find other previous questions with the same terms.
I have a SQL Server database table that includes a column WorkplaceName
( nvarchar
) with many different possibilities for that column, which comes from a different source system and cannot be modified upstream.
An example of what this might look like from a select statement MainDataTable
:
WorkplaceName | Value
---------------------
ID1234 | 54
ID9876 | 99
ID 2345 | 76
Almost all of these entries are correct, however there are a small number of entries that are incorrect and for the purpose of manipulating data downstream, I need to fix some of these locations based on a non-exhaustive lookup table.
Suppose "ID 2345" is poorly formed and should have been ID2345.
I have a named lookup table WorkplaceLookups
that contains ONLY entries that are known to be wrong and the corrected output is:
WorkplaceNameReference | WorkplaceNameLookup
----------------------------------------------
'ID 2345' | 'ID2345'
Thus, if a well-formed workspace (which is not in the search list), I want to return the original value; if a malformed job exists, it should be corrected with a value in the search.
Using the SQL-ish pseudocode, this is more or less what I am trying to do:
SELECT
CASE
WHEN [MainDataTable].[WorkplaceName] IN [WorkplaceLookups].[WorkplaceNameReference]
THEN [WorkplaceLookups].[WorkplaceNameLookup]
ELSE [MainDataTable].[WorkplaceName]
END AS [WorkplaceNameCorrected],
[Value]
FROM
MainDataTable
How should I do it? If at all possible, I would like to avoid any functions or stored procedures, but if the only solution is to use them, then so be it.
I would be very grateful for your feedback.
Thanks a lot, Brian
Use left outer join
and coalesce()
:
SELECT COALESCE(wl.WorkplaceNameLookup, mdt.WorkplaceName) as WorkplaceNameCorrected,
Value
FROM MainDataTable mdt LEFT JOIN
WorkplaceLookups wl
ON mdt.WorkplaceName = wl.WorkPlaceNameReference;
SELECT IsNull(m.WorkplaceNameLookup, l.WorkplaceName) as WorkplaceName, Value
FROM MainDataTable m LEFT JOIN
WorkplaceLookups l ON m.WorkplaceName = l.WorkPlaceNameReference
hope this helps you.