Posted by bbla32 on 02/19/07 21:21

I'll show you my query which doesn't want to compile for some reason
(Incorrect syntax near the keyword GROUP):

DECLARE @pp INT
SET @pp = 7


SELECT SUM(Sent) AS Sent, SUM(Received) AS Received, [ID]

FROM (

SELECT 1 AS Sent, 0 AS Received,
DM.* FROM WSDMS..DocumentMessages DM WHERE DM.ObsoleteDate IS NULL
AND DM.SenderID = @pp

UNION

SELECT 0 AS Sent, 1 AS Received,
DM.* FROM WSDMS..DocumentMessages DM WHERE DM.ObsoleteDate IS NULL
AND DM.ReceiverPersonID = @pp

UNION

SELECT 0 AS Sent, 1 AS Received,
DM.* FROM WSDMS..DocumentMessages DM WHERE DM.ObsoleteDate IS NULL
AND DM.ReceiverDepartmentID IN
(SELECT PD.DepartmentID FROM CF..PersonnelDepartment PD
WHERE PD.PersonID = @pp AND PD.ObsoleteDate IS NULL)
AND DM.StationID IN
(SELECT PS.StationID FROM CF..PersonnelStationsResponsibility PS
WHERE PS.PersonID = @pp AND PS.ObsoleteDate IS NULL)

UNION

SELECT 0 AS Sent, 1 AS Received,
DM.* FROM WSDMS..DocumentMessages DM WHERE DM.ObsoleteDate IS NULL
AND DM.ReceiverDepartmentID IN
(SELECT PD.DepartmentID FROM CF..PersonnelDepartment PD
WHERE PD.PersonID = @pp AND PD.ObsoleteDate IS NULL)
AND DM.InstructionNr IN
(SELECT I.InstructionNr FROM WSDMS..InstructionsNewest I
WHERE I.PUID = @pp AND I.PUID IN
(SELECT PU.ID FROM WSDMS..PUs PU WHERE PU.PersonID = @pp)
)

)

GROUP BY [ID]

[Back to original message]