Query working in Teradata but not in Oracle

Question

If I execute the following code I am able to get desired output; But in Oracle is am facing error :

SELECT NAME
  FROM STUD
 WHERE ID IN (
                 SEL DISTINCT  TAB1.ID  
                FROM (
                              SEL A.ID
                                , B.SALARY
                             FROM Fr   A
                       INNER JOIN PACK B ON A.ID = B.ID
                     ) AS TAB1
          INNER JOIN (
                              SEL A.FRIEND_ID
                                , B.SALARY
                             FROM Fr    A
                       INNER JOIN PACK  B ON A.FRIEND_ID = B.ID
                     ) AS TAB2
                  ON TAB2.SALARY > TAB1.SALARY
      )
    ;

Facing the below error in oracle : ORA-00907: missing right parenthesis

Aleksej · Accepted Answer · 2016-04-22 10:06:59Z

1

You have two errors: SEL instead of SELECT and the usage of table aliases with AS keyword, which is not supported by Oracle

SELECT NAME
  FROM STUD
 WHERE ID IN (SELECT DISTINCT  TAB1.ID  FROM
(SELECT A.ID , B.SALARY
FROM
Fr   A
INNER JOIN
PACK B
ON
A.ID = B.ID)  TAB1
INNER JOIN
(SELECT A.FRIEND_ID , B.SALARY
FROM
Fr  A
INNER JOIN
PACK  B
ON
A.FRIEND_ID = B.ID
)  TAB2
ON
TAB2.SALARY > TAB1.SALARY
)

Besides this, I think you don't need the DISTINCT in the inner query, given that you only use it in an IN clause

edited Apr 22, 2016 at 10:06

answered Apr 22, 2016 at 9:58

Aleksej

23.1k6 gold badges38 silver badges41 bronze badges

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

Collectives™ on Stack Overflow

Query working in Teradata but not in Oracle

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related