CODEWITHSUNDEEP
sqlsql-server
dmr
dmr

Reputation: 22383

Help debugging sql query: where do I have an extra ( or )?

I am getting 2 errors:
1) Incorrect syntax near keyword union
2) Incorrect syntax near ')'

Here is the query:

select count(*) as numUsers, count(r.cabGroupId) as numCompanies from
            (select r.newUserId, r.groupId
            from reportaccesslogs r full outer myTable d
            on r.num = d.num
            where (r.reportId = 1) and (r.groupId not in (666, 999))
            and ((r.num1 = @num1)) or (d.num1 = @num1))

            union

            select userId, groupId 
            from detailsLog
            where (num1 = coalesce(@num1, num1)) and (num2= @num2) and (num3= coalesce(@num3, num3)) 
            and (groupId not in (666, 999))) tmp

Upvotes: 0

Views: 34

Answers (2)

Sachin Shanbhag
Sachin Shanbhag

Reputation: 55499

Sure you have an extra ')' in last line beside tmp

Upvotes: 0

JNK
JNK

Reputation: 65187

select count(*) as numUsers, count(r.cabGroupId) as numCompanies 
from
            (select r.newUserId, r.groupId
            from reportaccesslogs r full outer myTable d
            on r.num = d.num
            where (r.reportId = 1) and (r.groupId not in (666, 999)
            and ((r.num1 = @num1) or (d.num1 = @num1)))

            union

            select userId, groupId 
            from detailsLog
            where (num1 = coalesce(@num1, num1)
            and (groupId not in (666, 999)))) tmp

See if that works.

Upvotes: 1

Related Questions