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 123»»»

TSQL Case Statement help Expand / Collapse
Author
Message
Posted Tuesday, October 2, 2012 6:59 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Sunday, October 20, 2013 3:13 PM
Points: 20, Visits: 79
Hi All,

First off im a TSQL Novice...so help is needed and much appreciated

within several tables i have a job type code which describes the job type from which we can deduce what type of job (install or revisit),
Everytime i use one of these tables i write one looooonnng case statement...

I have been playing with the idea of using TSQL to do this for my but im coming up with errors every time...

this is what i have:

ALTER FUNCTION [dbo].[Tester] (@jdt_jty_code varchar(50))
Returns varchar(50)
as
Begin

Return
Select case When jdt_jty_code ='ISCO' Then 'Install'
When jdt_jty_code ='ISSP' Then 'Install'
When jdt_jty_code ='IECO' Then 'Install'
When jdt_jty_code ='IECM' Then 'Install'
When jdt_jty_code ='IESP' Then 'Install'
When jdt_jty_code ='IEHD' Then 'Install'
When jdt_jty_code ='ISHD' Then 'Install'
When jdt_jty_code ='FRSI' Then 'Install'
When jdt_jty_code ='SB42' Then 'Service Call'
When jdt_jty_code ='SB4W' Then 'Service Call'
When jdt_jty_code ='HD42' Then 'Service Call'
When jdt_jty_code ='HD4W' Then 'Service Call'
When jdt_jty_code ='SA2C' Then 'Service Call'
When jdt_jty_code ='SA2W' Then 'Service Call'
When jdt_jty_code ='HD2C' Then 'Service Call'
When jdt_jty_code ='HD2W' Then 'Service Call'
When jdt_jty_code ='SNCO' Then 'Service Call'
Else 'UNKNOWN'
END


END

Thanks in advance
Post #1367004
Posted Tuesday, October 2, 2012 7:04 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, September 12, 2014 9:43 AM
Points: 7,284, Visits: 13,824
Select case 
When jdt_jty_code IN ('ISCO','ISSP','IECO','IECM','IESP','IEHD','ISHD','FRSI') Then 'Install'
When jdt_jty_code IN ('SB42','SB4W','HD42','HD4W','SA2C','SA2W','HD2C','HD2W','SNCO') Then 'Service Call'
Else 'UNKNOWN' END



“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Post #1367009
Posted Tuesday, October 2, 2012 7:05 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, July 25, 2014 12:09 PM
Points: 437, Visits: 334
You're getting an error because the CASE statement needs to be enclosed in parentheses.

Roland Alexander
The Developing World


There are two means of refuge from the miseries of life: music and cats. ~ Albert Schweitzer
Post #1367011
Posted Tuesday, October 2, 2012 7:18 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, September 12, 2014 9:43 AM
Points: 7,284, Visits: 13,824
Roland Alexander STL (10/2/2012)
You're getting an error because the CASE statement needs to be enclosed in parentheses.


I'm unaware of this as a requirement in TSQL.
I think it's more likely because the @ has been dropped from the variables in the CASE construct.


“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Post #1367019
Posted Tuesday, October 2, 2012 7:19 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, September 12, 2014 9:43 AM
Points: 7,284, Visits: 13,824
You're better off using inline table-valued functions. Here's an example:

ALTER FUNCTION [dbo].[iTVF_Tester]
(@jdt_jty_code varchar(50))
RETURNS TABLE AS RETURN
SELECT ReturnValue
FROM (VALUES
('ISCO','Install'),
('ISSP','Install'),
('IECO','Install'),
('IECM','Install'),
('IESP','Install'),
('IEHD','Install'),
('ISHD','Install'),
('FRSI','Install'),
('SB42','Service Call'),
('SB4W','Service Call'),
('HD42','Service Call'),
('HD4W','Service Call'),
('SA2C','Service Call'),
('SA2W','Service Call'),
('HD2C','Service Call'),
('HD2W','Service Call'),
('SNCO','Service Call')
) x (jdt_jty_code, ReturnValue)
WHERE jdt_jty_code = @jdt_jty_code

GO
SELECT * FROM [dbo].[iTVF_Tester] ('IESP')

iTVF's behave like parameterised views. Google or search this site to find the advantages over scalar UDF's and multi-statement TVF's.


“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Post #1367020
Posted Tuesday, October 2, 2012 7:20 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Sunday, October 20, 2013 3:13 PM
Points: 20, Visits: 79
Ok so my case statement should always read @jdt_jty_code?
Post #1367021
Posted Tuesday, October 2, 2012 7:23 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, September 12, 2014 9:43 AM
Points: 7,284, Visits: 13,824
waqqas.zia (10/2/2012)
Ok so my case statement should always read @jdt_jty_code?


Yes - the @ tells SQL server that the object is a variable. Your scalar UDF works with changes;

create FUNCTION [dbo].[Tester] (@jdt_jty_code varchar(50))
Returns varchar(50)
as
Begin

Return
(
Select case When @jdt_jty_code ='ISCO' Then 'Install'
When @jdt_jty_code ='ISSP' Then 'Install'
When @jdt_jty_code ='IECO' Then 'Install'
When @jdt_jty_code ='IECM' Then 'Install'
When @jdt_jty_code ='IESP' Then 'Install'
When @jdt_jty_code ='IEHD' Then 'Install'
When @jdt_jty_code ='ISHD' Then 'Install'
When @jdt_jty_code ='FRSI' Then 'Install'
When @jdt_jty_code ='SB42' Then 'Service Call'
When @jdt_jty_code ='SB4W' Then 'Service Call'
When @jdt_jty_code ='HD42' Then 'Service Call'
When @jdt_jty_code ='HD4W' Then 'Service Call'
When @jdt_jty_code ='SA2C' Then 'Service Call'
When @jdt_jty_code ='SA2W' Then 'Service Call'
When @jdt_jty_code ='HD2C' Then 'Service Call'
When @jdt_jty_code ='HD2W' Then 'Service Call'
When @jdt_jty_code ='SNCO' Then 'Service Call'
Else 'UNKNOWN'
END
)

END




“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Post #1367025
Posted Tuesday, October 2, 2012 7:42 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 4:12 AM
Points: 5,369, Visits: 9,910
I think I would create a lookup table, like this:

CREATE TABLE CodeActions (
Code char(4)
, TheAction varchar(12)
)

INSERT INTO CodeActions (Code,TheAction)
VALUES
('ISSP','Install'),
('IECO','Install'),
('IECM','Install'),
('IESP','Install'),
('IEHD','Install'),
('ISHD','Install'),
('FRSI','Install'),
('SB42','Service Call'),
('SB4W','Service Call'),
('HD42','Service Call'),
('HD4W','Service Call'),
('SA2C','Service Call'),
('SA2W','Service Call'),
('HD2C','Service Call'),
('HD2W','Service Call'),
('SNCO','Service Call')

That way you don't have to play about with lengthy function definitions, nor rewrite them every time a code changes.

Your function becomes:
ALTER FUNCTION [dbo].[Tester] (@jdt_jty_code varchar(50))
Returns varchar(50)
as
Begin

Return
SELECT
COALESCE(TheAction,'UNKNOWN')
FROM
CodeActions
WHERE
Code = @jdt_jty_code

END

which is so trivial that you probably don't even need a function for it. If you decide to keep it, bear in mind what Chris said about table-valued vs scalar functions. If you're going to use this function on large amounts of data, you'll take a performance hit if you leave it like it is.

John
Post #1367037
Posted Tuesday, October 2, 2012 2:08 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 10:38 AM
Points: 1,945, Visits: 3,004
First off I am a TSQL Novice...so help is needed and much appreciated.


We cannot give you an education in a forum, so expect to start reading and studying on your own.

within several tables I have a job type code [sic] which describes the job type from which we can deduce what type of job (install or revisit), [quote]

Wrong; an attribute can be a “<something>_type” or “<something>_code” but not this weird hybrid. Read ISO-11179 rule or any book on basic data modeling.

[quote] ALTER FUNCTION [dbo].[Tester] (@jdt_jty_code VARCHAR(50))
RETURNS VARCHAR(50) ..


NO! We hate UDFs; they are not declarative, do not optimize and stink of 1950's FORTRAN. SQL is declarative and uses tables! This is a huge change in your mindset.

CREATE TABLE Job_Codes
(jdt_jty_code CHAR(4) NOT NULL PRIMARY KEY
CHECK (jdt_jty_code LIKE '[A-Z][A-Z][A-Z][0-9][0-9]'),
job_category VARCHAR(15) NOT NULL
CHECK ( job_category IN ('Install', 'Service Call', 'UNKNOWN'));

Now fill out this look-up table.

Now you can REFERENCE your jdt_jty_code in the other tables.


Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
Post #1367279
Posted Tuesday, October 2, 2012 4:11 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 4:51 PM
Points: 2,095, Visits: 3,146
SQL Server is optimized to do table lookups, whereas CASE statements are comparatively very slow.

Therefore, I suggest using a lookup table, as suggested by others. You absolutely want to make the lookup code the unqiue clustering key to the table, to speed up SQL's table search. You can make it an actual PRIMARY KEY also, if you want to, but that's not required.


SQL DBA,SQL Server MVP('07, '08, '09)

"We came in spastic, Like tameless horses /
We left in plastic, As numbered corpses / ...
Remember Charlie, Remember Baker /
They left their childhood On every acre /
And who was wrong? And who was right? /
It didn't matter in the thick of the fight." : the inimitable Mr. Billy Joel, about the Vietnam War
Post #1367320
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse