Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SQL server conditional case statement in where clause using IN clause

Posted on 2014-07-23
6
Medium Priority
?
1,138 Views
Last Modified: 2014-07-25
Hi,
I have a situation where I need to filter based on a variable that is being set on a webpage, and if the variable isn't set, then I have to filter based on a comma-delimited string to see if there's a match.
Here it is:
First, I build the comma delimited string, with a STUFF in the FROM clause of the select statement - as follows:
 outer apply
 (
 select
  STUFF
  (
   (    
    select distinct
     ', ' + dx.class
FROM
data_RECORDS sr
 Join data_Xref dx ON sr.Text = dx.text
WHERE sr.userid = @vUID
    order by
     ', ' + dx.class
    for xml path ('')
   ),
   1,
   2,
   ''
  )
 ) mg (Classes)

mg.Classes now contains a long string, comma delimited, of the various classes for this particular user (@Vuid)

Now I want to add an additional condition to the WHERE clause I want to do the filtering.
If a variable with a class name was selected on the input screen, I only want to include that particular class.
If the variable from the input screen is NULL or '', then I want to include all classes from mg.Classes, and I want to filter out the classes that are not part of that string.
I tried many things in the WHERE clause, and they aren't working.
Even though the value of string is correct (it's a comma-delimited string of classes), the WHERE clause does not like the "IN" and is returning an error (Incorrect syntax near the keyword 'CASE'.)

How can I filter to see if the class in the table is in the list of allowed classes that was returned by the STUFF.

Here's a sample:
 WHERE dx.CLASS IN
       (      CASE
      
             when @vClass is NULL
                  then '(' + mg.Classes + ')'
            when @vClass = '' then '(' + mg.Classes + ')'
ELSE @vClass  END )
            
This is complicated, but any help would be GREATLY appreciated!
0
Comment
Question by:avivap
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
6 Comments
 
LVL 49

Accepted Solution

by:
PortletPaul earned 2000 total points
ID: 40216114
If you are you generating the query through something like C#/PHP or similar then using comma separated strings can work - but you need to supply more of the script generating that sql.

as it looks right now you won't get valid sql syntax from that case expression.

---
Despite the fact that A comma separated string looks like an IN() statement, it isn't. It is still ONE string.

if mg.Classes looks: just,like,this,separated,series

when actually being assessed it is treated as a 'single value'

WHERE dx.CLASS IN( 'just,like,this,separated,series' )

and 'just' or 'like' or 'this' or 'separated' or 'series' will not match to any part of  'just,like,this,separated,series'

Could you not use something like this?
 WHERE 
        ( 
         (@vClass is NULL OR @vClass = '') AND dx.CLASS  IN ( 
                                                    select dx.class FROM data_RECORDS sr
                                                    Join data_Xref dx ON sr.Text = dx.text 
                                                    WHERE sr.userid = @vUID         
                                                 ) 
         )
OR 
        ( 
         (@vClass is NOT NULL OR @vClass <> '') AND dx.CLASS = @vClass
         )

Open in new window

0
 
LVL 35

Expert Comment

by:ste5an
ID: 40216171
Think relational and atomic. Thus don't look in the resulting string, but in the raw data. Otherwise you may use an approach like
WHERE   ( COALESCE(@vClass, '') != ''
          AND mg.Classes LIKE '%' + dx.CLASS + '%'
        )
        OR ( COALESCE(@vClass, '') = ''
             AND @vClass = dx.CLASS
           )

Open in new window


But this requires a sligthly different class string. But this leads to the question, why concatenating the values first and searching the result and not in the raw data?
0
 

Author Comment

by:avivap
ID: 40218184
HI.
Thanks for your quick response!
Portletpaul - I tried your solution - and it works.  The only issue is that it takes a lot longer to run, I'm imagining it's because of the additional select in the WHERE clause.  Any ideas regarding optimizing?

Ste5an - I haven't tried yours because you wrote 'But this requires a slightly different class string' - would you mind explaining further?

I'm waiting to hear back from both of you.
Thanks.
0
Survive A High-Traffic Event with Percona

Your application or website rely on your database to deliver information about products and services to your customers. You can’t afford to have your database lose performance, lose availability or become unresponsive – even for just a few minutes.

 
LVL 49

Expert Comment

by:PortletPaul
ID: 40218274
consider indexing these:

dx.class
sr.Text
dx.text
sr.userid  

and in general that statistics are kept up to date and that  indexes are maintained

You can try 'select distinct' in that small subquery it might help (or it might not) I cannot be certian
0
 
LVL 35

Expert Comment

by:ste5an
ID: 40218729
The condition mg.Classes LIKE '%' + dx.CLASS + '%' works only when your class names are not reused as partial name. E.g. the classes Red and CadmiumRed. In the case of the class Red we could get an condition like 'CadmiumRed LIKE '%Red%', which is a false positive. This could be avoided by using ',' + mg.Classes + ',' LIKE '%,' + dx.CLASS + ',%', as concrete expression ',CadmiumRed,' LIKE '%,Red,%'. Now it works under the constraint that no comma is allowed in the content of the atomic class values.
0
 

Author Closing Comment

by:avivap
ID: 40220435
Thank you both for your help!
0

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

715 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