CODEWITHSUNDEEP
sqloracle-database
user6229795
user6229795

Reputation:

Query working in Teradata but not in Oracle

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

Upvotes: 0

Views: 44

Answers (1)

Aleksej
Aleksej

Reputation: 22949

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

Upvotes: 1

Related Questions