Clever Geek Handbook

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

+3


source to share


2 answers


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;
+1


source 


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.

0


source






More articles:


All Articles