CODEWITHSUNDEEP
sqloracle-database
JRidge
JRidge

Reputation: 7

ORA-00936: Missing Expression Error Code

New here and was wondering if anyone can shed some light into this ORA-00936 error with my SQL Query.

select Count(Auftragsnr) as Open_Orders
from wa_kopf
where convert(varchar(4), Aender_Datum, 104) = convert(varchar(4), getdate(), 104)
and zustand < (60);

Get the following error:

14:47:37 [SELECT - 0 row(s), 0.000 secs] [Error Code: 936, SQL State: 42000] ORA-00936: Missing Expression

Any help would be greatly appreciated.

Regards

JRidge

Upvotes: 0

Views: 14860

Answers (1)

Alex Poole
Alex Poole

Reputation: 191235

You're using SQL Server syntax, which won't work with an Oracle database. You seem to be trying to format a date as dd.mm.yyyy (but in a 4-character string, somehow?). There is no getdate function in Oracle either, but it does have sysdate instead.

So the Oracle equivalent for what I think you're trying to do would be:

where to_char(Aender_Datum, 'dd.mm.yyyy') = to_char(sysdate, 'dd.mm.yyyy')

But that would just mean you're comparing dates as string, which isn't generally a good idea. If you're looking for any value from today you could use the trunc() function, which strips the time portion from a date by default; if your column is only representing a date and the time is always midnight then you can do:

where Aender_Datum = trunc(sysdate)

If your column has varying times then you could truncate that as well:

where trunc(Aender_Datum) = trunc(sysdate)

... but that can have a performance impact if the column is indexed, and an alternative is to use a range comparison:

where Aender_Datum >= trunc(sysdate)
and Aender_Datum  < trunc(sysdate) + 1

Upvotes: 1

Related Questions