Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Are there alternatives to this SQL logic Expand / Collapse
Author
Message
Posted Tuesday, October 22, 2013 10:08 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, October 22, 2013 2:32 PM
Points: 2, Visits: 8
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
Post #1507233
Posted Tuesday, October 22, 2013 1:11 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Tuesday, September 23, 2014 8:31 AM
Points: 4,358, Visits: 9,537
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
Problems are opportunites brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs
Post #1507305
Posted Tuesday, October 22, 2013 1:13 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 8:15 AM
Points: 13,017, Visits: 10,800
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





How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1507306
Posted Tuesday, October 22, 2013 1:33 PM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Today @ 8:59 AM
Points: 760, Visits: 2,220
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.
Post #1507327
Posted Tuesday, October 22, 2013 1:43 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 8:56 AM
Points: 3,626, Visits: 8,132
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.
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?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1507335
Posted Tuesday, October 22, 2013 1:52 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 8:56 AM
Points: 3,626, Visits: 8,132
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.
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?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1507342
Posted Tuesday, October 22, 2013 2:33 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, October 22, 2013 2:32 PM
Points: 2, Visits: 8
Thanks for the very good suggestions.

really appreciate it.
Post #1507371
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse