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

Where clause Case Statement and IN Expand / Collapse
Author
Message
Posted Monday, December 24, 2012 1:04 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, May 17, 2013 1:15 PM
Points: 114, Visits: 186
How do I correct the following statement:

Declare @Component smallint = -1

Select srcComment.Comment As Comment, t.NameFull As CommentType, parenttypeId As Component,
(select Quarter From dimPeriod where periodId = srcComment.ParentId) As Quarter,
(select Year From dimPeriod where periodId = srcComment.ParentId) As periodYear
From srcComment
inner JOIN dimPeriod p
ON srcComment.ParentId = p.periodId
Left Outer Join refType t
ON t.TypeId = srcComment.ParentTypeId
where case @Component
when -1 then
parenttypeId IN (601, 605, 606)
else parenttypeId = @Component End
Post #1399981
Posted Monday, December 24, 2012 1:05 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 7:23 PM
Points: 20,860, Visits: 32,883
Faye Fouladi (12/24/2012)
How do I correct the following statement:

Declare @Component smallint = -1

Select srcComment.Comment As Comment, t.NameFull As CommentType, parenttypeId As Component,
(select Quarter From dimPeriod where periodId = srcComment.ParentId) As Quarter,
(select Year From dimPeriod where periodId = srcComment.ParentId) As periodYear
From srcComment
inner JOIN dimPeriod p
ON srcComment.ParentId = p.periodId
Left Outer Join refType t
ON t.TypeId = srcComment.ParentTypeId
where case @Component
when -1 then
parenttypeId IN (601, 605, 606)
else parenttypeId = @Component End


Hard to say, you tell us the error message you received.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1399982
Posted Monday, December 24, 2012 1:07 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, May 17, 2013 1:15 PM
Points: 114, Visits: 186
I basically want to say if @Component = -1 then parentTypeId In (601, 605, 606) else parentTypeId = @Component
Post #1399983
Posted Monday, December 24, 2012 1:08 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, May 17, 2013 1:15 PM
Points: 114, Visits: 186
I get an error message near = on last line
Post #1399984
Posted Monday, December 24, 2012 1:12 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 7:23 PM
Points: 20,860, Visits: 32,883
I would do it something like this:


Declare @Component smallint = -1

Select
sc.Comment As Comment,
t.NameFull As CommentType,
sc.parenttypeId As Component,
dt.periodQuarter,
dt.periodYear
From
srcComment sc
inner JOIN dimPeriod p
ON srcComment.ParentId = p.periodId
Left Outer Join refType t
ON t.TypeId = srcComment.ParentTypeId
outer apply (select Quarter, Year from dimPeriod dp where dp.periodId = sc.ParentId)dt(periodQuarter, periodYear)
where
(@Component = -1 and sc.parenttypeId IN (601, 605, 606)) or
(sc.parenttypeId = @Component);





Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1399985
Posted Monday, December 24, 2012 1:18 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, May 17, 2013 1:15 PM
Points: 114, Visits: 186
Thank you, it worked.
Post #1399986
Posted Monday, December 24, 2012 6:25 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: 2 days ago @ 9:53 PM
Points: 3,438, Visits: 5,390
Lynn has pointed you at a catch-all query that will work as you have indicated. But you should also read this article SQL in the Wild - Catch All Queries by Gail Shaw in case this method causes you performance issues.

I use catch-all queries myself in the same way that Lynn recommended, so this post is by no means a slight on his suggestion.



My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1400010
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse