Using IN in a Where statement with and option of all

  • I'm trying to use a
    Where TS.Skill IN (@TS)
    and it works fine. πŸ™‚
    The user can select one or any number of options from a drop down.

    But now I'm trying something like this.
    And (TS.Skill In (@TS) or @TS = '')
    And it is failing. πŸ™

    I've also tried
    And 1 = Case
    When @TS = 'All' Then 1
    When @TS <> 'All' And TS.Skill In (@TS) Then 1
    Else 0
    End

    I'd like the user to be able to select all, or any number of individual ones. I cannot use the 'Select All' from the drop down because the parameters are feed from another program and the drop down is not available. The best way, I thought, was to have one of the drop down options to be 'All'. If that is entered it act the same as if 'Select All' was chosen. However I can't seem to get it to work.

    Any help would be appreciated.

  • Does @TS have a value of '', or does it not have a value (NULL)? To test for an empty value (variable or field), you need to use
    @Field IS NULL

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
    Larnu.uk

  • Ken at work - Thursday, February 2, 2017 7:15 AM

    I'm trying to use a
    Where TS.Skill IN (@TS)
    and it works fine. πŸ™‚
    The user can select one or any number of options from a drop down.

    But now I'm trying something like this.
    And (TS.Skill In (@TS) or @TS = '')
    And it is failing. πŸ™

    I've also tried
    And 1 = Case
    When @TS = 'All' Then 1
    When @TS <> 'All' And TS.Skill In (@TS) Then 1
    Else 0
    End

    I'd like the user to be able to select all, or any number of individual ones. I cannot use the 'Select All' from the drop down because the parameters are feed from another program and the drop down is not available. The best way, I thought, was to have one of the drop down options to be 'All'. If that is entered it act the same as if 'Select All' was chosen. However I can't seem to get it to work.

    Any help would be appreciated.

    IN (@Variable) doesn't usually work because the column isn't compared to comma-separated elements in the variable, it's compared to the whole variable. There's no "macro substitution". Here's a very simple mockup of this:

    IF OBJECT_ID('tempdb..#Clients') IS NOT NULL DROP TABLE #Clients; CREATE TABLE #Clients (Surname VARCHAR(30), Skill VARCHAR(2));

    INSERT INTO #Clients (Surname, Skill) VALUES

    ('Smith', 'AB'), ('Smith', 'AB'), ('Smith', 'AB'), ('Smith', 'AB'),

    ('Jones', 'CD'), ('Jones', 'CD'), ('Jones', 'CD'), ('Jones', 'CD'), ('Jones', 'CD'),

    ('Sahathevarajan', 'EF'), ('Sahathevarajan', 'EF');

    SELECT Surname, Skill FROM #Clients;

    DECLARE @TS VARCHAR(100) = 'AB,CD,EF'

    SELECT Surname, Skill FROM #Clients TS WHERE TS.Skill IN (@TS)

    β€œ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

  • Thom A - Thursday, February 2, 2017 7:28 AM

    Does @TS have a value of '', or does it not have a value (NULL)? To test for an empty value (variable or field), you need to use
    @Field IS NULL

    For compatibility reasons I'm not testing for either '' or Null. Specifically I'm trying to get @TS = 'All' to work.
    Also testing for Where @TS = 'All' or or testing for Where IN both work.

    Just not together.

  • Ken at work - Thursday, February 2, 2017 7:49 AM

    Thom A - Thursday, February 2, 2017 7:28 AM

    Does @TS have a value of '', or does it not have a value (NULL)? To test for an empty value (variable or field), you need to use
    @Field IS NULL

    For compatibility reasons I'm not testing for either '' or Null. Specifically I'm trying to get @TS = 'All' to work.
    Also testing for Where @TS = 'All' or or testing for Where IN both work.

    Just not together.

    What code are you using for this, Ken? Looking at the execution plan from the little test ^^, you see this in the search predicate:

    SELECT Surname, Skill FROM #Clients TS WHERE TS.Skill IN (@TS)

    -- Predicate: [tempdb].[dbo].[#Clients].[Skill] as [TS].[Skill]=[@TS]

    SELECT Surname, Skill FROM #Clients TS WHERE TS.Skill IN ('AB','CD')

    -- Predicate: [tempdb].[dbo].[#Clients].[Skill] as [TS].[Skill]='AB' OR [tempdb].[dbo].[#Clients].[Skill] as [TS].[Skill]='CD'

    β€œ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

  • Ken at work - Thursday, February 2, 2017 7:49 AM

    Thom A - Thursday, February 2, 2017 7:28 AM

    Does @TS have a value of '', or does it not have a value (NULL)? To test for an empty value (variable or field), you need to use
    @Field IS NULL

    For compatibility reasons I'm not testing for either '' or Null. Specifically I'm trying to get @TS = 'All' to work.
    Also testing for Where @TS = 'All' or or testing for Where IN both work.

    Just not together.

    I'm very surprised that selecting multiple values with TS.Skill IN (@TS) works. As Chris pointed out, SQL doesn't convert comma delimited strings into a range of values to match.

    Personally, if you are using a comma delimited string, I would expect to use the DelimitedSplit8K such as:
    WHERE (TS.Skill IN (SELECT Item FROM dbo.DelimitedSplit8K(@TS, ','))
       OR @TS = 'All')

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
    Larnu.uk

  • I don't believe it is a comma delimited string; I'm still relatively new with SSRS. The program, if run on a web page from the SSRS server, will use the standard SSRS drop down. But when launched from our ERP the @TS can only be a single value. So I'm trying to get both to work.

    If I use:
    Where
    HRMst.ActiveYN = 'Y'
    And HRMst.Co = @Company
    And (TS.Skill In (@TS) or @TS = 'All')

    When @TS = 'All' -  it works.
    When @TS = and single selection -  it works
    When @TS = multiple selections from the drop down in SSRS it fails.

  • P.S. If I use
    Where
      HRMst.ActiveYN = 'Y'
      And HRMst.Co = @Company
      And TS.Skill In (@TS)

    It works with multiple selections, but that doesn't give me the ability to enter/select "All"; which isn't the same thing as clicking the 'Select All' from the drop down.

  • Considering that you are using the IN clause, it is HEAVILY implied that you are providing multiple values, as that's what the IN clause is for. if you are only supplying one value, use the = operator.

    For multiple values, see my post about Delimited8KSplit.

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
    Larnu.uk

  • Let me elaborate a bit more. We've not had a lot of information on your SSRS report set up, so I'm running on guesswork here however.

    I assume you are getting a list of your values you can select from @TS on your SSRS report from a dataset. Maybe something like:
    SELECT DISTINCT Skill
    FROM MyTable;

    This is only going to provide you with a list of valid  Skills, which I would imagine won't have a skill called "All". You'll need to therefore use a query like:
    SELECT DISTINCT Skill
    FROM MyTable
    UNION
    SELECT 'All' AS Skill;

    This gives you a skill "All" in your selection box.

    THEN you can use the WHERE statement i provided above:
    WHERE (TS.Skill IN (SELECT Item FROM dbo.DelimitedSplit8K(@TS, ','))
       OR @TS = 'All')

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
    Larnu.uk

  • Thom A - Thursday, February 2, 2017 8:28 AM

    THEN you can use the WHERE statement i provided above:
    WHERE (TS.Skill IN (SELECT Item FROM dbo.DelimitedSplit8K(@TS, ','))
       OR @TS = 'All')

    I'm not sure what SELECT Item FROM dbo.DelimitedSplit8K is and I can't get it to run for me.

  • Ken at work - Thursday, February 2, 2017 8:42 AM

    Thom A - Thursday, February 2, 2017 8:28 AM

    THEN you can use the WHERE statement i provided above:
    WHERE (TS.Skill IN (SELECT Item FROM dbo.DelimitedSplit8K(@TS, ','))
       OR @TS = 'All')

    I'm not sure what SELECT Item FROM dbo.DelimitedSplit8K is and I can't get it to run for me.

    Thom A - Thursday, February 2, 2017 7:58 AM

    I'm very surprised that selecting multiple values with TS.Skill IN (@TS) works. As Chris pointed out, SQL doesn't convert comma delimited strings into a range of values to match.

    Personally, if you are using a comma delimited string, I would expect to use the DelimitedSplit8K such as:
    WHERE (TS.Skill IN (SELECT Item FROM dbo.DelimitedSplit8K(@TS, ','))
       OR @TS = 'All')

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
    Larnu.uk

  • Ken at work - Thursday, February 2, 2017 8:42 AM

    Thom A - Thursday, February 2, 2017 8:28 AM

    THEN you can use the WHERE statement i provided above:
    WHERE (TS.Skill IN (SELECT Item FROM dbo.DelimitedSplit8K(@TS, ','))
       OR @TS = 'All')

    I'm not sure what SELECT Item FROM dbo.DelimitedSplit8K is and I can't get it to run for me.

    It's a function you would have to add as it is not part of SQL.  Check out this article for more details as well as the code itself.
    http://www.sqlservercentral.com/articles/Tally+Table/72993/


    SELECT quote FROM brain WHERE original = 1
    0 rows returned

  • TS.Skill IN (@TS) works perfectly. I don't know if the drop down list of the SSRS parameter creates a a comma delimited string. I didn't think that was what was happening.

    But I did find an alternate, though not perfect, solution.

    You are correct this is what I'm been using from the start:
    SELECT DISTINCT Skill
    FROM MyTable
    UNION
    SELECT 'All' AS Skill;

    So the first thing I did was to eliminate the 'UNION SELECT 'All' AS Skill' part.  Then in the SSRS report, Parameter Properties I selected the query as the default Value. Doing this causes the report, when run, to have all of the selections checked. Finally I dropped that parameter from being passed from our ERP. Since it isn't passed to beging with the default will always be used.

    Basically if lauched from our ERP then it will have the effect of chosing 'Select All'. When run from a web page the user will see the drop down and be able to limit the list. Of course my preference would have been to allow the user the option of selecting all or a a single skill when using the ERP. Not the preferred solution but definitely workable.

  • Try using
    And (TS.Skill In (@TS) or NULLIF(@TS, '') IS NULL)
    It gets around the NULL value issue.

    Jay

Viewing 15 posts - 1 through 15 (of 32 total)

You must be logged in to reply to this topic. Login to reply