I see that that a similar question was posted by slacka 6 months ago, and remains unfixed.
I don't understand why this issue hasn't been taken seriously. This seems to be a critical issue in the way a client is able to, or in many cases, not able to retrieve data from a BusinessVision database. I have tested this over the past week and the issue still exists as of today, even in brand new data sets generated in a clean install from Sage BusinessVision CSE 2023 v7.93.05, running the latest patched version of Actian v15.21.006.
Several indexes particularly on GL_TRANSACTIONS, AP_TRANSACTIONS, and AR_TRANSACTIONS tables seem to be broken. Successful table queries are only possible by dropping indexes or bypassing them with LTRIM statements or by converting data types, and in some case by turning ANSI padding off.
Although there were some issues in Pervasive, these problems have become significantly worse since the addition of Actian 15 with the BusinessVision 2023 release.
Try running these commands, and you will quickly confirm that there are problems, that should be of high priority to have resolved.
PROBLEMS WITH GL_TRANSACTIONS TABLE & INDEXES
//This does not work as expected, many results are missing
//RECNO is an integer but appears to sort as if it was a string data type.
select * from "GL_TRANSACTIONS" order by RECNO
//This does not work as expected, many results are missing
select * from "GL_TRANSACTIONS" where TRANS_NO = '{insert known transaction #}'
//This does not work as expected, many results are missing
select DISTINCT(TRANS_NO) from "GL_TRANSACTIONS"
//This DOES work as expected because it bypasses the indexes
select * from "GL_TRANSACTIONS" where LTRIM(TRANS_NO) = '{insert known transaction #}'
//This DOES work as expected because it bypasses the indexes
select * from "GL_TRANSACTIONS" where LTRIM(TRANS_NO) = '{insert known transaction #}' ORDER BY CONVERT(RECNO, SQL_INTEGER)
PROBLEMS WITH AP_TRANSACTIONS TABLE & INDEXES
//This does not work as expected, many results are missing
select * from AP_TRANSACTIONS where VEND = '{insert known vendor number}'
PROBLEMS WITH AP_TRANSACTIONS TABLE/INDEXES
//This does not work as expected, many results are missing
select * from AR_TRANSACTIONS where CUST = '{insert known customer number}'
