Maintainable way we could alter SQL logic

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

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

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources