crony crony - 1 month ago 24
SQL Question

An expression of non boolean type specified in a context where a condition is expected near ','

When I run the below query on SQL Server it is working fine with single value or multiple values. But When I try to execute the same on SSRS Dataset it is throwing an error for multiple values.


ERROR:
SSRS: An expression of non boolean type specified in a context where a condition is
expected near ','. Incorrect syntax near the keyword 'ELSE'.


It is working fine with single value. Not sure what is the issue or is there any other way that we can write this query.

DECLARE @UserTeam VARCHAR(max) = 'Team1,Team2,Team3,Team4,Team5,Team6,'''

-- Values can inculdes null. if user select blank and Team1,Team2 we need to pull data which is null, Team1, Team2
IF Object_id('tempdb.dbo.#Team', 'U') IS NOT NULL
DROP TABLE #team;

SELECT item AS Team
INTO #team
FROM dbo.FNSPLIT(@UserTeam, ',')

IF @UserTeam = ''
BEGIN
SELECT U.username,
T.teamcode
FROM [User].vwuser u
LEFT JOIN[User].userteam UT
ON U.userid = UT.userid
LEFT JOIN [User].team AS T
ON T.teamid = Ut.teamid
WHERE T.teamcode IS NULL
ORDER BY U.username
END
ELSE
BEGIN
SELECT U.username,
T.teamcode
FROM [User].vwuser u
LEFT JOIN [User].userteam UT
ON U.userid = UT.userid
LEFT JOIN [User].team AS T
ON T.teamid = Ut.teamid
JOIN #team tm
ON T.teamcode = tm.team
WHERE ( U.username NOT LIKE '%System%' )
ORDER BY U.username
END

Answer Source

I could figured out what is the issue. It's because UserTeam parameter is not accepting multiple selections. To resolve I added below expression.

=JOIN(Parameters!UserTeam.Value,",")

Dataset Properties -> parameters -> @UserTeam -> add expression. Basically it will delimit in a comma separated values and function split dbo.fnSplit(@UserTeam, ',') will convert into a table.

Also I have changed a query in a better way without having IF condition

SELECT U.userName, T.teamCode 
FROM [User].vwUser u
   left join [User].UserTeam UT  ON U.userId = UT.userId 
   left join [User].Team AS T ON T.teamId = Ut.teamId 
WHERE        
   U.userName NOT LIKE '%System%'
   AND ISNULL(T.teamCode,'')  IN (SELECT ISNULL(item,'') FROM dbo.fnSplit(@UserTeam,','))
ORDER BY U.userName