I have our accounting database in a BTREIVE Pervasive SQL 8.5 and am testing a few queries to it through the Pervasive.SQL Data Manger. Through all my experience with SQL I have never seen anything as strange as this.
For example I have a table named TABLE_A with 2 columns COL_X (text) and COL_Y (text). Let's assume TABLE_A has the following data:
COL_X | COL_Y
You would think to filter all the records where COL_X = aa1 you would write this query:
SELECT * FROM TABLE_A WHERE COL_X = 'aa1'
However I do not get any results. I then tried the following query:
SELECT * FROM TABLE_A WHERE COL_X LIKE 'aa1%'
And got NO RESULTS.
I then tried the following query:
SELECT * FROM TABLE_A WHERE COL_X LIKE 'aa%' (ommiting the numerical '1' in this case)
And it behaved perfectly giving me all the records (the 1st three in my sample) where there was an "aa".
It's strange the first 2 queries do not work despite the fact there THERE ARE records that match the criteria in the "WHERE" clause.
Why would this be? On a side note my accounting package that uses the Pervasive SQL database is BusinessVision 7.11.