Solved

Use a CASE expression to chose which WHERE to use

Posted on 2009-03-31
38
381 Views
Last Modified: 2012-05-06
I have the following table

CREATE TABLE [ValuesTable]
(
[RATING] [nvarchar](255) NULL,
[MT] [int] NULL,
[YR] [int] NULL,
[VAL1] [nvarchar](255) NULL,
[VAL2] [nvarchar](255) NULL
)

INSERT INTO ValuesTable (RATING,MT,YR,VAL1,VAL2) VALUES ('CRITICAL',3,2008,'10','15')
INSERT INTO ValuesTable (RATING,MT,YR,VAL1,VAL2) VALUES ('HIGH',1,2008,'2','28')
INSERT INTO ValuesTable (RATING,MT,YR,VAL1,VAL2) VALUES ('CRITICAL',2,2008,'90','16')
INSERT INTO ValuesTable (RATING,MT,YR,VAL1,VAL2) VALUES ('LOW',3,2008,'10','15')
INSERT INTO ValuesTable (RATING,MT,YR,VAL1,VAL2) VALUES ('LOW',5,2008,'89','34')

GO

What I want to do is dynamically select which WHERE clause to use depending on a variable that I pass.  I have this working but I think there might be a better (prettier) way to get this accomplished so I thought I would post.  Here is my current solution.

DECLARE @Which NVARCHAR(20)
SET @Which = 'Rate'

IF @Which  = 'Rate'
BEGIN

SELECT * FROM ValuesTable WHERE Rating = 'CRITICAL'

END

IF @Which  <> 'Rate'
BEGIN

SELECT * FROM ValuesTable WHERE Val1 = '90'

END

I don't like it because I have to show the same query 2 or more times depending on how many different ways I chose to allow the users to filter.  What I want to do is something like this.

***psuedo code***

SELECT * FROM ValuesTable
CASE WHEN 1 THEN
WHERE Rating = 'CRITICAL'
CASE WHEN 2 THEN
WHERE Val1 = '90'
CASE WHEN 3 THEN
WHERE Whatever = Whatever
END

And I can't get it to work properly.  Help is appreciated, and worth 500 points = )
0
Comment
Question by:jclemo
  • 13
  • 12
  • 9
  • +2
38 Comments
 
LVL 57

Expert Comment

by:Raja Jegan R
Comment Utility
You can use IF ELSE instead of CASE statement as given below:

Or you can create a dynamic sql based upon the input val @A and then execute it using sp_executesql

exec sp_executesql @sql

where @sql is the query framed using dynamic sql.


IF @A = 1 THEN

SELECT * FROM ValuesTable

WHERE Rating = 'CRITICAL'

ELSE @A = 2 THEN

SELECT * FROM ValuesTable

WHERE Val1 = '90'

ELSE @A = 2 THEN

SELECT * FROM ValuesTable

WHERE Whatever = Whatever

Open in new window

0
 
LVL 5

Expert Comment

by:defi0
Comment Utility
Well, you could write it like:

SELECT * FROM ValuesTable WHERE
(@Which <> 1 OR Rating=Critical)
AND
(@Which <> 2 OR Val=90)
AND
(@Which <> 3 OR Whatever=Whatever)
 
but I don't see how this is better...

In any case, you cannot use the CASE statement.
 
0
 
LVL 26

Expert Comment

by:Chris Luttrell
Comment Utility
I believe this is what you are looking for
DECLARE @Which NVARCHAR(20)

SET @Which = 'Rate'
 
 

SELECT * FROM ValuesTable 

WHERE Rating = CASE WHEN @Which = 'Rate' THEN 'CRITICAL' ELSE Rating END

AND Val1 = CASE WHEN @Which <> 'Rate' THEN '90' ELSE Val1 END
 

SET @Which = 'NotRate'
 

SELECT * FROM ValuesTable 

