Don't Understand SQL Syntax Error

Question

I've gone over this code several times but I cannot figure out why I'm getting

Incorrect Syntax near the keyword 'SR'

Here's my SELECT query:

SELECT OrderDetails.OrderID,OrderDetails.ProductCode,OrderDetails.Vendor_Price,OrderDetails.Quantity
FROM OrderDetails
JOIN
(SELECT OrderDetails.OrderID,  
CASE Orders.SalesRep_CustomerID WHEN  1 THEN 'S'  WHEN 2 THEN 'K' WHEN 3 THEN 'M' ELSE '' END 
FROM Orders
GROUP BY OrderDetails.OrderID)
AS 'SR'
WHERE OrderDetails.ShipDate IS NOT NULL
AND OrderDetails.ShipDate >= DATEADD(Day, Datediff(Day,0, GetDate() -6), 0)
ORDER BY OrderDetails.ProductCode ASC

It's pretty straightforward I just don't see the syntax error the computer is referring to. Thanks.

Unquote the table alias - should be SR, not 'SR'. Add the missing 'ON' clause after it; it's JOIN <table> ON <columnA> = <columnB>, and you're missing the ON and columns to JOIN. — Ken White
– Ken White, Commented Nov 6, 2013 at 20:30

Mojtaba · Accepted Answer · 2013-11-06 20:46:25Z

4

Change 'SR' to SR. There is no need for quotes.

[UPDATE]

You also forget ON statement after AS SR

JOIN (select query...) as SR on SR.OrderID = OrderDetails.OrderID

I guess it should be OrderID if not change it to whatever you need.

[UPDATE 3] REPLACE THE WHOLE QUERY

BTW, I think you wont achieve the result with this query, you need to use the following:

SELECT OrderDetails.OrderID
            ,OrderDetails.ProductCode
            ,OrderDetails.Vendor_Price
            ,OrderDetails.Quantity
            ,CASE Orders.SalesRep_CustomerID WHEN  1 THEN 'S'  WHEN 2 THEN 'K' WHEN 3 THEN 'M' ELSE '' END 
    FROM OrderDetails 
    JOIN #Also consider using INNER JOIN if needed
        Orders on Orders.OrderId = OrderDetails.OrderId
    WHERE 
        OrderDetails.ShipDate IS NOT NULL
        AND OrderDetails.ShipDate >= DATEADD(Day, Datediff(Day,0, GetDate() -6), 0)
    ORDER BY OrderDetails.ProductCode ASC

edited Nov 6, 2013 at 20:46

answered Nov 6, 2013 at 20:26

Mojtaba

6,0644 gold badges29 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Jwit Over a year ago

you are missing the ON portion of your join, it should be above the where clause.

Mojtaba Over a year ago

Sorry about that, yeah you forgot ON statement, updated answer

henryaaron Over a year ago

Thank you but now I'm getting The multi-part identifier "OrderDetails.OrderID" could not be bound.

Mojtaba Over a year ago

@henryaaron Use the query I wrote for you in update3! Yours has too many issues.

wergeld · Accepted Answer · 2013-11-06 20:27:51Z

2

You do not have an ON statement after your JOIN

answered Nov 6, 2013 at 20:27

wergeld

14.5k9 gold badges58 silver badges84 bronze badges

3 Comments

Ken White Over a year ago

You got half the problem. :) I can't do half an upvote, though; SO won't let me.

wergeld Over a year ago

I figured he could combine this answer with the others.

Ken White Over a year ago

You should answer the entire question, or just comment on the other one providing the rest. As I said, I can't give half an upvote (and the poster can't split an acceptance).

Collectives™ on Stack Overflow

Don't Understand SQL Syntax Error

2 Answers 2

4 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest