Using IN in a Where statement with and option of all

Question

Using IN in a Where statement with and option of all

Viewing 15 posts - 16 through 30 (of 32 total)

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

Ken at work Hall of Fame Points: 3426 More actions · Answer 1

Jay McGrath - Friday, February 3, 2017 8:15 AM
Try using
And (TS.Skill In (@TS) or NULLIF(@TS, '') IS NULL)
It gets around the NULL value issue.
Jay

I will try that next week.

Thank you

Ken at work Hall of Fame Points: 3426 More actions · Answer 2

Jay McGrath - Friday, February 3, 2017 8:15 AM
Try using
And (TS.Skill In (@TS) or NULLIF(@TS, '') IS NULL)
It gets around the NULL value issue.
Jay

Sorry,

That still didn't work.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

Ken, in one of your earlier posts you state "But when launched from our ERP the @TS can only be a single value".
Several of have explained why this is the case but those posts appear to have been swamped by noise.
Do you know for sure what @TS contains when it's supposed to be holding multiple values?

^{“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 Hall of Fame Points: 3426 More actions · Answer 4

It seems to fail if:
A) I use the TS.Skill In as part of an OR or Case statement in the Where and
B) The user selects more than one skill.

All I can get working is, selecting everything or a single skill.

Is there no way to choose in a where statement between this
And TS.Skill In (@TS)
And selecting everything.

Thom A SSC Guru Points: 99130 More actions · Answer 5

Ken at work - Wednesday, February 8, 2017 7:00 AM
It seems to fail if:
A) I use the TS.Skill In as part of an OR or Case statement in the Where and
B) The user selects more than one skill.
All I can get working is, selecting everything or a single skill.
Is there no way to choose in a where statement between this
And TS.Skill In (@TS)
And selecting everything.

I think at this stage we need to know what it is you're running when you run a query with multiple values in the paramter.

Can you post your T-SQL that you or your application runs, but instead replace @TS variable with your multi-value input. I.e. for a single value it would be:
And TS.Skill In ('Badminton')
You may need to do a trace to achieve this,

Thom~

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

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 6

Ken at work - Wednesday, February 8, 2017 7:00 AM
It seems to fail if:
A) I use the TS.Skill In as part of an OR or Case statement in the Where and
B) The user selects more than one skill.
All I can get working is, selecting everything or a single skill.
Is there no way to choose in a where statement between this
And TS.Skill In (@TS)
And selecting everything.

AND TS.SKILL IN (@TS) won't work if the variable contains multiple choices. It will only work if the variable contains a single choice.

^{“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 SSC Guru Points: 99130 More actions · Answer 7

ChrisM@Work - Wednesday, February 8, 2017 7:13 AM
AND TS.SKILL IN (@TS) won't work if the variable contains multiple choices. It will only work if the variable contains a single choice.

Agreed, hence why I'm unsure why the OP hasn't made use of the Delimited8KSplit function that has been linked to in this topic several times. I think if the OP sees the comma delimited string being passed in his SQL then we might finally get there 🙂

Thom~

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

Jay McGrath SSC-Addicted Points: 456 More actions · Answer 8

Thom A - Wednesday, February 8, 2017 7:19 AM
ChrisM@Work - Wednesday, February 8, 2017 7:13 AM
AND TS.SKILL IN (@TS) won't work if the variable contains multiple choices. It will only work if the variable contains a single choice.
Agreed, hence why I'm unsure why the OP hasn't made use of the Delimited8KSplit function that has been linked to in this topic several times. I think if the OP sees the comma delimited string being passed in his SQL then we might finally get there 🙂

Where are you running your test? I've found the IN clause will only return a single choice in Management Studio, but returns all results when embedded in an RDL.

Works in RDL, but only returns a single choice in SSMS
AND TS.SKILL IN (@TS)

Works in SSMS, causes an error in RDL
AND TS.SKILL IN (SELECT Item FROM dbo.DelimitedSplit8K(@TS, ','))

Thom A SSC Guru Points: 99130 More actions · Answer 9

Jay McGrath - Wednesday, February 8, 2017 7:32 AM
Thom A - Wednesday, February 8, 2017 7:19 AM
ChrisM@Work - Wednesday, February 8, 2017 7:13 AM
AND TS.SKILL IN (@TS) won't work if the variable contains multiple choices. It will only work if the variable contains a single choice.
Agreed, hence why I'm unsure why the OP hasn't made use of the Delimited8KSplit function that has been linked to in this topic several times. I think if the OP sees the comma delimited string being passed in his SQL then we might finally get there 🙂
Where are you running your test? I've found the IN clause will only return a single choice in Management Studio, but returns all results when embedded in an RDL.
Works in RDL, but only returns a single choice in SSMS
AND TS.SKILL IN (@TS)
Works in SSMS, causes an error in RDL
AND TS.SKILL IN (SELECT Item FROM dbo.DelimitedSplit8K(@TS, ','))

What's the error? Is your SSRS Data connection pointing to the database that has the Delimited8KSplit function in it? If you're getting an "error" please tell us what the error is. We're not able to see your screen.

Thom~

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

Jay McGrath SSC-Addicted Points: 456 More actions · Answer 10

Thom A - Wednesday, February 8, 2017 7:35 AM
Jay McGrath - Wednesday, February 8, 2017 7:32 AM
Thom A - Wednesday, February 8, 2017 7:19 AM
ChrisM@Work - Wednesday, February 8, 2017 7:13 AM
AND TS.SKILL IN (@TS) won't work if the variable contains multiple choices. It will only work if the variable contains a single choice.
Agreed, hence why I'm unsure why the OP hasn't made use of the Delimited8KSplit function that has been linked to in this topic several times. I think if the OP sees the comma delimited string being passed in his SQL then we might finally get there 🙂
Where are you running your test? I've found the IN clause will only return a single choice in Management Studio, but returns all results when embedded in an RDL.
Works in RDL, but only returns a single choice in SSMS
AND TS.SKILL IN (@TS)
Works in SSMS, causes an error in RDL
AND TS.SKILL IN (SELECT Item FROM dbo.DelimitedSplit8K(@TS, ','))
What's the error? Is your SSRS Data connection pointing to the database that has the Delimited8KSplit function in it? If you're getting an "error" please tell us what the error is. We're not able to see your screen.

Sorry. I normally avoid using the Split function when embedding SQL directly into a report, so it's not a problem for me. I was just trying to determine how the OP was arriving at his trouble.
I can post screenshots with an example if you like.

Thom A SSC Guru Points: 99130 More actions · Answer 11

Jay McGrath - Wednesday, February 8, 2017 8:06 AM
Sorry. I normally avoid using the Split function when embedding SQL directly into a report, so it's not a problem for me. I was just trying to determine how the OP was arriving at his trouble.
I can post screenshots with an example if you like.

Just a copy paste of the error is fine.

Thom~

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

Jay McGrath SSC-Addicted Points: 456 More actions · Answer 12

Thom A - Wednesday, February 8, 2017 8:10 AM
Jay McGrath - Wednesday, February 8, 2017 8:06 AM
Sorry. I normally avoid using the Split function when embedding SQL directly into a report, so it's not a problem for me. I was just trying to determine how the OP was arriving at his trouble.
I can post screenshots with an example if you like.
Just a copy paste of the error is fine.

Here you go. It doesn't tell me much. 🙁

For more information about this error navigate to the report server on the local server machine, or enable remote errors
----------------------------
Query execution failed for dataset 'Test'. (rsErrorExecutingCommand)
----------------------------
An error has occurred during report processing. (rsProcessingAborted)

Here's the screenshot of the test report I used. It works when I enter a single number, but fails with the error above when I enter more than one.

">

Thom A SSC Guru Points: 99130 More actions · Answer 13

Really need to see the error behind it (you have remote error disabled so I have no idea what the error is). Unfortunately without a true error I'm going to be shooting in the dark.

Couple of guesses
1. Your Split Function isn't on your Sims database
2. You've passed a value in your @Programs parameter that can't be cast as an INT.

Thom~

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

Jay McGrath SSC-Addicted Points: 456 More actions · Answer 14

(you have remote error disabled so I have no idea what the error is).

Sorry, but that's out of my control as far as I know.

In any event, I normally just work around this by avoiding the Split function when I use direct SQL in a report. Thanks for your help!

Ken at work Hall of Fame Points: 3426 More actions · Answer 15

For me, I am not using the Split Function.

Normally I only use the Where 'Something' IN (@Variable). Everything is good. The using can 'Select All' or any number of the options from the drop down parameter. This time I wanted, rather then 'Select All' to actually pass, using the variable, "ALL". I was hoping the SSRS drop down still functioned as before and if I clicked on "All" I could get it to select all. But I can't get that to work.