Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Syntax error when using IN statement inside inner query Expand / Collapse
Author
Message
Posted Friday, March 8, 2013 2:59 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, March 8, 2013 2:58 AM
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
Post #1428470
Posted Friday, March 8, 2013 3:07 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 10:05 AM
Points: 42,829, Visits: 35,961
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 2008, MVP
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

Post #1428472
Posted Friday, March 8, 2013 3:14 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, March 8, 2013 2:58 AM
Points: 4, Visits: 6
Wow that was quick, and also solved the problem.

Much appreciated!!!!!!
Post #1428482
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse