Solved

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

  • 6 September 2023
  • 2 replies
  • 38 views
J
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

2 replies

Ethem Q
Userlevel 1
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.

J
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.

Reply


Terms and ConditionsCookie settings