0

I have a dataset that has two fields Main_Activity and Requesting_Activity

Sample dataset"

Main_activity    Reuesting_Activity
-------------    ------------------
Act1             Act1,Act1a,Act2,Act3
Act2             Act1a,Act3,Act5
Act3             Act1,Act3,Act4
Act4             Act2,Act4,Act5

When I use the SQL query and pass Act1 as parameter (@mainActivity) for the dataset 

SELECT DISTINCT
    Main_Activity,
    Requesting_Activity
FROM Activity_table
WHERE Requesting_Activity LIKE ''+ (@mainActivity) +'' OR Requesting_Activity LIKE ''+ (@mainActivity) +',%' OR Requesting_Activity LIKE '%,'+ (@mainActivity) +',%' OR Requesting_Activity LIKE '%,'+ (@mainActivity) +''

I get the expected output 

Act1
Act3

The problem is when I make the parameter as multi-select, I am getting error. How do I resolve it? How to use "LIKE and "IN" operator in same query?

damseldeebi
  • 137
  • 1
  • 16
  • 1
    have a look here: http://stackoverflow.com/questions/1865353/combining-like-and-in-for-sql-server its already a duplicate – deterministicFail Mar 06 '15 at 07:27
  • I already had a look at that post, but there the values to be matched are static. In my question it is dynamic. – damseldeebi Mar 06 '15 at 07:30
  • 1
    @damseldeebi your problem is not in SSRS, but in your table design. Multivalue fields are wrong! You are trying to solve a problem in SSRS, when you just need to redesign your table(s). – mordack550 Mar 06 '15 at 07:40
  • Please, provide error, which you get. Please describe the query logic. If you need a Requesting_Activity, which contain @mainActivity, why don't you use simple variant: Requesting_Activity LIKE '%'+ (@mainActivity) +'%' ? – Andrey Davydenko Mar 06 '15 at 07:59
  • @mordack550 My table contains thousands of records and hundreds of fields, if I normalize the database to avoid multi-value fields then I end up in having few thousand more records. So I can't change the table design – damseldeebi Mar 06 '15 at 08:42
  • @AndreyDavydenko as you can see, I used that and got the output. But it takes only "single parameter".. from my SSRS report, I want to have the parameter to allow multiple values.. – damseldeebi Mar 06 '15 at 08:44
  • 1
    @damseldeebi I'm sorry, but you are doing it completely wrong. Why do you even care about number of rows, when you are spending A LOT of processing power on LIKEs on multi value fields?? Are you short of hard drive space? Few thousand more records? That's nothing for almost any DMBS, even less on engines like SQL Server that can handle datawarehouses with tables with 30+millions records. – mordack550 Mar 06 '15 at 09:04
  • 1
    @damseldeebi you are facing the X-Y problem. You are trying to solve "How to retrieve the rows I want" when the only thing your should care about is "How to correctly design a relational database". Multivalues field in a relational database are **wrong**. Always, no exception. – mordack550 Mar 06 '15 at 09:08
  • @mordack550 yeah I get your point... but that is how the database is designed and I dont have the permissions to change it – damseldeebi Mar 06 '15 at 09:39
  • What is the real problem you are trying to solve? Why did the database end up like that? You don't have any multivalued fields, you only have a text field that you are trying to use as if it were multivalued. This isn't denormalization, you broke even the 1st NF. Essentially, you trapped yourself in a corner. The only efficient way left is to use full text search, ie the FREETEXT and CONTAINS functions, not LIKE – Panagiotis Kanavos Mar 06 '15 at 09:54
  • A much less performant alternative do what the duplicate proposes, only put the values (with `%` prefix and suffix)in a TVP, then do a join of the two tables with `ON Requesting_Activity LIKE @values.Activity`. Performance will still be bad (no indexes after all) but at least you won't have to type that much – Panagiotis Kanavos Mar 06 '15 at 09:59

1 Answers1

1

your where clause is full of redundancies

WHERE Requesting_Activity LIKE '%'+ (@mainActivity) +'%'
A ツ
  • 1,267
  • 2
  • 9
  • 14