I have a sql database query that uses this parameter like such:
"select * from Countries where CountryID is null or CountryID in (@CountryID)
Running the report, everything works as expected when I set the parameter value to NULL or to a single value. However, if I set it equal to two or more values I get the following error:
An error has occurred while processing Table 'crosstab1': An expression of non-boolean type specified in a context where a condition is expected, near ','.
What am I missing? How do I configure the parameter and/or adjust the query so that it works with NULL, single values and multiple values?
15 Answers, 1 is accepted
Judging by the error, the problem is with expression used in filter/grouping/sorting of a CrossTab and not with the report parameters directly. Our suggestion is to revise these expressions and verify if they are correct in the context of a report parameter containing multiple values and allowance of null (i.e. respective IsNull checks have to be in place, see Functions).
All the best,
the Telerik team
"select * from Countries where @CountryID is null or CountryID in (@CountryID)"
The problem does not appear to be with null checking. My results are:
1. Click the NULL checkbox (thus setting the value to null) ...the above works
2. Select ONE value ... the above sql query works
3. Select TWO OR MORE values ... the sql query gives me the exception I specified in my original post.
If the problem is with the query or some kind of null checking I need to do ... why is it that the first two cases succeed and only the last fails?
Thanks - Wayde
Checking for null was only one of the suggestions. Most likely you have an expression in the CrossTab that uses the CountryID report parameter and this expression is no longer valid when an array of values is used as condition.
the Telerik team
I also think the query is fine.
The problem I speculate has something to do with have a multi-value parameter with AllowNull = True. I'm trying now to fix what is fed into the SQL statement by editing the value used depending on whether or not there are multiple values selected or not
Right now I have this ...
Name DbType Value
@CountryID Int32 =iif(Parameters.Indexes.Value.Length > 0,Parameters.Indexes.Value, null)
This is not working. Here is what happens:
- If I check the Allow Null checkbox it works ... @CountryID is set to null as expected
- If I select ONE value it works ... @Country is set to Parameters.Indexes.Value as expected
- If I select TWO OR MORE VALUES ... it fails. Why? I don't know
I change my SQL to look like this:
"select * from Countries where CountryID IN (isnull(@CountryID,CountryID))"
The error I get when I pass in MULTIPLE VALUES is ... "The isnull function requires 2 argument(s)"
I'm not sure what is happening here ... but it looks like when multiple values are specified the @CountryID isn't being set to something like "1,2,3" as I would expect.
Any ideas? I've been dealing with this forever.
I've prepared a sample project that works correctly. Please find it attached and should you continue to have problems, provide us with a sample that shows the problem.
the Telerik team
- ... WHERE @CountryID IS NULL OR CountryID IN (@CountryID)
- ... WHERE @CountryID1, @CountryID2 IS NULL OR CountryID IN (@CountryID1, @CountryID2)
To enable your multi-select parameter to be "ignored" by the query if it's NULL, the only way I've found is to use the T-SQL COALESCE() function in your query, like so:
- ... WHERE COALESCE( @CountryID, NULL) IS NULL OR CountryID IN (@CountryID)
Now the @CountryID can be dynamically replaced with multiple values and it evaluates as you intend...and without fear of a syntax error.
Telerik should include this as a best practice when users want to enable NULL as an option to have a parameter "ignored" in a query's WHERE clause.
Thank you for your input.
First let me point that the query is not well structured, if you expect a collection from the parameter (CountryID in (@CountryID)) the checking for null should handle collection as well - Concat, Coalesce.
So indeed this is one possible solution on query level.
We can suggest you to move this filtering on report level, where the expression will look as follows:
The expression is created with the help of the the built-in reporting functions.
Let us know if you have any further concerns.
the Telerik team
Telerik Reporting Q1 2013 available for download with impressive new visualizations. Download today from your account.
1st time in 10 years of SQL Server Ive found a use for COALESE! :)
This can be tested in t-sql:
select 'A' where 1,2 is null -- this will generate the error mentioned, so telerik is replacing the param value with comma separated values
select 'A' where COALESCE( 1,2, NULL) IS NULL -- this works and will evaluate to false so it will pick the values in the 'in' clause
Hi Cory from 2018:)
Your solution still actual, thank you very much, really appriciate your help!