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

Question

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];

Answer 1

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;