Solved

SQL Error [HY000]: ORDER BY is mandated in the OVER clause for the function row_number.

  • 6 September 2023
  • 3 replies
  • 68 views

Badge

I am using the 2023 xBase driver in dBeaver, and receiving this error:

SQL Error [HY000]: ORDER BY is mandated in the OVER clause for the function row_number.

 

The Query is:

select distinct

       'BNK' swityp,

       cast(c.cbank_acct as varchar) oldkey,

       row_number () over (order by g.cid) newkey,

       '' del,

       g.cgroup_cod,

       g.acct_no,

       g.acct_descr,

       g.active,

       g.cid

from checkreg c

join glacct g on g.cid = c.cbank_acct

order by g.cid

 

As you can see I have an ORDER BY in the OVER clause. BTW, changing it to upper case has no effect. What am I missing? Does xBase only partially support row_number? Is there any documentation on the xBase driver and which syntax it supports?

icon

Best answer by Ethem Q 8 September 2023, 17:46

View original

3 replies

Userlevel 5
Badge

Thank you for posting your issue here. From a first look, the syntax of the query you are executing is quite correct so there should not be a reason for you to see the error. I suspect there might be an issue on how the query is being processed under the hood but in order to verify that we will need our engineering team to make a more in-depth investigation.

 

In the meantime, you might want to use the following query as a workaround:

SELECT DISTINCT * FROM ( SELECT
      'BNK' swityp,
       CAST(c.cbank_acct as varchar) oldkey,
       ROW_NUMBER () OVER (ORDER BY g.cid) newkey,
       '' del,
       g.cgroup_cod,
       g.acct_no,
       g.acct_descr,
       g.active,
       g.cid
FROM checkreg c
JOIN glacct g on g.cid = c.cbank_acct
ORDER BY g.cid)

 

Let me know if that works for you.

Badge

This does work. I did not even consider that the distinct was the culprit. thanks for the workaround. A note for the engineering teem, it isn’t just row_number() that fails like this, rank() and dense_rank() have the same issue, and workaround.

Userlevel 5
Badge

I wanted to update this thread as the engineering team has implemented a fix for the issue that was reported here.

If you want to get an updated version of the JDBC driver for xBase feel free to reach our support team out at support@cdata.com or submit a support ticket here https://www.cdata.com/support/submit.aspx

Reply