0

I am trying to run the below ddl statment generated on DB2 to mysql. However, it gives me error that row_number() doesn't exist on mysql.

Can someone please tell me how to emulate this in mysql ?

Query

CREATE VIEW "MY_PORTAL"."MyView" AS SELECT ROW_NUMBER() OVER() AS id, b.id batch_id, 
b.name batch_name, b.description batch_desc, b.status batch_status, b.classname batch_classname, 
b.active batch_active, b.server batch_server, s.id scheduler_id, s.typeid scheduler_type_id, 
str.typename scheduler_type_name, s.days scheduler_days, s.hours scheduler_hours, s.minutes scheduler_minutes, 
s.seconds scheduler_seconds FROM myDB.batches b LEFT OUTER JOIN myDB.schedulers s 
ON s.batchid = b.id LEFT OUTER JOIN myDB.scheduler_type_ref str ON str.typeid = s.typeid;
Paul Vernon
  • 3,818
  • 1
  • 10
  • 23
Naxi
  • 1,504
  • 5
  • 33
  • 72
  • Upgrade to version 8. – mustaccio Jan 19 '19 at 14:05
  • I have to aws aurora as target DB. Unfortunately, it only supports verdion 5.6, 5.7 – Naxi Jan 19 '19 at 16:24
  • You could simulate it with a variable, as shown for example in [this answer](https://stackoverflow.com/questions/10762598/how-to-use-unique-column-values-as-input-into-another-select-statement/10763237#10763237), but you cannot use MySQL variables in a view, so you're up for some refactoring. – mustaccio Jan 20 '19 at 17:28

0 Answers0