March 8, 2013 at 2:59 am
Hi,
I'm getting a syntax error on the following query designed to return the newest status from a one to many relationship based on a dynamic list of IDs passed via an IN statement. The inner query runs fine on its own, as does the entire query when the IN statement is removed. The error I keep getting is "Error in list of values in IN clause. Unable to parse query text." Any assistance with this would be greatly appreciated.
SELECT ToBeActionedBy,
COUNT(ToBeActionedBy) AS ActionCount
FROM (SELECT
(SELECT TOP (1) ToBeActionedBy FROM dbo.PStatus AS PStatus_1
WHERE (POID = dbo.POrder.ID) ORDER BY ID DESC) AS ToBeActionedBy
FROM dbo.POrder
INNER JOIN dbo.Address ON dbo.POrder.Company_Name = dbo.Address.Company_Name
WHERE (AccountNumber IN ('PARISH') AND (dbo.Address.ID IN (11743,11899))
AND (Complete = 0)) AS A
WHERE (ToBeActionedBy Is Not NULL) GROUP BY ToBeActionedBy
March 8, 2013 at 3:07 am
Mismatched brackets in the inner WHERE clause. Opened here and not closed (AccountNumber IN ('PARISH')
This is syntactically valid
SELECT ToBeActionedBy ,
COUNT(ToBeActionedBy) AS ActionCount
FROM ( SELECT ( SELECT TOP (1)
ToBeActionedBy
FROM dbo.PStatus AS PStatus_1
WHERE POID = dbo.POrder.ID
ORDER BY ID DESC
) AS ToBeActionedBy
FROM dbo.POrder
INNER JOIN dbo.Address ON dbo.POrder.Company_Name = dbo.Address.Company_Name
WHERE AccountNumber IN ('PARISH')
AND dbo.Address.ID IN (11743, 11899)
AND Complete = 0
) AS A
WHERE ToBeActionedBy IS NOT NULL
GROUP BY ToBeActionedBy
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 8, 2013 at 3:14 am
Wow that was quick, and also solved the problem.
Much appreciated!!!!!!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply