Btree index problem (OpenInsight 32-bit)
At 21 JUL 2020 04:36:54AM cmeyer wrote:
I have had many clients with a btree index issue after clearing the file after the year end process.
The issue is a btree index on a varchar(65531) key dictionary (EMPLOYEE_NO) as the first part of a 2 part key table. The varchar(65531) is a left over from the old AREV days. Rebuilding the index did not fix the problem. Had to remove the index, change the dictionary to a varchar(255) and re establish the index and all is fixed. Removing the index and re establish the index WITHOUT changing to a varchar(255) did NOT fix the issue.
The problem manifested itself using a select on the EMPLOYEE_NO dictioanry and the Rlist did not select all the required records.
Is there a know problem btree indexing a varchar(65531).
Do I need to change all other btree indexed varchar(65531) dictionaries to varchar(255). If so, this would be a mammoth task fixing all my clients.
Any advice would be grateful.
Chris.
At 21 JUL 2020 04:45AM Andrew McAuley wrote:
We'd need to know more specifics about what was and what was not selected and what the criteria were. That said we are not aware of any such issues and wonder if perhaps changing the DATATYPE (which is all but ignored in OI - it is there for ODBC/Dataset operations) also changed something like the dictionary justification?
World leaders in all things RevSoft
At 21 JUL 2020 05:16AM cmeyer wrote:
The specific select statement in the commuter module is as follows:
Sel = 'SELECT PAY_DETAILS WITH EMPLOYEE_NO = ':Quote(Employee_No)
Rlist(Sel, 5,
,,'')All changes were made via table builder.
Remove the index on the EMPLOYEE_NO the select is OK. Put the btree index back and the select is NBG.
Change the dictionary from varchar(65531) to varchar(255) and re establish the btree index the select is OK.
I did NOT have to touch/alter the other 2 indexes on the PAY_DETAILS table for the select to start working.
Therefore I assumed there was nothing wrong with the !PAY_DETAILS file and suspected there may be an issue with indexing this type of dictionary.
There were 4 clients (so far) reporting the same issue and duplicated on my development laptop.
Let me know if there are any other details you require.
Chris
At 21 JUL 2020 01:13PM Andrew McAuley wrote:
If you replace the rlist with a btree.extract call do you get the same errors?
World leaders in all things RevSoft
At 22 JUL 2020 07:11PM Matthew Crozier wrote:
If you replace the rlist with a btree.extract call do you get the same errors?
If the Btree.Extract works ok then perhaps the OpenInsight 9.4.5 Standalone Patch will fix this, especially if the EMPLOYEE_NO index is Left justified with typically numeric values.
HTH, M@