Multi select parameter does not work in WHERE clause
-
Hello,
I´m using a SyBase SQL Anywhere 17 Database.
Then I want to create a sql server report services (SSRS) report in MS Visual Studio 2017.
First I create a parameter named "param_matcode" which is from typ Text an allows multiple values. See picture "param_matcode.PNG".
In the Box of "Materialcode" a more than 1 value for selction. See picture "select_matcode.png".
My parameter list in the main dataset "dsetLeonieFdT" is a following: See picture "param_list.PNG".
And the parameter expression of param_matcode is like this: See "param_expression.PNG".
An last here ist the query:
SELECT
mat.MatCode, mat.MatName,
round(sum(matverb.VerbrGewAuto), 2) as VerbrGewAutoValue,
round(sum(matverb.VerbrGewHand), 2) as VerbrGewHandValue,
replace(STR(sum(matverb.VerbrGewAuto), 15, 2), '.', ',') as VerbrGewAutoText,
replace(STR(sum(matverb.VerbrGewHand), 15, 2), '.', ',') as VerbrGewHandText
FROM mat, matverb
WHERE mat.MatCode = matverb.MatCode
and
matVerb.VerbrDatumZeit between ? and ?
and
matVerb.ProdLinCode = ?
and
matVerb.SchichtCode = ?
and
matVerb.MatCode like ?
GROUP BY mat.MatCode, mat.MatNameNow the problem is that conditions "matVerb.VerbrDatumZeit between ? and ?", "matVerb.ProdLinCode = ?" and "matVerb.SchichtCode = ?" works well.
But the condition "matVerb.MatCode like ?" is not working.
It seems that a multi select value parameter is not possible to work with this.
Have anyone an idea how to solve this problem.
How shall the condition "matVerb.SchichtCode = ?" be?
How shall the parameter expression be?
Is it possible to make a condition with the operator "like" or " "in"?
I am asking you for help.
Thanks a lot.
CopWorker
-
It's been a while since I have done this, so I can offer only limited help.
I would say that LIKE is definitely not going to work in this case. You should have more success with IN ...
WHERE matVerb.MatCode IN (?)
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
SSRS doesn't use
?for parameters, it's uses the parameters name. Something likeSELECT * FROM MyTable WHERE ID = ?;isn't correct, it would beID = @ID.When using multiple value parameters and a dataset with a SQL statement (rather than an SP), SSRS sees
INcaluses and treats them differently. As much as I hate it, SSRS injects into the query the correct syntax for anIN.For example, you have the query below:
SELECT *
FROM MyTable MT
WHERE MT.MyDate >= @StartDate
AND MT.MyDate < @EndDate
AND MT.MyCode IN (@Code);In your report,
@Codeis defined as a multi value parameter. Let's say you have selected the values1,2,4and5. SSRS would turn the query above into something like this:DECLARE @StartDate = '20190101';
DECLARE @EndDate = '20200101';
SELECT *
FROM MyTable MT
WHERE MT.MyDate >= @StartDate
AND MT.MyDate < @EndDate
AND MT.MyCode IN ('1','2','4','5');You don't need to try and string aggregate your
INvalues if you're using an inline statement in SSRS. SSRS handles it "gracefully".If you're using an SP, then SSRS will automatically string aggregate the values. Let's say, for example, you turn the above statement into an SP called
GetMyTableData. For@codeSSRS would pass a delimited list instead. So the statement will look something like:EXEC PROC GetMyTableData @StartDate = '20190101', @EndDate = '20200101', @Code = '1,2,4,5';
As it's now a delimited list, you'll need to handle that in your SP correctly. Something like this would work:
CREATE PROC GetMyTableData @StartDate date, @EndDate date, @Code varchar(8000) AS
BEGIN
SELECT *
FROM MyTable MT
JOIN dbo.DelimitedSplit8K_LEAD(@Code,',') DS ON MT.MyCode = DS.Item
WHERE MT.MyDate >= @StartDate
AND MT.MyDate < @EndDate;
END;Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk -
Hello Phil,
by using "WHERE matVerb.MatCode IN (?)" there are the same problems.
Nevertheless thanks al lot.
CopWorker
-
Hello Tom,
you said: SSRS doesn't use ? for parameters, it's uses the parameters name. Something like SELECT * FROM MyTable WHERE ID = ?; isn't correct, it would be ID = @ID.
Using "?" is already correct.
It is a matter of a SyBase SQL Anywhere 17 Database not SQL Database.
SQL Database queries a most easier than this.
The links to the parameter works with the key word "@" additional the parameter name.
In case of SyBase Database each link to a parameter gets the same key word "?".
In parameter list the parameter are listed in the same order.
Nevertheless thanks al lot.
CopWorker
-
Are there no SYBASE forums? 😉
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills -
Hello The Dixie Flatline,
SyBase forms already exists.
It seams the combination of SyBase and SSRS is extremely rare. I dont´t found till this time anyone who do this.
Most easier is the combination of SQL an SSRS with develop environment visual studio from microsoft.
But we use SyBase just now.
If you find somewone you works with SSRS and SyBase please tell me this.
Thanks a lot
Best regards from CopWorker
-
Gramatically better: If you find somewone who works with SSRS and SyBase please tell me this.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply