Re: SQL query problem with character comparison.

Hi Leena

I have a problem in Foxpro. We have a query like
Select * from Account, Bank where alltrim(account.bank_id) =
alltrim(bank_bank_id) and alltrim(account.Bac) = alltrim(Bank.Bac) and
alltrim(Bank.Bac) = '0000991' and not Deleted()

Bank table has a column Bac which is type Charater(7). The application
always inserts 7 charactes in this column. Eg. for 71 it will insert
0000071. But i have some records in the table which are marked deleted
and they have bac column as 0001 i.e is less than 7 characters.
The problem is above query does not return a result set even though
there are records present.
Moreover if i pack the table Bac, the query works fine.

I also tried changing the query to Bank.Bac = '0000144'. Initially it
worked fine and returned me 400 records. Then i zapped 300 records
from account and executed the query. this time it agan gave me no
records. By some trial and error i found that the modified query
worked if there were more than 170 records. For any less it gave blank
recordset.

I want to find out the reason for such weird behaviour and if this
some Foxpro limitation

The part NOT DELETED() of your select statement can not work correctly since foxpro finds some random value for this expression. You don't tell Foxpro which of your tables should be checked for DELETED(), and you cannot tell it! Even if you add some alias as a parameter like DELETED("bank") cannot work, since the internal aliasses of the tables during execution of the SELECT statement are most likely different from the pure table names.
You should avoid it if you SELECT from more than on table.
But SELECT ... FROM singleTable WHERE !deleted() will work as one expects.

Mostly it works correct, if you SET DELETED ON and then SELECT FROM more than one table.

If you want to be asolutely sure, that you don't get any deleted records,
so first do some
SELECT * FROM table1 INTO CURSOR cursor1 FOR NOT deleted()
SELECT * FROM table2 INTO CURSOR cursor2 FOR NOT deleted()
and then:
SELECT ... FROM cursor1, cursor2,...


Besides the DELETED() problem, it does not sound very good, if you reference two tables by alltrim'ed expressions. There is a big risk, that there will be some hits you did not expect: alltrim("0000111") = alltrim("0000 ") returns .T., same does alltrim(" 00001") = alltrim("0000 "). Maybe you don't want these results...

Regards
Bernhard Sander
.

Relevant Pages

  • Re: 10g Bind peeking causes multiple cursors for same query with perl DBD::Oracle
    ... My query uses 2 bind ... > variables and when it is prepared oracle generates 1 cursor for my ... When I execute that prepared statement with actual input bind ... it generates a 2nd cursor for the same query. ...
    (comp.databases.oracle.server)
  • 10g Bind peeking causes multiple cursors for same query with perl DBD::Oracle
    ... My query uses 2 bind ... When I execute that prepared statement with actual input bind ... it generates a 2nd cursor for the same query. ...
    (comp.databases.oracle.server)
  • Re: Avoiding a Double Cursor...
    ... > going to be returned by the query. ... > whole cursor one time, ... select rownum rnum, ename ... select rownum rnum, ename, countover ...
    (comp.databases.oracle.misc)
  • RE: Oracle 10g and DBD::Oracle
    ... I'm hoping to get some insight into why oracle creates 2 cursors for the ... My query uses 2 bind ... When I execute that prepared statement with actual input bind variables, ... it generates a 2nd cursor for the same query. ...
    (perl.dbi.users)
  • Re: SQL query problem with character comparison.
    ... SET DELETED ON before the query ... FoxPro SQL queries are not governed by the setting of SET EXACT. ... Bank table has a column Bac which is type Charater. ... always inserts 7 charactes in this column. ...
    (microsoft.public.fox.programmer.exchange)