tags:

views:

17

answers:

2

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
+1  A: 
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.

JNK
Note - this will combine your two unioned queries. I am assuming you want to use that as the data set for the counts.
JNK
A: 

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

Sachin Shanbhag