Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Maintainable way we could alter SQL logic

Author  Topic 

urpalshu
Starting Member

21 Posts

Posted - 2013-10-22 : 11:57:45
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.

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

Thank you,

djj55
Constraint Violating Yak Guru

352 Posts

Posted - 2013-10-22 : 12:33:51
Have you tried to use a lookup table.

Then you can use a join.

djj
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-22 : 14:39:31
do you mean this?

SELECT CASE
WHEN RTRIM(Customer.Source) IN ('CREDITNEW','CREDITOLD','TOC') THEN 'CREDIT'
WHEN RTRIM(Customer.Source) = 'DEBIT' THEN 'UC DEBIT'
WHEN RTRIM(Customer.Source) IN ('DEBITOLD','DEBITNEW','TLC') THEN 'DEBIT'
ELSE 'UNDEFINED' -- Default logo
END AS CompanyName, CompanyId, TermsofUse
FROM Customer


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-10-22 : 14:57:16
Did you mean something like this (which is what djj55 was alluding to, if I am not mistaken)
SELECT 
COALESCE(l.TargetTerm,'UNDEFINED') AS CompanyName,
CompanyId, TermsofUse
FROM Customer c
LEFT JOIN #Lookup l ON l.SourceTerm = RTRIM(c.Source);


The lookup table would be like this:
CREATE TABLE #Lookup(SourceTerm VARCHAR(32), TargetTerm VARCHAR(32));
INSERT INTO #Lookup
( SourceTerm, TargetTerm )
VALUES
('CREDITNEW','CREDIT'),
('CREDITOLD' , 'CREDIT'),
('TOC' , 'CREDIT'),
'DEBIT' , 'UC DEBIT'
('DEBITOLD' , 'DEBIT' ),
('DEBITNEW' , 'DEBIT' ),
('TLC' , 'DEBIT');
Go to Top of Page
   

- Advertisement -