Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Syntax error when using IN statement inside inner query


Syntax error when using IN statement inside inner query

Author
Message
alex 64682
alex 64682
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 6
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
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47165 Visits: 44346
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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


alex 64682
alex 64682
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 6
Wow that was quick, and also solved the problem.

Much appreciated!!!!!!
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search