Are there alternatives to this SQL logic

Question

Post reply

Are there alternatives to this SQL logic

Sush Ram

SSC Rookie

Points: 26
More actions
October 22, 2013 at 10:08 am

#395706

Hi!
what ways you could simplify using an explicit “=” statement for each Source value? Are there alternatives, especially when you have 3 Source values that equate to one Eg: 'CREDITNEW' and 'CREDITOLD' equate to one 'CREDIT'.
Is there a more maintainable way we could alter this logic. Can I avoid using the case statement.
SELECT CASE
WHEN RTRIM(Customer.Source) = 'CREDITNEW' THEN 'CREDIT'
WHEN RTRIM(Customer.Source) = 'CREDITOLD' THEN 'CREDIT'
WHEN RTRIM(Customer.Source) = 'TOC' THEN 'CREDIT'
WHEN RTRIM(Customer.Source) = 'DEBIT' THEN 'UC DEBIT'
WHEN RTRIM(Customer.Source) = 'DEBITOLD' THEN 'DEBIT' -- Defined orginally with space
WHEN RTRIM(Customer.Source) = 'DEBITNEW' THEN 'DEBIT' -- Crystal uses the space version for logo
WHEN RTRIM(Customer.Source) = 'TLC' THEN 'DEBIT'
ELSE 'UNDEFINED' -- Default logo
END AS CompanyName, CompanyId, TermsofUse
FROM Customer

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 1

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.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 2

You could use a "simple case" expression (in contrast with the searched case expression you wrote).

SELECT CASE RTRIM(Customer.Source)

WHEN 'CREDITNEW' THEN 'CREDIT'

WHEN 'CREDITOLD' THEN 'CREDIT'

WHEN 'TOC' THEN 'CREDIT'

WHEN 'DEBIT' THEN 'UC DEBIT'

WHEN 'DEBITOLD' THEN 'DEBIT' -- Defined orginally with space

WHEN 'DEBITNEW' THEN 'DEBIT' -- Crystal uses the space version for logo

WHEN 'TLC' THEN 'DEBIT'

ELSE 'UNDEFINED' -- Default logo

END AS CompanyName, CompanyId, TermsofUse

FROM Customer

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

LightVader Hall of Fame Points: 3810 More actions · Answer 3

How about using IN?

SELECT CASE

WHEN RTRIM(Customer.Source) IN ('CREDITNEW','CREDITOLD','TOC') THEN 'CREDIT'

WHEN RTRIM(Customer.Source) IN ('DEBIT') THEN 'UC DEBIT'

WHEN RTRIM(Customer.Source) IN ('DEBITOLD','DEBITNEW','TLC') THEN 'DEBIT' -- Defined orginally with space, Crystal uses the space version for logo

ELSE 'UNDEFINED' -- Default logo

END AS CompanyName, CompanyId, TermsofUse

FROM Customer

Edit: Formatting

The opinions expressed herein are strictly personal and do not necessarily reflect the views or policies of my employer.

Luis Cazares SSC Guru Points: 183706 More actions · Answer 4

You don't need the RTRIM at all. Here's an example.

WITH Customer(Source) AS(

SELECT 'CREDITNEW' UNION ALL

SELECT 'CREDITNEW ' UNION ALL --Spaces added

SELECT 'CREDITNEW' UNION ALL --Tab added

SELECT 'CREDITOLD' UNION ALL

SELECT 'TOC')

SELECT * ,

CASE

WHEN RTRIM(Customer.Source) IN ('CREDITNEW','CREDITOLD','TOC') THEN 'CREDIT'

WHEN RTRIM(Customer.Source) IN ('DEBIT') THEN 'UC DEBIT'

WHEN RTRIM(Customer.Source) IN ('DEBITOLD','DEBITNEW','TLC') THEN 'DEBIT' -- Defined orginally with space, Crystal uses the space version for logo

ELSE 'UNDEFINED' -- Default logo

END AS CompanyName,

CASE

WHEN Customer.Source IN ('CREDITNEW','CREDITOLD','TOC') THEN 'CREDIT'

WHEN Customer.Source IN ('DEBIT') THEN 'UC DEBIT'

WHEN Customer.Source IN ('DEBITOLD','DEBITNEW','TLC') THEN 'DEBIT' -- Defined orginally with space, Crystal uses the space version for logo

ELSE 'UNDEFINED' -- Default logo

END AS CompanyName

FROM Customer

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

Luis Cazares SSC Guru Points: 183706 More actions · Answer 5

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'),

Customer AS(

SELECT 'CREDITNEW' AS Source,

1 AS CompanyID,

'Something' AS TermsofUse

UNION ALL

SELECT 'DOESITMATTER?' AS Source,

2 AS CompanyID,

'Something Else' AS TermsofUse

)

SELECT ISNULL(CAST( n.Name AS varchar(10)), 'UNDEFINED') AS CompanyName,

CompanyId,

TermsofUse

FROM Customer c

LEFT

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

Sush Ram SSC Rookie Points: 26 More actions · Answer 6

Thanks for the very good suggestions.

really appreciate it.