• Register
0 votes
93 views

Problem :

I am facing following error with the below query in SQL Server 2012.

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

 

SQL Query:

CREATE FUNCTION [dbo].[GetMYPMResources](@UserResourceNo nvarchar(250))
RETURNS @MyResources TABLE (
MyResourceNo nvarchar(250) COLLATE Latin1_General_CS_AS not null,
MyName 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.MyResourceNo = @UserResourceNo
INSERT @MyResources
SELECT r.MyResourceNo,MyName = r.MyResourceNo + ' ' + r.MyName
FROM Resource r WHERE r.MyResourceNo IN
( 
SELECT m.MyResourceNo FROM JobMember m
JOIN Job j ON j.JobNo = m.JobNo
WHERE j.ProjectManagerNo = @UserResourceNo 
OR
j.AlternateProjectManagerNo = @UserResourceNo
 ) 
 OR
 (
 SELECT m.MyResourceNo FROM JobMember m
 JOIN JobTask t ON t.JobTaskNo = m.JobTaskNo
 WHERE t.TaskManagerNo = @UserResourceNo
 OR
 t.AlternateTaskManagerNo = @UserResourceNo
 )                   
        RETURN 
  END
6 5 3
7,540 points

1 Answer

0 votes

Solution :

Your problem might be following query snippet:

OR
(
SELECT m.MyResourceNo FROM JobMember m
JOIN JobTask t ON t.JobTaskNo = m.JobTaskNo
WHERE t.TaskManagerNo = @UserResourceNo
OR
t.AlternateTaskManagerNo = @UserResourceNo
)

try updating it to as below

OR r.MyResourceNo IN
(
SELECT m.MyResourceNo FROM JobMember m
JOIN JobTask t ON t.JobTaskNo = m.JobTaskNo
WHERE t.TaskManagerNo = @UserResourceNo
OR
t.AlternateTaskManagerNo = @UserResourceNo
)

 

9 7 4
38,600 points

Related questions

0 votes
2 answers 177 views
177 views
Problem : I want to access my hosting server’s database through SQL Server Management Studio, everything is fine but when I use the following command use myDatabase it gives me following error: The server principal "****" is not able to access the database "****" under the current security context. Can anyone give me solution on my problem?
asked Nov 21, 2019 peterlaw 6.9k points
0 votes
1 answer 176 views
176 views
Problem : My stored procedure giving me following error cannot perform an aggregate function on an expression containing an aggregate or a subquery.
asked Nov 15, 2019 peterlaw 6.9k points
0 votes
2 answers 171 views
171 views
Problem : I know that my query below causes the error - ORDER BY items must appear in the select list if SELECT DISTINCT is specified. SELECT DISTINCT mycity FROM Employees WHERE mycountry = N'USA' AND myregion = N'WA' ORDER BY mybirthdate; What is a reason for ... gives me some output, but not the error. Is it because SELECT DISTINCT mycity only gives the result set with cities and nothing else ?
asked Jan 17 jwilliam 3.9k points