Getting this error with the following query in SQL Server 2012.
An expression of non-boolean type specified in a context where a condition is expected, near 'RETURN'.
CREATE FUNCTION [dbo].[GetPMResources](@UserResourceNo nvarchar(250))
RETURNS @Resources TABLE (
ResourceNo nvarchar(250) COLLATE Latin1_General_CS_AS not null,
Name nvarchar(250) COLLATE Latin1_General_CS_AS not null
)
AS
BEGIN
Declare @RoleID int, @UserDivision nvarchar(20)
SELECT TOP(1) @RoleID = r.ReportingRole, @UserDivision = r.DivisionCode
FROM Resource r
WHERE r.ResourceNo = @UserResourceNo
INSERT @Resources
SELECT r.ResourceNo,Name = r.ResourceNo + ' ' + r.Name
FROM Resource r WHERE r.ResourceNo IN
(
SELECT m.ResourceNo FROM JobMember m
JOIN Job j ON j.JobNo = m.JobNo
WHERE j.ProjectManagerNo = @UserResourceNo
OR
j.AlternateProjectManagerNo = @UserResourceNo
)
OR
(
SELECT m.ResourceNo FROM JobMember m
JOIN JobTask t ON t.JobTaskNo = m.JobTaskNo
WHERE t.TaskManagerNo = @UserResourceNo
OR
t.AlternateTaskManagerNo = @UserResourceNo
)
RETURN
END
This question is related to
sql
sql-server
sql-server-2012
That is invalid syntax. You are mixing relational expressions with scalar operators (OR
). Specifically you cannot combine expr IN (select ...) OR (select ...)
. You probably want expr IN (select ...) OR expr IN (select ...)
. Using union would also work: expr IN (select... UNION select...)
YOu can also rewrite it like this
FROM Resource r WHERE r.ResourceNo IN
(
SELECT m.ResourceNo FROM JobMember m
JOIN Job j ON j.JobNo = m.JobNo
WHERE j.ProjectManagerNo = @UserResourceNo
OR
j.AlternateProjectManagerNo = @UserResourceNo
Union All
SELECT m.ResourceNo FROM JobMember m
JOIN JobTask t ON t.JobTaskNo = m.JobTaskNo
WHERE t.TaskManagerNo = @UserResourceNo
OR
t.AlternateTaskManagerNo = @UserResourceNo
)
Also a return table is expected in your RETURN statement
Your problem might be here:
OR
(
SELECT m.ResourceNo FROM JobMember m
JOIN JobTask t ON t.JobTaskNo = m.JobTaskNo
WHERE t.TaskManagerNo = @UserResourceNo
OR
t.AlternateTaskManagerNo = @UserResourceNo
)
try changing to
OR r.ResourceNo IN
(
SELECT m.ResourceNo FROM JobMember m
JOIN JobTask t ON t.JobTaskNo = m.JobTaskNo
WHERE t.TaskManagerNo = @UserResourceNo
OR
t.AlternateTaskManagerNo = @UserResourceNo
)
An expression of non-boolean type specified in a context where a condition is expected
I also got this error when I forgot to add ON condition when specifying my join clause.
Source: Stackoverflow.com