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?](http://stackoverflow.com/questions/9811711/sql-command-not-properly-ended) — Martin Smith, Sep 10 '13 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, Sep 10 '13 at 12:10
You don't need subqueries here -- just join the tables in the main query itself. — David Aldridge, Sep 10 '13 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, Sep 10 '13 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, Sep 10 '13 at 12:22

Robert · Answer 1 · 2013-09-10T12:13:08.953

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;

score 12 · Answer 2 · answered Sep 10 '13 at 12:11

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;

SQL join subquery

2 Answers2

Linked