Jeffrey Williams 3188 (10/22/2013)
The only way to get rid of the case expression would be to build a table that contains a cross-map of the values.
Then in your query you would join (outer join) to this table and pull the value you want.
An example without the need of a physical table. Performance should be basically the same as the number of rows is very small. However, test should be done.
WITH CompanyNames(Source, Name) AS(
SELECT 'CREDITNEW', 'CREDIT' UNION ALL
SELECT 'CREDITOLD' , 'CREDIT' UNION ALL
SELECT 'TOC' , 'CREDIT' UNION ALL
SELECT 'DEBIT' , 'UC DEBIT' UNION ALL
SELECT 'DEBITOLD' , 'DEBIT' UNION ALL -- Defined orginally with space
SELECT 'DEBITNEW' , 'DEBIT' UNION ALL -- Crystal uses the space version for logo
SELECT 'TLC' , 'DEBIT'),
SELECT 'CREDITNEW' AS Source,
1 AS CompanyID,
'Something' AS TermsofUse
SELECT 'DOESITMATTER?' AS Source,
2 AS CompanyID,
'Something Else' AS TermsofUse
SELECT ISNULL(CAST( n.Name AS varchar(10)), 'UNDEFINED') AS CompanyName,
FROM Customer c
JOIN CompanyNames n ON c.Source = n.Source
Luis C.General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?How to post data/code on a forum to get the best help: Option 1 / Option 2