Why I am having error and how to solve this query

Question

Post reply

Why I am having error and how to solve this query

Newbi

SSCertifiable

Points: 6546
More actions
January 13, 2017 at 7:48 am

#104296

I am trying to join two main data tables, I have my outer alias as well as ON relationship but I am getting >>incorrect syntax near B<<<SELECT CID, CONVERT(datetime, CreatedOn, 103) AS CreatedOn, Points, Quantity, CASE WHEN quantity < 0 THEN 'N' ELSE 'P' END AS PointsGrpFROM dbo.Loyalty_Points_Trans ALeft outer join (SELECT top 100 C.AccountNo AS Acc, MONTH(T.CreatedOn) AS Mth, YEAR(T.CreatedOn) AS Yr, SUM(CONVERT(INT, T.Points) * CONVERT(INT, T.Quantity)) AS TotalPtFROM Customers AS C LEFT OUTER JOIN Addresses AS A ON C.RefID = A.ParentID LEFT OUTER JOIN (SELECT CID, CONVERT(datetime, CreatedOn, 103) AS CreatedOn, Points, Quantity FROM Loyalty_Points_Trans) AS T ON C.RefID = T.CID WHERE (C.Relationship = 8) AND (C.Grade IN ('Normal')) AND (YEAR(T.CreatedOn) = 2016) AND T.CID=C.RefIDGROUP BY C.AccountNo, MONTH(T.CreatedOn), YEAR(T.CreatedOn) B On A.CID=B.RefID

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

You're missing a bracket after the GROUP BY in your subquery

GROUP BY C.AccountNo, MONTH(T.CreatedOn), YEAR(T.CreatedOn) /*needs a close bracket here*/ B On A.CID=B.RefID

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

Newbi SSCertifiable Points: 6546 More actions · Answer 2

GilaMonster - Friday, January 13, 2017 7:51 AM
You're missing a bracket after the GROUP BY in your subquery
GROUP BY C.AccountNo, MONTH(T.CreatedOn), YEAR(T.CreatedOn) B /*needs a close bracket here*/ On A.CID=B.RefID

I am still having error.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

Newbi - Friday, January 13, 2017 8:06 AM
GilaMonster - Friday, January 13, 2017 7:51 AM
You're missing a bracket after the GROUP BY in your subquery
GROUP BY C.AccountNo, MONTH(T.CreatedOn), YEAR(T.CreatedOn) B /*needs a close bracket here*/ On A.CID=B.RefID
I am still having error.

Does this solve the problem?

SELECT CID, CONVERT(datetime, CreatedOn, 103) AS CreatedOn, Points, Quantity, CASE WHEN quantity < 0 THEN 'N' ELSE 'P' END AS PointsGrpFROM dbo.Loyalty_Points_Trans A Left outer join (SELECT top 100 C.AccountNo AS Acc, MONTH(T.CreatedOn) AS Mth, YEAR(T.CreatedOn) AS Yr, SUM(CONVERT(INT, T.Points) * CONVERT(INT, T.Quantity)) AS TotalPt FROM Customers AS C LEFT OUTER JOIN Addresses AS A ON C.RefID = A.ParentID LEFT OUTER JOIN (SELECT CID, CONVERT(datetime, CreatedOn, 103) AS CreatedOn, Points, Quantity FROM Loyalty_Points_Trans) AS T ON C.RefID = T.CID WHERE (C.Relationship = 8) AND (C.Grade IN ('Normal')) AND (YEAR(T.CreatedOn) = 2016) AND T.CID = C.RefID GROUP BY C.AccountNo, MONTH(T.CreatedOn), YEAR(T.CreatedOn)) B On A.CID = B.RefID;

Another way to write this for SQL Server 2005 or newer:

WITH ACte as (SELECT top 100 C.AccountNo AS Acc, MONTH(T.CreatedOn) AS Mth, YEAR(T.CreatedOn) AS Yr, SUM(CONVERT(INT, T.Points) * CONVERT(INT, T.Quantity)) AS TotalPtFROM Customers AS C LEFT OUTER JOIN Addresses AS A ON C.RefID = A.ParentID LEFT OUTER JOIN (SELECT CID, CONVERT(datetime, CreatedOn, 103) AS CreatedOn, Points, Quantity FROM Loyalty_Points_Trans) AS T ON C.RefID = T.CIDWHERE (C.Relationship = 8) AND (C.Grade IN ('Normal')) AND (YEAR(T.CreatedOn) = 2016) AND T.CID = C.RefIDGROUP BY C.AccountNo, MONTH(T.CreatedOn), YEAR(T.CreatedOn))SELECT CID, CONVERT(datetime, CreatedOn, 103) AS CreatedOn, Points, Quantity, CASE WHEN quantity < 0 THEN 'N' ELSE 'P' END AS PointsGrpFROM dbo.Loyalty_Points_Trans A Left outer join ACte B On A.CID = B.RefID;