WHERE Rating = CASE WHEN @Which = 'Rate' THEN 'CRITICAL' ELSE Rating END

AND Val1 = CASE WHEN @Which <> 'Rate' THEN '90' ELSE Val1 END

Open in new window

0
 
LVL 26

Expert Comment

by:Chris Luttrell
Comment Utility
You can also put a CASE on both sides of the condition like this:
DECLARE @Which NVARCHAR(20)

SET @Which = 'Rate'

 

SELECT * FROM ValuesTable 

WHERE CASE WHEN @Which = 'Rate' THEN Rating ELSE Val1 END = CASE WHEN @Which = 'Rate' THEN 'CRITICAL' ELSE '90' END

Open in new window

0
 

Author Comment

by:jclemo
Comment Utility
thanks for the solutions all.  

rrjegan17:  your solution works great but it is similar to what I already have.  My goal was to write it so I don't have to duplicate the same query several times, but thanks.

CGLuttrell: your's gets me as close as possible but it some how filters out too much.. hmm
0
 
LVL 26

Expert Comment

by:Chris Luttrell
Comment Utility
What is is filtering out that it should not?  I tried it with your samples.  What are your expected results, maybe I can see what needs modified.
0
 

Author Comment

by:jclemo
Comment Utility
Thanks again CGLuttrell,  using your solution in 24030818.  I was expecitng that only one filter would be applied at a time depending on what I select as the filter value..  when I use the solution with LIVE data I get very few records returned when I should be gettting thousands.  I haven't had time to do any research as to why this happens yet, but I will try to re-create the problem with control data as soon as I get a minute.
0
 

Author Comment

by:jclemo
Comment Utility
I think it may have something to do with my joins... my actual query is as follows.  Thanks again.

DECLARE @ReportType NVARCHAR(20)
DECLARE @FilterValue NVARCHAR(20)

SET @ReportType= 'VO'
SET @FilterValue = 'TESTGUY'
 
SELECT  VValues.VVal
FROM VValues
INNER JOIN Assets ON VValues.GUID = Assets.GUID
INNER JOIN Contacts ON Assets.BusID = Contacts.BusID
WHERE
Assets.Owner = CASE WHEN @ReportType = 'VO' THEN @FilterValue ELSE Assets.Owner END
AND
Contacts.SubGroup = CASE WHEN @ReportType = 'SG' THEN @FilterValue ELSE Contacts.SubGroup END
AND
Contacts.ContactName = CASE WHEN @ReportType = 'BU' THEN @FilterValue ELSE Contacts.ContactName END
GO
0
 
LVL 26

Expert Comment

by:Chris Luttrell
Comment Utility
Your joins are going to force there to be a value in all 3 tables that satify the join conditions.  If that is not the case then you need to switch them to Left Outer Join.  You will have to move the condition in the where clause up to the join condition on tables you LOJ to.  Let me know more details about your expected results if this does not answer your questions and I can help craft the query better, there were too many options at this point to try.
0
 

Author Comment

by:jclemo
Comment Utility
So you would LOJ to an inline QUERY that filters the data then apply the filter there ??

Something like this?

SELECT  VValues.VVal
FROM VValues
INNER JOIN (SELECT * FROM ASSETS WHERE Assets.Owner = CASE WHEN @ReportType = 'VO' THEN @FilterValue ELSE Assets.Owner END)

ON VValues.GUID = Assets.GUID

INNER JOIN (SELECT * FROM Contacts WHERE Contacts.SubGroup = CASE WHEN @ReportType = 'SG' THEN @FilterValue ELSE Contacts.SubGroup END AND Contacts.ContactName = CASE WHEN @ReportType = 'BU' THEN @FilterValue ELSE Contacts.ContactName END)

ON Assets.BusID = Contacts.BusID
0
 

Author Comment

by:jclemo
Comment Utility
err...

SELECT  VValues.VVal
FROM VValues
INNER JOIN (SELECT * FROM ASSETS WHERE Assets.Owner = CASE WHEN @ReportType = 'VO' THEN @FilterValue ELSE Assets.Owner END) AS Assets

ON VValues.GUID = Assets.GUID

INNER JOIN (SELECT * FROM Contacts WHERE Contacts.SubGroup = CASE WHEN @ReportType = 'SG' THEN @FilterValue ELSE Contacts.SubGroup END AND Contacts.ContactName = CASE WHEN @ReportType = 'BU' THEN @FilterValue ELSE Contacts.ContactName END)  AS Contacts

ON Assets.BusID = Contacts.BusID
0
 
LVL 26

Expert Comment

by:Chris Luttrell
Comment Utility
You still have inner join instead of left outer join.  Try the following:
SELECT  VValues.VVal

FROM VValues

LEFT OUTER JOIN Assets ON VValues.GUID = Assets.GUID  AND Assets.Owner = CASE WHEN @ReportType = 'VO' THEN @FilterValue ELSE Assets.Owner END

LEFT OUTER JOIN Contacts ON Assets.BusID = Contacts.BusID AND Contacts.SubGroup = CASE WHEN @ReportType = 'SG' THEN @FilterValue ELSE Contacts.SubGroup END

AND 

Contacts.ContactName = CASE WHEN @ReportType = 'BU' THEN @FilterValue ELSE Contacts.ContactName END

GO

Open in new window

0
 
LVL 26

Expert Comment

by:Chris Luttrell
Comment Utility
Although I still think I have missed the exact condition you are looking for.  Basically I am trying to point out that you can have more than one condition in your joins.  Changing the inner join to a left outer join will still retrun all the data from the left side and no data from the right even if the condition of the join is not met, but since you are only Selecting 1 column from the primary table you will get all of them back, so I bet one of the LOJ still needs to be an Inner join or you want more columns returned to see what is not in the other tables.
0
 

Author Comment

by:jclemo
Comment Utility
Thanks again CGLuttrell:

What I'm looking for is essentially to return ONLY VVAalues.VVAL, no matter which field / table I chose to filter on.  

So I was hoping to get three separate queries rolled into one.

1.  FIlter on Assets.Owner only, any filters on Contacts.SubGroup or Contacts.ContactName are OFF
2.  FIlter on Contacts.SubGroup only, any filters on Assets.Owner or Contacts.ContactName are OFF
3.  FIlter on Contacts.ContactName only, any filters on Assets.Owner or Contacts.SubGroup  are OFF

Though in every case I only want to return the value of VVAalues.VVAL.  Actually I want to return COUNT(VVAalues.VVAL).  I have this working which I show at the top but it's really wordy so I thought there might be a better way, and I think we are very close.  Thanks in advance for your patience though, I'm relatively new to SQL syntax so I certainly appreciate the help you are providing.  
0
 

Author Comment

by:jclemo
Comment Utility
Maybe this will help, here is what I use now..

SELECT  VValues.VVal
FROM VValues
INNER JOIN Assets ON VValues.GUID = Assets.GUID
INNER JOIN Contacts ON Assets.BusID = Contacts.BusID
WHERE Contacts.ContactName = 'BOB'
GO

SELECT  VValues.VVal
FROM VValues
INNER JOIN Assets ON VValues.GUID = Assets.GUID
INNER JOIN Contacts ON Assets.BusID = Contacts.BusID
WHERE Assets.AssetOwner = 'JIM'
GO

SELECT  VValues.VVal
FROM VValues
INNER JOIN Assets ON VValues.GUID = Assets.GUID
INNER JOIN Contacts ON Assets.BusID = Contacts.BusID
WHERE Contacts.SubGroup = 'SG1'
GO


Then I have an if statement that decides which query to run based on the value.  I just wanted to consolidate so that I don't have to copy and paste the query 3 times, and it makes my SP's really long.  Thanks again, again.
0
 
LVL 26

Expert Comment

by:Chris Luttrell
Comment Utility
I created tables and ran your scripts above and the query we worked out before (snippet below for clarification) and get the same results for all cases.  I am attaching the script I used to create and populate the tables and run the queries.  You have not fully described the relation between the tables so I may be wrong in that they always join 1:1 instead of 1:M so correct me if I am wrong.
/* new query that will handle all cases */

SELECT  VValues.VVal

FROM VValues

INNER JOIN Assets ON VValues.GUID = Assets.GUID 

INNER JOIN Contacts ON Assets.BusID = Contacts.BusID

WHERE Assets.Owner = CASE WHEN @ReportType = 'VO' THEN @FilterValue ELSE Assets.Owner END

AND Contacts.SubGroup = CASE WHEN @ReportType = 'SG' THEN @FilterValue ELSE Contacts.SubGroup END

AND Contacts.ContactName = CASE WHEN @ReportType = 'BU' THEN @FilterValue ELSE Contacts.ContactName END

Open in new window

VariableCaseInWhere.txt
0
 

Author Comment

by:jclemo
Comment Utility
Heh.. does that make a difference here?  To be fair I did disclose that I was new to SQL syntax..hehe.  

I do have relationships defined as follows

One Contact has many assets
One Asset has many VValues

Thanks again, again, again for your help. = )
0
 
LVL 57

Expert Comment

by:Raja Jegan R
Comment Utility
As per you comment No 24034179, May I assume that if one condition is selected the other values are null like if ContactName is passed then Assetowner and Subgroup are null.

Hence you can rewrite your query as:

SELECT  VValues.VVal
FROM VValues
INNER JOIN Assets ON VValues.GUID = Assets.GUID
INNER JOIN Contacts ON Assets.BusID = Contacts.BusID
WHERE Contacts.ContactName LIKE ISNULL('BOB', '%')
AND Assets.AssetOwner LIKE ISNULL('JIM', '%')
AND Contacts.SubGroup LIKE ISNULL('SG1', '%')

Hope this helps out for you..
0
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
LVL 26

Expert Comment

by:Chris Luttrell
Comment Utility
jclemo,
I applied your relationships and created a larger set of data and set it up to run random test and they all come out matching the results of your 3 current queries.  I have attached a doc with the script and examples of the results.
If this still is not working then I need data examples of where it does not work.
QueryProof.doc
0
 
LVL 51

Expert Comment

by:Mark Wills
Comment Utility
Gosh, havenot yet digested all the new code snippets, and plan to do so...

In the meantime, the way I normally handle that type of thing (using your original pseudo-code ) is :

SELECT * FROM ValuesTable
WHERE 1 =  CASE WHEN 1 and Rating = 'CRITICAL' then 1
                             WHEN 2 and Val1 = '90' then 1
                             WHEN 3 and Whatever = Whatever then 1
                    ELSE 0
                    END
0
 
LVL 51

Accepted Solution

by:
Mark Wills earned 500 total points
Comment Utility
OK, have seen the new code snippets....


How are you calling your SP - by providing just the values and explictly understanding where they apply, or, are you also providing which column to use  ?

basically not sure if you are calling
1) exec my_sp('bob','jim','sg1')         -- we will call these parameters @V1,@V2,@V3 -- and are they mutually exclusive ? assuming that they are...
or somthing like
2) exec my_sp('ConactName','bob')   -- we will call these parameters @which, @v


in the case of 1) would do :

SELECT  VValues.VVal
FROM VValues
INNER JOIN Assets ON VValues.GUID = Assets.GUID
INNER JOIN Contacts ON Assets.BusID = Contacts.BusID
WHERE 1 = case when @v1 is not null and Contacts.ContactName = @v1 then 1                
                           when @v2 is not null and Assets.AssetOwner = @v2 then 1
                           when @v3 is not null and Contacts.SubGroup = @v3 then 1
                   else 0 end

-- or could use :  when contacts.contactname = isnull(@v1,contacts.contactname) then 1


in the case of 2) would do :

SELECT  VValues.VVal
FROM VValues
INNER JOIN Assets ON VValues.GUID = Assets.GUID
INNER JOIN Contacts ON Assets.BusID = Contacts.BusID
WHERE 1 = case when @which = 'ContactName' and Contacts.ContactName = @v then 1                
                           when @which = 'AssetOwner' and Assets.AssetOwner = @v then 1
                           when @which = 'SubGroup' and Contacts.SubGroup = @v then 1
                   else 0 end

OR, if running inside a stored procedure especially in the case of the last item, could use dynamic sql - but MUST safegaurd against sql injection and maybe best avoided:

declare @sql varchar(4000)
set @sql = 'SELECT  VValues.VVal
FROM VValues
INNER JOIN Assets ON VValues.GUID = Assets.GUID
INNER JOIN Contacts ON Assets.BusID = Contacts.BusID
WHERE ' + @which + ' = ' + ''' + @v + '''

of course there is a bit more work depending on datatypes and actual column names in the @which etc...
0
 

Author Comment

by:jclemo
Comment Utility
Thanks for the response mark,

I'm not sure I understand this one.. when I "adapt" it to live data I get an error that states "an expression of non-boolean type specified in a context where a condition is expected" where it says "WHEN 1, WHEN 2, WHEN 3" etc..
0
 
LVL 51

Expert Comment

by:Mark Wills
Comment Utility
oops, that last dynamic one was not quite complete :


declare @sql varchar(4000)
set @sql = 'SELECT  VValues.VVal
FROM VValues
INNER JOIN Assets ON VValues.GUID = Assets.GUID
INNER JOIN Contacts ON Assets.BusID = Contacts.BusID
WHERE ' + @which + ' = ''' + @v + ''''
exec (@sql)
0
 
LVL 51

Expert Comment

by:Mark Wills
Comment Utility
the example "WHEN 1 and Rating = 'CRITICAL' then 1" was using the same psuedo code as your original posting and 1 should really be replaced with the expression for the criteria represented by 1. Have a look at the other postings, probably explains it a bit better.
0
 
LVL 26

Expert Comment

by:Chris Luttrell
Comment Utility
jclemo,
Was there a problem with the script I sent you?  I would like to know if there is for my own knowledge.  If not why are you switching directions, I put a bit of work into setting up and testing queries vs just making some wild guesses.
0
 
LVL 51

Expert Comment

by:Mark Wills
Comment Utility
Excuse me CGLuttrell - are you suggesting I am making wild guesses ?  Why not try them before such accusations !

Please choose your words a little more carefully. I did point out that I had not read all the snippets, and having done so, saw that the psuedo code approach had been abandoned, so, resurected it, but also used your example of providing all three parameters just in case that change of direction was preferred.

Cheers,
Mark Wills
0
 

Author Comment

by:jclemo
Comment Utility
I got Mr. Wills solution working straight away and received the results I had expected, but I CERTAINLY wouldn't want to take a smidgen of credit away from CGLuttrell, who obviously dedicated a TON of time to helping me and even created documents for me to follow, which had to be time consuming.  I sincerely appreciate your help.  

CGLuttrell, I'm not sure why the results didn't return as expected to be honest.  In your last post  #24036540 I literally copy and pasted the solution into query analyzer but the numbers came back way low.  

(e.g.. with my control filter I get 19+k records but when I try your solution I get only 700+),
if I knew why this happened I wouldn't have had to ask for help, hehe.  I get the sense that when using the WHERE statement of VO, for instance, one of the other filters is also affecting the results, I dunno.  

Anyway, I sincerely do appreciate your help and will give you the lions share of the points to reflect the time spent.  However, I certainly can't forger Mr. Wills.  I really wish I could give you both 500 points.  Thanks again to both of you.
0
 
LVL 51

Expert Comment

by:Mark Wills
Comment Utility
hmmmm... my solution worked straight away, but i get an assist, and the solution that didn't work is accepted, interesting.

And I get accused by CGluttrel as making wild guesses.

What is happening here ? Certainly not in the spirit of EE and fair play.

jclemo - never have to worry about taking pity, if it is not working, it is not working, and instead of CGLuttrell making wild accusations, and getting rewarded, should really be admonished and be trying to work a bit harder to make it easier for the Asker to accept the answer.

if you want to give CGLuttrell extra points, then choose a few of those posts that helped you reach an answer, not just because of the volume of typing.


0
 

Author Comment

by:jclemo
Comment Utility
Sorry.. I had to do something.. I can't make everyone happy all the time I guess.  I thought I was doing the right thing, but I guess not.  I STILL appreciate everyones effort, heh.  
0
 
LVL 51

Expert Comment

by:Mark Wills
Comment Utility
Tis OK, was really wanting to hear from CGLuttrell.

You can award points however you want, that is your perogative. The "accepted" solution albeit not working reflects on you a little less favourably, and I was really trying to encourage you not to worry too much about various efforts. I have seen some go for quite some time, then a single statement, even a one liner makes the whole think suddenly work - totally regardless of any other inputs - it can, does and will happen that way.

Most of us here in EE are tuff enough, but one thing that does tend to get to experts is having the right solution but not being recognised.

Enjoy EE, and use it to your advantage, and award points as you will, just remember about recognising the working solution, not the hardest worker.
0
 
LVL 26

Expert Comment

by:Chris Luttrell
Comment Utility
mark_wills and jclemo,
I am sorry if I came off harsh there, I really do not want credit if the answer did not work.  As I stated above I really want to know what the solution is for my own knowledge.  I have always learned more by helping others, that is why I do this and put so much time in to it. If jclemo would request attention and have them remove the acceptance and give it to mark where it belongs that is the right thing to do and leaving it on my answer will confuse other users.  I just wanted reed back and to learn from the final solution.  And mark, I am sorry to have implied you were guessing at an answer.  I just have had solutions snatched out from under me by incomplete answers with typos that won't run while I am trying to test and run a complete statement.  It happened the other day where someone acccepted the solution above mine too quickly and then came back and said it did not work..  The other expert admited it was just his first thought and it was my first thought also, but since I was testing and saw the problem and was working on a working solution while he got the credit.  You just unfairly bore my pent up frustration and I do appologize.  In a small defence, he had not said that yours worked yet.
0
 
LVL 51

Expert Comment

by:Mark Wills
Comment Utility
Understand - It happens to us all, but need to keep vigilant about your lanaguage - we are all volunteers and it does get frustrating.

Thanks for posting back... All is good again...
0
 
LVL 26

Expert Comment

by:Chris Luttrell
Comment Utility
Your welcome.  And I do want jclemo to have the acceptance reversed to not confuse others.  If I need to make the request I can, but I thought I would let him respond first.
0
 

Author Comment

by:jclemo
Comment Utility
Wow.. ok, how do I move the points?
0
 
LVL 26

Expert Comment

by:Chris Luttrell
Comment Utility
Under your question at the top there is a Request Attention button.  Ask them to reopen it so you can correct the acceptance.
BTW: for others general knowledge, I figured out why we were getting different results.  The test tables I set up had the 3 fields on the different tables all mandatory so I always had a value in all 3 and my logic required that, but Mark's correctly looked at only one and the others being null would not interfere.
0
 

Author Closing Comment

by:jclemo
Comment Utility
I used the first on which worked straight away.. thanks again.
0
 
LVL 51

Expert Comment

by:Mark Wills
Comment Utility
Thanks very much to all. The space time continuum has been restored and all is good...
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now