January 19, 2009 at 5:18 am
Hi All,
I have a dropdown list which is a multi selection field, when multiple value is selected it is stored using ','.
My query is fetching the required data for single item selected and fails for fetching the data, when multiple items are selected.
Total company with comp_incode is '838', my query is fetching '811' , 27 are the one which have multiple 'comp_indcode'.
My Query:
select capt_code,rtrim(ltrim(replace(comp_indcode,',',''))) as Indcode,capt_us
from custom_captions,company
where capt_family = 'comp_indcode'
and comp_deleted is null
and comp_indcode is not null
and capt_code =rtrim(ltrim(replace(comp_indcode,',','')))
here when it is replacing ', ' and trim function is used then it is joining multiple codes and when it cannot find that with capt_code then returns null.
I just want to elliminate ',' from starting and end, so that it can understand the code and fetch the data or is there any way by which i can get the desired data.
Ex: Desired Result
capt_code comp_indcode Capt_us
211 ,211, Sample1
3214 ,3214, Sample2
,211,3214, Sample1,Sample2
Result from my query
capt_code comp_indcode Capt_us
211 211 Sample1
3214 3214 Sample2
2113214 Null
Help greatly appreciated.
January 19, 2009 at 5:38 am
You will need to post a Source Table Definition and some test data in order to understand the problem
January 19, 2009 at 6:15 am
When comp_indcode contains more than one value, it's a comma-delimited list and this won't work: capt_code = comp_indcode . This isn't because there are pesky commas everywhere, it's because you are no longer trying to compare one value with one other value, like x=y, you're trying to compare several: x = y, or x = z.
There are two common methods of getting around this.
Firstly, you could use a function or some other code to resolve the comma-delimited list into a table structure, either a #temp table or a @table variable.
Secondly, you could build a query using dynamic SQL where the comparison between capt_code and comp_indcode is made using a list operator
WHERE capt_code IN (211, 324).
The first method almost always gets my vote. It's more readable, meaning easier to work with, and it's usually faster. The second method is associated with the risk of SQL injection.
Cheers
ChrisM
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
January 19, 2009 at 9:18 pm
Right on Chris. I'll give my vote for the first option as well. We have a SP here that relies heavily on using a table valued function (along with a tally table) to split the comma delimited list. Works like a charm and performs well too.
January 19, 2009 at 11:20 pm
From BOL:
Writing Queries for Multivalue Report Parameters
You can define a multivalue parameter for any report parameter that you create. However, if you want to pass multiple parameter values back to a data source by using the query, the following requirements must be satisfied:
The data source must be SQL Server, Oracle, Analysis Services, SAP BI NetWeaver, or Hyperion Essbase.
The data source cannot be a stored procedure. Reporting Services does not support passing a multivalue parameter array to a stored procedure.
The query must use an IN clause to specify the parameter.
The following example illustrates the use of an IN keyword in the WHERE clause of a Transact-SQL statement. For more information about the IN keyword or the results returned by this query, see IN (Transact-SQL).
Copy Code
SELECT FirstName, LastName, e.Title
FROM HumanResources.Employee AS e
JOIN Person.Contact AS c
ON e.ContactID = c.ContactID
WHERE e.Title IN (@Title)
To experiment with this in a report, define a dataset using this query. Change the properties for the automatically created report parameter Title in the following way:
Select the Multi-value option.
In Available values, select the Non-queried option. Enter the following list in the Value column (leave the Label column blank): Design Engineer, Buyer, Marketing Assistant.
In Default values, enter Buyer.
Run preview. Select different combinations of values for Title and verify that you get the expected results.
Note:
The report server rewrites queries for data sources that cannot process parameters as an array. Rewriting the query is necessary to produce the intended result. A query rewrite is triggered when a parameter is defined as multivalued and the query uses an IN statement to specify the parameter. If you build a query that does not include the IN statement, realize that you are circumventing the logic the report server provides to support multivalued parameters.
Filter expressions for datasets, data regions, and groups are defined on the Filter page of the corresponding Properties dialog box. If you have defined a filter expression that refers to a multivalue parameter, you must use the IN operator in the filter expression. Filter expressions that use operators other than IN result in processing errors. For more information, see How to: Add a Filter (Reporting Services).
There is more information in BOL on using Multi-value parameters. If you are using a Stored procedure then you need to do as Chris and John have suggested.
BTW-I recommend using a stored procedure and implementing the logic as suggested by Chris and John. I just wanted to let you know that it is possible to just use the IN clause.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply