Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2016
    Posts
    3

    Unanswered: Access SQL to Oracle SQL

    Hi all,

    I have some Access SQL code that i want our IT dept to create a Oracle SQL view from as it takes to long to run in Access.

    I have tried it in SQL developer but getting this error.
    Error at line 1, column 25:
    ORA-00923: FROM keyword not found where expected

    Below is the code, could anyone please help?

    Thanks in advance

    Chris

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

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Oracles flavour of sub queries is different to Access. If I was a gambler I'd hazard a few quid on it to be Access/JET's flavour of SQL is most divergent from the standard

    http://www.techonthenet.com/oracle/subqueries.php

    https://www.google.co.uk/webhp?sourc...acle+subselect
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Put all your logic in the oracle view and then simply do an external link the the view from the oracle database via an odbc link. Much faster. Also what is the database version of the oracle database that you are connecting to. For example the TOP command is not implemented in Oracle until version 12. but they do it different (see below)

    SELECT val
    FROM rownum_order_test
    ORDER BY val DESC
    FETCH FIRST 5 ROWS ONLY;
    Last edited by beilstwh; 01-20-16 at 09:44.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  4. #4
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    "TOP" is not a keyword in Oracle. You will likely have to do a lot of re-work to get this into an Oracle ready form.

  5. #5
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    If Chriscs is NOT the oracle schema then use the following. If it is then replace CHRISCS_ WITH chriscs.

    Code:
    CREATE OR REPLACE VIEW MY_VIEW AS
      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 Rn
                        FROM Chriscs_emsal T2) T3
               WHERE     T3.Det_numbera = T1.Det_numbera
                     AND T3.Rn = 1
                     AND T3.Smn_datec > T1.Smn_datec)
                Enddate,
             T1.Det_numbera,
             T1.Smn_datec
        FROM Chriscs_emsalt T1
    ORDER BY T1.Det_numbera, T1.Smn_datec;
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •