troubleshooting Question

SELECT query not filtering records properly

Avatar of itcaptain
itcaptainFlag for Canada asked on
Databases
13 Comments1 Solution1577 ViewsLast Modified:
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
aa1, 55
aa1, 234
aa2, 40
bb1, 50
bb2, 70

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.
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 13 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 13 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros