SQLite substr problem
Posted: Tue Sep 15, 2009 12:31 am
Something weird going on with a SELECT statement that includes a substr function.
This statement yields the ubiquitous "SQL error or missing database" rev error:
SELECT AccountID,substr(AccountName,12) FROM AccountHdr WHERE AccountType='Asset' AND substr(AccountName,1,11)='Receivable:' AND Hidden =0 ORDER BY AccountName
The exact same statement (copied and pasted) in the Firefox SQLite Manager plug in works fine and produces the correct results.
Trial and error shows that the following works:
SELECT AccountID,AccountName FROM AccountHdr WHERE AccountType='Asset' AND substr(AccountName,1,11)='Receivable:' AND Hidden =0 ORDER BY AccountName
So something about the substr function in the SELECT appears to be the problem. I'm using revDataFromQuery to execute the SELECT.
Any ideas?
As an aside, why does rev not return specific SQLite error messages?
Thanks,
Pete
This statement yields the ubiquitous "SQL error or missing database" rev error:
SELECT AccountID,substr(AccountName,12) FROM AccountHdr WHERE AccountType='Asset' AND substr(AccountName,1,11)='Receivable:' AND Hidden =0 ORDER BY AccountName
The exact same statement (copied and pasted) in the Firefox SQLite Manager plug in works fine and produces the correct results.
Trial and error shows that the following works:
SELECT AccountID,AccountName FROM AccountHdr WHERE AccountType='Asset' AND substr(AccountName,1,11)='Receivable:' AND Hidden =0 ORDER BY AccountName
So something about the substr function in the SELECT appears to be the problem. I'm using revDataFromQuery to execute the SELECT.
Any ideas?
As an aside, why does rev not return specific SQLite error messages?
Thanks,
Pete