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

Question

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

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?

Ethem Q · Accepted Answer

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.

jmarkmurphy · Answer

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

Sign up

CData Community

Scanning file for viruses.

This file cannot be downloaded