SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


TSQL Case Statement help


TSQL Case Statement help

Author
Message
waqqas.zia
waqqas.zia
Valued Member
Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)

Group: General Forum Members
Points: 74 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
ChrisM@Work
ChrisM@Work
SSC-Forever
SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)

Group: General Forum Members
Points: 41785 Visits: 20007
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
Roland Alexander STL
Roland Alexander STL
SSC Eights!
SSC Eights! (990 reputation)SSC Eights! (990 reputation)SSC Eights! (990 reputation)SSC Eights! (990 reputation)SSC Eights! (990 reputation)SSC Eights! (990 reputation)SSC Eights! (990 reputation)SSC Eights! (990 reputation)

Group: General Forum Members
Points: 990 Visits: 491
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
ChrisM@Work
ChrisM@Work
SSC-Forever
SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)

Group: General Forum Members
Points: 41785 Visits: 20007
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
ChrisM@Work
ChrisM@Work
SSC-Forever
SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)

Group: General Forum Members
Points: 41785 Visits: 20007
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
waqqas.zia
waqqas.zia
Valued Member
Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)

Group: General Forum Members
Points: 74 Visits: 79
Ok so my case statement should always read @jdt_jty_code?
ChrisM@Work
ChrisM@Work
SSC-Forever
SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)

Group: General Forum Members
Points: 41785 Visits: 20007
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
John Mitchell-245523
John Mitchell-245523
SSC-Dedicated
SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)

Group: General Forum Members
Points: 34931 Visits: 16663
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
ScottPletcher
ScottPletcher
SSCoach
SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)

Group: General Forum Members
Points: 19777 Visits: 7410
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) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial:
If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search