Re: SQL query problem with character comparison.
- From: "Curious Joe" <joebob.johnson@xxxxxxxxx>
- Date: 20 Mar 2007 09:35:21 -0700
On Mar 20, 5:04 am, "Leena" <Leena.re...@xxxxxxxxx> wrote:
Hi,
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
Please reply.
Thank you
Leena,
I do not have Fox 2.6 onhand to prove this query works but it works in
9.0(SP1) and I am making the assumption that the deleted records are
less than 7 chars in length.
use bank in 0 alias "Bank"
use account in 0 alias "Account"
select * from Bank, Account where Account.bank_id =
Bank.bank_id and Account.Bac = Bank.Bac and
Bank.Bac = '0000991' and len(alltrim(Bank.Bac))=7
If there are records marked for deletion that are actually 7 chars in
length, replace "len(alltrim(Bank.Bac))=7" with "!deleted("Bank")".
HTH,
CJ
.
- References:
- SQL query problem with character comparison.
- From: Leena
- SQL query problem with character comparison.
- Prev by Date: Re: Foxpro going open-sourced?
- Next by Date: Re: SQL query problem with character comparison.
- Previous by thread: Re: SQL query problem with character comparison.
- Next by thread: Re: SQL query problem with character comparison.
- Index(es):
Relevant Pages
|
