1

I'm getting the missing right parenthesis error on this query. What am I missing here, I see 5 left and 5 right parenthesis. What I'm trying to do is to only return rows with the most current date.

WITH snCTE AS 
(
   SELECT
    T1.column1
    ,T1.column2
    ,T2.column3
    ,T2.column4
    ,T1.datefield
    ,ROW_NUMBER() OVER (PARTITION BY column1 ORDER BY datefield DESC) AS RN
    FROM dBtable1 T1
    LEFT OUTER JOIN dBtable2 T2 ON (T1.columnid=T2.columnid AND T1.otherfield=T2.otherfield)
    WHERE EXISTS (SELECT 1 FROM dbtable3 AS T3 WHERE T3.column1 = T1.column1)
)

SELECT column1, column2, column3, column4, datefield
FROM snCTE
WHERE snCTE.RN = 1
Barbaros Özhan
  • 59,113
  • 10
  • 31
  • 55
Jennifer K.
  • 11
  • 1

2 Answers2

4

The error message is misleading; the real issue is that Oracle does not support AS in table aliasing, but only for columns, so 

FROM dbtable3 AS T3

should be edited into 

FROM dbtable3 T3

For example:

SQL> select 1 as ONE from dual;

       ONE
----------
         1

SQL> select 1 as ONE from dual D;

       ONE
----------
         1

SQL> select 1 as ONE from dual as D;
select 1 as ONE from dual as D
                             *
ERROR at line 1:
ORA-00933: SQL command not properly ended
Aleksej
  • 22,443
  • 5
  • 33
  • 38
0

The source of the error ORA-00907 is using AS in FROM dbtable3 AS T3, since, when aliasing tables or subqueries using AS is not allowed in Oracle DB.

So, Just remove that AS keyword .

Barbaros Özhan
  • 59,113
  • 10
  • 31
  • 55
  • oh boy, that was in fact misleading. I've removed the "as" and the error is gone, thanks so much! Now I'm facing column ambiguously defined, but I should be able to figure that one out. Thanks! – Jennifer K. Sep 11 '18 at 08:51