Oracle SQL error "missing expression" (but same SQL query works in MySQL)

Question

I have an existing large enterprise application that we are converting the database from MySQL to Oracle for various reasons. We've got hundreds of current MySQL commands working inside Oracle without much hassle, except for one. The following SQL executes on MySQL without any issues:

SELECT `user_id`, `shift_type`, `date`, COUNT(*) as c
FROM `staff`
WHERE `date` = '2020-02-02' AND `shift_type` != 'oncall'
GROUP BY user_id, shift_type, date
HAVING c > 1

This executes and gives us the results of any staff who exist more than once for a given date with the criteria above. But running that same SQL query in Oracle, with the apostrophes removed (as required by Oracle), we get a Error Message : ORA-00936: missing expression. This is the exact Oracle SQL I tried:

SELECT user_id, shift_type, date, COUNT(*) as c
FROM staff
WHERE date = '2020-02-02' AND shift_type != 'oncall'
GROUP BY user_id, shift_type, date
HAVING c > 1

I've checked lots of other Stack Overflow answers about this error code. Most refer to removing an extra comma. I've tried that on various parts above, but nothing seems to work. Really hoping someone here is able to help point me in the right direction.

"with the apostrophes removed (as required by Oracle)" - as required by the SQL standard, this is not Oracle specific. The dreaded backticks are simply invalid standard SQL. — user330315
– user330315, Commented Aug 28, 2021 at 9:07
thanks @a_horse_with_no_name - the backticks come from the ORM we are using, but good to know. The ORM does not seem to generate the backticks in the Oracle query, so thats an improvement :) — Laurence
– Laurence, Commented Aug 28, 2021 at 9:11

Mark Rotteveel · Accepted Answer · 2021-08-28 09:17:08Z

4

+500

Proper syntax - "date" instead of date and HAVING COUNT(*)>1:

SELECT user_id, shift_type, "date", COUNT(*) as c
FROM staff
WHERE "date" = '2020-02-02' 
  AND shift_type != 'oncall'
GROUP BY user_id, shift_type, "date"
HAVING COUNT(*) > 1;

db<>fiddle.com demo

ORA-00936: missing expression

Oracle expects date literal here, thus error:

SELECT date '2020-01-01'
FROM dual

Also using keywords as identifiers is not the best practice.

edited Aug 28, 2021 at 9:17

Mark Rotteveel

110k240 gold badges160 silver badges233 bronze badges

answered Aug 28, 2021 at 8:56

Lukasz Szozda

181k26 gold badges278 silver badges326 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

user330315 Over a year ago

It probably needs to be "DATE" as Oracle stores identifiers as uppercase by default.

Laurence Over a year ago

Thanks @Lukasz Szozda - that works. I agree "date" is not best practice, its a legacy application, so I dont think I can change it now. But we'll keep it in mind. I'll flick you the bounty when SO allows me. cheers.

ibre5041 Over a year ago

DATE is a keyword in (all) SQL dialects and namely in ISO SQL. This can easily stop working in future MySQL release too.

Collectives™ on Stack Overflow

Oracle SQL error "missing expression" (but same SQL query works in MySQL)

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related