Xbasic limit for Query.filter

[Garry Flanigan]

I have a script with 4 nested while loops.

In each loop I use a Query.filter to find records for further processing. However, this failed to run to completion. I fixed it by eliminating the innermost Query.filter and stepped through all records using an if statement to decide whether or not to process and it ran to completion.

I concluded there might be a limit to the number of times Query.filter can be used or nested in a script.

Does anyone have any ideas?

[Stan Mathews]

RE: Xbasic limit for Query.filter

There is a maximum for index or query key length which may be exceeded by your multiple queries


Knowing the key length of an index or query key can be important because Alpha Five only supports key lengths of 100 characters of less. If you use a key length of more than 100 characters, your data may not appear to sort correctly because Alpha Five ignores key values past the first 100 characters.

[Tom Cone Jr]

RE: Xbasic limit for Query.filter

What happened when "it failed to run to completion" ?

-- tom

[Garry Flanigan]

RE: Xbasic limit for Query.filter

Tom,
see sample code below:
t0 = table.open("table0")
t1 = table.open("table1")
t2 = table.open("table2")
t0.fetch_first()
while .not. t0.fetch_eof()
expression = "field1='" + t0.field1 + "'"
query.filter = expression
i1 = t1.query_create()
t1.fetch_first()
while .not. t1.fetch_eof()
expression = "field1='" + t1.field1 + "'"
query.filter = expression
i2 = t2.query_create()
t2.fetch_first()
while .not. t2.fetch_eof()
'
' do things
'
t2.fetch_next()
end while
t1.fetch_next()
end while
t0.fetch_next()
end while
t0.close()
t1.close()
t2.close()


All table are related.
In this case, the inner loop only executes for the first 14 records.
However, if I comment out the query.filter and query.create it loops as expected.
Any help will be appreciated.

[Tom Cone Jr]

RE: Xbasic limit for Query.filter

I think you're running out of the 16 available query slots for the table being filtered over and over. You can arrange to detach queries when they're not needed. Each "detachment" frees up a slot. Possibly a better solution is to use "temporary queries" option. Check the "T" option in the discussion of "tbl".query_create().

BTW, there's a good chance your script will be relatively slow. I'd prefer the faster "tbl".fetch_find() to do a find by key after setting the primary index for the table to match. Then I'd loop through the table one record after the other until the key no longer matches or eof() is encountered. Will be faster. I would never nest queries such as you are doing. I wouldn't want to generate all the network traffic a table wide query might entail, only to repeat it over and over, if you see what I mean.

-- tom

[Garry Flanigan]

RE: Xbasic limit for Query.filter

Tom,
1. The "T" option worked.
2. I will look into the next option.
How do I loop?
Is it something like :

t1.index_primary_put("indx1")
t1.fetch_first()
while .not. t1.fetch_eof()
rec_no t1.fetch_find("x")
if rec_no " 0 then
'
' do things
'
end if
t1.fetch_next()
end while

Thanks in anticipation of your reply!

[Tom Cone Jr]

RE: Xbasic limit for Query.filter

I'm glad the "T" option worked. It's one of the relatively recent improvements Alpha has made and I don't have much experience with it.

As for "looping" through the sorted records I was thinking of this:

Assume the goal is to process all the records in a child table that have a Customer_id field value of "01001". In your original approach you queried the table and Alpha returned a "result subset" containing only the matching records, which you step through one at a time. I was suggesting that the table NOT be queried at all. Instead the sequence would be something like this:

1) set an INDEX on the Customer_id field primary in the child table. This presents the records in order based on the Customer_id field values in each record. If you browsed the table top to bottom you'd see that all the '01001' records are grouped together. All the '01002' records are grouped together. All the '01003' records are grouped together and so on.

2) do a find by key to locate the first record with a Customer_id field value of '01001'.

3) use a WHILE ... END WHILE loop to process all '01001' records, one at a time. To move from one record to the next you do NOT have to do a find by key each time. Remember the records are already sorted for you. You merely have to fetch the next record each time.

4) eventually when you fetch the next record the Customer_id field value will no longer match '01001' so your script should stop processing. At that point you'd determine the value of the "next" Customer_id you wanted to process by moving the record pointer in the Parent table. With the "new" Customer_id field value in hand you'd loop back to step 2 and process all of those records. Again you'd continue until the "next" Customer_id field value doesn't match or you hit the eof.

Here's the inner loop for this sequence expressed in code. Can you recognize the steps?

t1.index_primary_put("Cust_id")    't1 is the 1:N child
vc_Curr_Key = t0.Customer_id     't0 is the parent

recnum = t1.fetch_find(vc_Curr_Key)

if recnum > 0 then    'the find by key succeeded
   while t1.Customer_id = var->vc_Curr_key .and. .NOT. t1.fetch_eof()
       ' process the record
       ' finish processing the record
       t1.fetch_next()  'get the next child record
   end while    'no more t1 records to process
end if

At this point either you've hit the eof() and can stop or vc_Curr_Key holds a different value. So your script would then loop back, using an outer loop not presented here, to retrieve the next Customer_id you needed to process.

Hope this helps.

-- tom

[Garry Flanigan]

RE: Xbasic limit for Query.filter

Tom,
That worked!!
I certainly appreciate your help.
With thanks,

Garry