CODEWITHSUNDEEP
sqldatabaseoracle-databaseora-00933
aMerkuri
aMerkuri

Reputation: 173

ORA-00933 SQL command not properly ended

I need to convert MSSQL query to Oracle but end up with SQL command not properly ended.

Here is MSSQL query

SELECT * FROM [dbo].[trade] AS [Extent1]
WHERE EXISTS (
    SELECT 1 AS [C1] FROM
    [dbo].[findetail] AS [Extent2]
    INNER JOIN [dbo].[transact] AS [Extent3] ON [Extent2].[transact] = [Extent3].[transact]
    WHERE [Extent1].[trade] = [Extent2].[trade]
    AND 'ACCR' = [Extent3].[subledger]
    AND [Extent3].[date] = '2016-03-18T00:00:00'
)

Converting it to Oracle SQL I end with this.

SELECT * FROM trade Extent1
WHERE EXISTS
  (SELECT 1 C1 FROM findetail Extent2
    JOIN transact Extent3
    ON Extent2.transact=Extent3.transact
    WHERE Extent1.trade=Extent2.trade 
    AND 'ACCR'=Extent3.subledger
    AND  Extent3.date='2016-03-18T00:00:00'
  );

and receive error above.

Upvotes: 0

Views: 766

Answers (2)

MT0
MT0

Reputation: 168671

DATE is a reserved word so needs to the surrounded in double quotes and, I am assuming that it is of DATE data type so you will probably need to convert the string:

SELECT *
FROM   trade t
WHERE  EXISTS (
         SELECT 1
         FROM   findetail f
                JOIN transact r
                ON f.transact = r.transact
         WHERE  t.trade   = f.trade 
         AND    'ACCR'    = r.subledger
         AND     r."DATE" = TO_DATE( '2016-03-18T00:00:00', 'YYYY-MM-DD"T"HH24:MI:SS' )
      );

If you just use the string in r."DATE" = '2016-03-18T00:00:00' then Oracle will implicitly try to convert the string literal using the TO_DATE() function with the NLS_DATE_FORMAT session parameter as the format mask. If they match then it will work but this is a client variable so can be changed and then the query will break without the code having changed (and be a pain to debug). The simple answer is to ensure that you compare date value by either using TO_DATE() and specifying the format mask (as per the query above) or to use an ANSI date literal DATE '2016-03-18' (which is independent of the NLS settings).

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271003

Date formats are different in Oracle. Perhaps something like this:

SELECT *
FROM trade Extent1
WHERE EXISTS (SELECT 1 
              FROM findetail Extent2 JOIN
                   transact Extent3
                   ON Extent2.transact = Extent3.transact
              WHERE Extent1.trade = Extent2.trade AND
                    Extent3.subledger = 'ACCR' AND
                    Extent3."date" = DATE '2016-03-18'
             );

Upvotes: 0

Related Questions