Ms-access – Error at line 1, column 25: ORA-00923: FROM keyword not found where expected

ms accessoracle

I have some code that works in Access but now I want to forward the code to our IT department to make a view in our SQL database.

Could anyone please help me convert it so it is correct for normal SQL and not Access SQL if that makes sense?

I have tried to test it in ORACLE SQL Developer but am getting the error:

Error at line 1, column 25: ORA-00923: FROM keyword not found where
expected

SELECT T1.*, 
       (SELECT TOP 1 T3.[SMN_DATEC]-1 
        FROM 
            (SELECT T2.[DET_NUMBERA], T2.[SMN_DATEC] 
             FROM [CHRISCS_EMSAL] AS T2 
             ORDER BY [DET_NUMBERA],[SMN_DATEC]
            ) AS T3 
        WHERE T3.[DET_NUMBERA] = T1.[DET_NUMBERA] 
          AND T3.[SMN_DATEC] > T1.[SMN_DATEC] 
       ) AS EndDate, 
       T1.[DET_NUMBERA], 
       T1.[SMN_DATEC]
FROM CHRISCS_EMSAL AS T1
ORDER BY T1.[DET_NUMBERA], 
         T1.[SMN_DATEC];

Best Answer

As mentioned in the comments, Oracle doesn't like the square brackets around identifiers, and it also doesn't support TOP n clauses, however, you can work around that with a couple of simple changes:

SELECT T1.*, 
   (SELECT T3.SMN_DATEC-1 
    FROM 
        (SELECT T2.DET_NUMBERA
              , T2.SMN_DATEC
              , row_number() over (ORDER BY DET_NUMBERA,SMN_DATEC) AS top
         FROM CHRISCS_EMSAL T2 
        ) T3 
    WHERE T3.TOP=1
      AND T3.DET_NUMBERA = T1.DET_NUMBERA 
      AND T3.SMN_DATEC > T1.SMN_DATEC 
   ) AS EndDate, 
   T1.DET_NUMBERA, 
   T1.SMN_DATEC
FROM CHRISCS_EMSAL T1
ORDER BY T1.DET_NUMBERA, 
         T1.SMN_DATEC;

First off remove the square brackets, then move your ORDER BY clause in T3 to a ROW_NUMBER analytic function aliased as TOP, then in the outer query of your scalar valued subquery (aliased as EndDate) change the TOP 1 clause in the select statement to a TOP=1 predicate in the where clause.

Also remove the AS from table aliases, Oracle doesn't allow it (but it's OK in column aliases.)

On closer inspection of your code it looks like your scalar valued subquery is just getting the next chronological DET_NUMERA's SMN_DATEC and subtracting 1 day from it. That's something really easy to do with Oracle analytic functions without all the scalar valued subqueries:

SELECT T1.*
     , LEAD(T1.SMN_DATEC-1) OVER (PARTITION BY T1.DET_NUMBERA
                                      ORDER BY T1.SMN_DATEC) EndDate
     , T1.DET_NUMBERA
     , T1.SMN_DATEC
  FROM CHRISCS_EMSAL T1
 ORDER BY T1.DET_NUMBERA, 
       T1.SMN_DATEC;
Related Question