0

I am trying to migrate my existing data from oracle to mysql and am having a constraint of going with Mysql5.6. I have one view in the DB which is using OVER() clause. When I am trying to run the CREATE query in mysql workbench, it straight away gives me a syntex error around OVER().

Can someone please help me with a workaround.

Part of the DDL

COUNT(*) OVER(PARTITION BY mei.ID ORDER BY mei.EVENTDATE ASC ROWS UNBOUNDED PRECEDING) COUNT,
    COUNT(*) OVER(PARTITION BY mei.ID,mei.SUBTRANTYPEVIEW ORDER BY mei.EVENTDATE ASC ROWS UNBOUNDED PRECEDING)
APC
  • 144,005
  • 19
  • 170
  • 281
Naxi
  • 1,504
  • 5
  • 33
  • 72
  • Please do not tag `[mysql]` questions with `[oracle]`. They are two different RDBMS products (even if owned by the same corporation). – APC Mar 26 '19 at 15:33
  • MySQL 5.6 does not supports window functions. It was added to MySQL 8. – abestrad Mar 26 '19 at 15:34
  • You have to use user defined variables for that, but they don't work in a view. – fancyPants Mar 26 '19 at 15:36
  • Possible duplicate of [MySql using correct syntax for the over clause](https://stackoverflow.com/questions/6292679/mysql-using-correct-syntax-for-the-over-clause) – abestrad Mar 26 '19 at 15:36

1 Answers1

0

the syntax between oracle db and mysql is a little different

Example: SELECT a, b, COUNT(*) OVER() FROM table

is equivalent to: SELECT a, b, ( SELECT COUNT(*) FROM table ) FROM table

galzalait
  • 231
  • 2
  • 6