query question

[martinwcole]

if I create a query on a table and then try to find a record in that table based on an index, its failing

t1=table.open("table1")
t1.index_primary_put("customers")
query.filter="some filter"
idx=t1.query_create()

t2=table.open("table2")
t2.fetch_first()
while .not. t2.fetch_eof()
rec=t1.fetch_find(t2.customer)
this doesn't find a valid record

I presume?? that if a table is in a query state, you can't do a fetch_find on that table based on one of its indexes

is this correct?

[Tom Cone Jr]

RE: query question

Martin, I believe fetch_find() uses the current or primary index. I suggest you set the order for the recordset returned by the query so that key obtained from t2 will match the sort order in effect in the query.

i.e.

query.order = "customer_id"


Also, unless you have other reasons for running the query, it's not needed to simply find the desired record. You could set the index in t1 without using a query. (t1.index_primary_put("indextagdesired")) Then retrieve the key from t2 and use it to fetch_find() the desired record in t1.

If the query isn't needed for other purposes it's probably slowing things down unnecessarily.

-- tom

[martinwcole]

RE: query question

in this case I have a date field in one file but not in the other - thus the query - I was hoping that if I created a query on a table, that I could fetch for a particualr customer based on that index and it would only find records in that query - I have worked out a different approach - but is much slower.

[Tom Cone Jr]

RE: query question

Martin, try

t1=table.open("table1")
't1.index_primary_put("customers")
query.order = "Name of your customer field in tbl t1 goes here"
query.filter="some filter"
idx=t1.query_create()

t2=table.open("table2")
t2.fetch_first()
while .not. t2.fetch_eof()
rec=t1.fetch_find(t2.customer)


Experimenting with the customer table in the sample invoice app I found:

1. If I set the primary index to customer lastname, and then run a find by key (Ctrl-K) I get prompted to supply a lastname to search for. However, if I set the primary index to customer lastname, and then run a query by form to return all customers in bill_state = "MA", then a Ctrl-K (find by key) prompts me to supply the desired record number to search for. Because the QBF doesn't specify an 'order' in the returned recordset, Alpha Five presents it in recordnumber order. Your script may be failing because the query returns a set of records arranged in order by recordnumber, and then you do a fetch_find (which is also a find by key) assuming the sort order of your original index is still in place.

2. So, why not define the sort order for the returned set of records in your query? which is what the code changes I've suggested above is attempting.

-- tom

[martinwcole]

RE: query question

Tom - as per your suggestion, I got the following script to work

t1=toseconds(time())
ph=table.open("payment history")
dim dtest1 as d
dim dtest2 as d
dim mo as n
dim yr as n
yr=year(date())
n=0

pay=table.open("payments")
for mo = 1 to 12
dtest1=ctod(str(mo,2,0) + "/01/" + str(yr,4,0))
dtest2=(addmonths(dtest1,1))-1
query.filter="between(payment_date,dtest1,dtest2)"
query.order="paymentlink"
idx=ph.query_create()

pay.fetch_first()
while .not. pay.fetch_eof()
rec=ph.fetch_find(pay.paymentlink)
if rec > 0
n=n + 1
end if
pay.fetch_next()
end while
idx.drop()
next
ph.close()
pay.close()
t2=toseconds(time())
ui_msg_box("" + str(t2 - t1),"" + str(n))
end

however - note***
if I say this for the query
query.filter="month(payment_date)=var->mo .and. year(payment_date=var->yr)"
query.order="paymentlink")
***it won't work.

but the first one is faster anyway, and uses optimization

thank's