SQL join subquery

Question

I tried searching for an answer to my problem but failed to get an answer that actually helped. I've written an sql code but can't seem to find where the problem is. Is it possible to put a subquery in the "FROM" section?

SELECT S2.ITEM,
       S1.SHOP_ORDER,
       S1.OPERATION_NO,
       S1.START_DATE,
       S1.QTY_GOODS,
       S1.QTY_ENTERED,
       S1.QTY_MRB
FROM   (SELECT SHOP_ORD_RPT.OPERATION_NO,
               SHOP_ORD_RPT.SHOP_ORDER
        FROM   FLAME.SHOP_ORD_RPT
        WHERE  SHOP_ORD_RPT.OPERATION_NO = 110
                OR SHOP_ORD_RPT.OPERATION_NO = 370) AS S1
       JOIN (SELECT SHOP_ORD.SHOP_ORDER
             FROM   FLAME.SHOP_ORD
             WHERE  SHOP_ORD.ITEM = '3A2375'
                     OR SHOP_ORD.ITEM = '3A2703')AS S2
         ON S1.SHOP_ORDER = S2.SHOP_ORDER;

The error I receive upon running the script is:

SQL command not properly ended

I'd appreciate if anyone can help. Thank you very much- Ruth

Remove the "AS" in AS S1 and AS S2. possible duplicate of SQL Command not properly ended? — Martin Smith
– Martin Smith, Commented Sep 10, 2013 at 12:06
I take it this is oracle. That's one of my least favourite error messages. You have a syntax error somewhere. It might be worth a shot to put aliases for the shop_order columns in each subquery. Otherwise, start again and go with baby steps until you find the error. — Dan Bracuk
– Dan Bracuk, Commented Sep 10, 2013 at 12:10
You don't need subqueries here -- just join the tables in the main query itself. — David Aldridge
– David Aldridge, Commented Sep 10, 2013 at 12:16
@DanBracuk Why would aliasing those columns help? Did you not look at the possible duplicate link and see it is the same issue? — Martin Smith
– Martin Smith, Commented Sep 10, 2013 at 12:16
@MartinSmith, thinking something is worth a shot is not the same as thinking it will work. As far as your link is concerned, I first saw your comment after I posted mine. I'm a slow typer. — Dan Bracuk
– Dan Bracuk, Commented Sep 10, 2013 at 12:22

Robert · Accepted Answer · 2013-09-10 12:13:08Z

41

Try to remove AS keywrod after FROM and JOIN subquery as below

SELECT S2.ITEM, S1.SHOP_ORDER, S1.OPERATION_NO, S1.START_DATE,S1.QTY_GOODS, S1.QTY_ENTERED, S1.QTY_MRB
FROM (
    SELECT SHOP_ORD_RPT.OPERATION_NO, SHOP_ORD_RPT.SHOP_ORDER
    FROM FLAME.SHOP_ORD_RPT
    WHERE SHOP_ORD_RPT.OPERATION_NO =110
    OR SHOP_ORD_RPT.OPERATION_NO =370
    )  S1
JOIN (
    SELECT SHOP_ORD.SHOP_ORDER
    FROM FLAME.SHOP_ORD
    WHERE SHOP_ORD.ITEM ='3A2375'
    OR SHOP_ORD.ITEM ='3A2703'
    )  S2
ON S1.SHOP_ORDER = S2.SHOP_ORDER;

edited Sep 10, 2013 at 12:13

answered Sep 10, 2013 at 12:02

Robert

25.8k8 gold badges70 silver badges86 bronze badges

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

Comments

ChrisProsser · Accepted Answer · 2013-09-10 12:11:30Z

12

It is possible to put a subquery within the from clause. Try this:

SELECT S2.ITEM,
       S1.SHOP_ORDER,
       S1.OPERATION_NO,
       S1.START_DATE,
       S1.QTY_GOODS,
       S1.QTY_ENTERED,
       S1.QTY_MRB
FROM   (SELECT SHOP_ORD_RPT.OPERATION_NO,
               SHOP_ORD_RPT.SHOP_ORDER
        FROM   FLAME.SHOP_ORD_RPT
        WHERE  SHOP_ORD_RPT.OPERATION_NO = 110
                OR SHOP_ORD_RPT.OPERATION_NO = 370) S1,
      (SELECT SHOP_ORD.SHOP_ORDER
       FROM   FLAME.SHOP_ORD
       WHERE  SHOP_ORD.ITEM = '3A2375'
               OR SHOP_ORD.ITEM = '3A2703') S2
WHERE S1.SHOP_ORDER = S2.SHOP_ORDER;

answered Sep 10, 2013 at 12:11

ChrisProsser

13.2k6 gold badges38 silver badges45 bronze badges

Collectives™ on Stack Overflow

SQL join subquery

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related