TSQL Case Statement help

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » T-SQL (SS2K8) » TSQL Case Statement help

21 posts, Page 1 of 3

1 2 3 »»»

TSQL Case Statement help

Rate Topic

Display Mode

Topic Options

Author

Message

waqqas.zia

Posted Tuesday, October 02, 2012 6:59 AM

Grasshopper

Group: General Forum Members
Last Login: Thursday, April 11, 2013 9:49 AM
Points: 19, Visits: 75

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

ChrisM@Work

Posted Tuesday, October 02, 2012 7:04 AM

SSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:27 AM
Points: 5,618, Visits: 10,990

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

Roland Alexander STL

Posted Tuesday, October 02, 2012 7:05 AM

Old Hand

Group: General Forum Members
Last Login: Tuesday, May 21, 2013 11:10 AM
Points: 306, Visits: 311

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

ChrisM@Work

Posted Tuesday, October 02, 2012 7:18 AM

SSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:27 AM
Points: 5,618, Visits: 10,990

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}

Post #1367019

ChrisM@Work

Posted Tuesday, October 02, 2012 7:19 AM

SSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:27 AM
Points: 5,618, Visits: 10,990

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}

Post #1367020

waqqas.zia

Posted Tuesday, October 02, 2012 7:20 AM

Grasshopper

Group: General Forum Members
Last Login: Thursday, April 11, 2013 9:49 AM
Points: 19, Visits: 75

Ok so my case statement should always read @jdt_jty_code?

Post #1367021

ChrisM@Work

Posted Tuesday, October 02, 2012 7:23 AM

SSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:27 AM
Points: 5,618, Visits: 10,990

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}

Post #1367025

John Mitchell-245523

Posted Tuesday, October 02, 2012 7:42 AM

SSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 7:19 AM
Points: 4,434, Visits: 7,217

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

CELKO

Posted Tuesday, October 02, 2012 2:08 PM

SSCommitted

Group: General Forum Members
Last Login: Tuesday, January 15, 2013 11:11 AM
Points: 1,945, Visits: 2,782

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

ScottPletcher

Posted Tuesday, October 02, 2012 4:11 PM

Ten Centuries

Group: General Forum Members
Last Login: 2 days ago @ 3:56 PM
Points: 1,324, Visits: 1,778

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)
One man with courage makes a majority. Andrew Jackson

Post #1367320

« Prev Topic | Next Topic »

21 posts, Page 1 of 3

1 2 3 »»»

Permissions