Click here to monitor SSC
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
Grasshopper
Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)

Group: General Forum Members
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
ChrisM@Work
ChrisM@Work
SSCrazy Eights
SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)

Group: General Forum Members
Points: 8921 Visits: 19002
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
Mr or Mrs. 500
Mr or Mrs. 500 (517 reputation)Mr or Mrs. 500 (517 reputation)Mr or Mrs. 500 (517 reputation)Mr or Mrs. 500 (517 reputation)Mr or Mrs. 500 (517 reputation)Mr or Mrs. 500 (517 reputation)Mr or Mrs. 500 (517 reputation)Mr or Mrs. 500 (517 reputation)

Group: General Forum Members
Points: 517 Visits: 382
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
SSCrazy Eights
SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)

Group: General Forum Members
Points: 8921 Visits: 19002
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
SSCrazy Eights
SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)

Group: General Forum Members
Points: 8921 Visits: 19002
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')
   Wink 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
Grasshopper
Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)

Group: General Forum Members
Points: 20 Visits: 79
Ok so my case statement should always read @jdt_jty_code?
ChrisM@Work
ChrisM@Work
SSCrazy Eights
SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)

Group: General Forum Members
Points: 8921 Visits: 19002
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
SSCertifiable
SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)

Group: General Forum Members
Points: 7349 Visits: 15066
I think I would create a lookup table, like this:

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

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
Hall of Fame
Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)

Group: General Forum Members
Points: 3934 Visits: 6660
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