Converting a MySQL query to Oracle

Question

The following query was working fine in MySQL:

SELECT
  total,
  status.nome
FROM status
  INNER JOIN (SELECT count(*) AS total,
                     status_id
                FROM [log]
               WHERE evento_id = '21'
                 AND data BETWEEN '02/09/2013 00:00:00' AND '02/09/2013 23:59:59'
               GROUP BY status_id) AS groupedTable
ON groupedTable.status_id = status.id;

but when I try to run it in Oracle, i get the following error message:

ORA-00903: invalid table name

If I change the query as:

SELECT 
    total, status.nome 
FROM 
    status 
    INNER JOIN (
        SELECT count(*) as total, status_id 
          FROM log
        WHERE evento_id = '21' 
          AND data BETWEEN '02/09/2013 00:00:00' AND '02/09/2013 23:59:59' 
        GROUP BY status_id) AS groupedTable 
    ON groupedTable.status_id = status.id;

i get this error message instead:

ORA-00905: keyword not found

I think the problem is in the log table but I am not able to convert it and make it work in Oracle. Can anybody please help me?

Please read about how to compare dates: http://stackoverflow.com/questions/10178292/comparing-dates-in-oracle-sql/10178346#10178346; you've not here which will cause all sorts of problems. — Ben, Sep 03 '13 at 12:05

score 2 · Accepted Answer · answered Sep 03 '13 at 12:36

2

First, you were right to remove the square brackets from the [log] table. The square brackets are not a valid delimiter, and they're not valid characters for a table name.

Second, Oracle doesn't like the AS in SELECT ... FROM myTable AS myAlias. It allows AS for column aliases, but not for table aliases, so drop the As in As groupedTable.

Third, your date format is probably invalid. If it isn't, it's still a potential failure point. Oracle's DATETIME literal format is like this (MySQL supports this too):

DATE 'YYYY-MM-DD'

Further down on the referenced page, you'll see that the TIMESTAMP literal (at least as far as you need it) is:

TIMESTAMP 'YYYY-MM-DD HH:MI:SS'

The hours should use the "24-hour" clock, so 4PM is 16:00:00.

I'd recommend changing the date logic to this:

AND data >= DATE '2013-02-09' AND data < '2013-02-10'

Or it you're keen to use BETWEEN here you could go with the wordier option:

AND data BETWEEN DATE '2013-02-09' AND TIMESTAMP '2013-02-09 23:59:59'

answered Sep 03 '13 at 12:36

Ed Gibbs

25,924
4
46
69

Thanks, now it's works... SELECT total, status.nome FROM status INNER JOIN (SELECT COUNT(*) as total, status_id FROM log WHERE evento_id = '21' AND data >= DATE '2013-09-02' AND data <= DATE '2013-09-03' GROUP BY status_id) ON status_id = status.id – brbrbr Sep 03 '13 at 13:03
Excellent! But note the logic in your original question looked for dates of any time on `9/2/2013`. To do that, you need to end the `WHERE` with `... AND data < DATE '2013-09-03` (less than, not less than or equal to like in your comment). If you use `<=` then a date of 9/3/2013 at midnight will be included. – Ed Gibbs Sep 03 '13 at 13:08
Yeah, I have seen that now... i changed to SELECT total, status.nome FROM status INNER JOIN (SELECT COUNT(*) as total, status_id FROM log WHERE evento_id = '21' AND data >= TIMESTAMP '2013-09-02 00:00:00' AND data <= TIMESTAMP '2013-09-02 23:59:59' GROUP BY status_id) ON status_id = status.id – brbrbr Sep 03 '13 at 15:02

Converting a MySQL query to Oracle

1 Answers1