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.

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.

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 [email protected] or submit a support ticket here https://www.cdata.com/support/submit.aspx