avivap
asked on
SQL server conditional case statement in where clause using IN clause
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!
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!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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.
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
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
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.
ASKER
Thank you both for your help!
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?