Last post Aug 15, 2012 05:33 PM by rjpalli
Jul 13, 2010 06:47 AM|aroopbhattacharya|LINK
Why do i get error: An expression of non boolean type specified in a context where a condition is expected , near ','.
When previewing this query in SSRS: The error is at WHERE clause to be precise.
SELECT CLIENT_GR_Name, Client_Name, GC_Name, DIV_Name, PolicyType_Name, PolicyTrans_InceptionDt, PolicyTrans_RenewalDt, PolicyTrans_PremAmt,
PolicyTrans_ServiceTax, PolicyTrans_ID, PolicyTrans_PolicyNo, [Engine No.] AS [Engine No.], [Chasis No.] AS [Chasis No.],
[Cubic Capacity] AS [Cubic Capacity], [IDV] AS [IDV], [Inspection done] AS [Inspection done],
[Last year premium paid (Rs.)] AS [Last year premium paid (Rs.)], [No Claim Bonus % expiring policy] AS [No Claim Bonus % expiring policy],
[Registration No.] AS [Registration No.], [Year of Manufacture] AS [Year of Manufacture]
FROM (SELECT CLIENT_GR_Name, Client_Name, GC_Name, DIV_Name, PolicyType_Name, PolicyTrans_InceptionDt, PolicyTrans_RenewalDt,
PolicyTrans_PremAmt, PolicyTrans_ServiceTax, PolicyTrans_ID, PolicyTrans_PolicyNo, Parameters_Name, PolicyTransPara_Value
FROM VW_Trans_Policypara) ps PIVOT (max(PolicyTransPara_Value) FOR Parameters_Name IN ([Registration No.], [Make & Model],
[Cubic Capacity], [Inspection done], [Hypothecation with], [Year of Manufacture], [No Claim Bonus % expiring policy], [IDV],
[Last year premium paid (Rs.)], [Engine No.], [Chasis No.])) AS PVT
WHERE (PolicyTrans_InceptionDt BETWEEN @StartDt AND @EndDt) AND CLIENT_GR_Name IN (CASE WHEN @clientgrname IS NULL
THEN CLIENT_GR_Name ELSE @clientgrname END) AND Client_Name IN (CASE WHEN @Clientname IS NULL
THEN Client_Name ELSE @Clientname END) AND GC_Name IN (CASE WHEN @GCName IS NULL THEN GC_Name ELSE @GCName END) AND
DIV_Name IN (CASE WHEN @divname IS NULL THEN DIV_Name ELSE @divname END)
Jul 18, 2010 03:53 AM|FileFoundException|LINK
What's happening I think (I don't have access to report builder to verify) when you have conditions like
CLIENT_GR_Name IN (CASE WHEN @clientgrname IS NULL
THEN CLIENT_GR_Name ELSE @clientgrname END)
and @clientgrname is a multi-value parameter what happens is that the reporting servers will replace this parameter in the query with a comma separated list of the selected values and so the conditions will look like
CLIENT_GR_Name IN (CASE WHEN 'a','b','c' IS NULL
THEN CLIENT_GR_Name ELSE 'a','b','c' END) ; -- assuming a,b and c are possible values.
and this won't work.
Refactor your where clause. Try coalesce; something like
CLIENT_GR_Name in (@ clientgrname) OR coalesce(@clientgrname) IS NULL
Jul 19, 2010 02:39 AM|aroopbhattacharya|LINK
WHERE CLIENT_GR_Name IN
I tried something like this but it is still not working.....can you reframe my where clause and show me...please!!! Thank you very much!!!
Jul 19, 2010 11:12 AM|FileFoundException|LINK
WHERE CLIENT_GR_Name IN (null) OR COALESCE(null,null,'Zee') IS NULL
this expression will always be false because no client_gr_name is null and coalesce(...) will return Zee that's not null.
declare a dummy variable at the very top of your query. It has be to null.
declare @null varchar;
set @null = null;
now try this
WHERE CLIENT_GR_Name IN (@Your_Parameter_Name) OR COALESCE(@Your_Parameter_Name,@null) IS NULL
there might be a cleaner way of refactoring.
Jul 20, 2010 03:56 AM|You-Hu Fu|LINK
Next time if you encounter the issue of T-SQL, I would recommend you post the case in T-SQL forum at this link
http://social.technet.microsoft.com/Forums/en-US/transactsql/threads There are many experts here, you will receive a quick and professional solution.
Jul 22, 2010 12:51 AM|aroopbhattacharya|LINK
Thanks for you reply.....i agree with you but i can't implement multivalued parameter in SSRS...because i'm forced to select all the parameter values.
Jul 25, 2010 02:04 AM|FileFoundException|LINK
So it's a single-value parameter you're working with? What do you mean you have to select all parameters values?
Aug 03, 2010 05:29 AM|aroopbhattacharya|LINK
can i use COALESE as
WHERE CLIENT_GR_Name IN (select branch from branch) OR COALESCE(select
branch from branch,@null) IS NULL....????
Aug 15, 2012 05:33 PM|rjpalli|LINK
None of the solutions given below worked for me. i am using SQL Server 2008 R2 with SSRS. the only solution that worked for me is, changing the datatype from varchar to nvarchar, seems to be working. the field in where clause was varchar datatype and if
i use this field to compare with multiselect parameter [for example: where (Name IN (@Name)) ... ] the "Name" field should be nvarchar type. then its working for me.