Query options??

[Tbaker]

Re: Query options??

Peter

Just starting looking over your code. Seems to be missing an operation "DeleteZerioPreCharge". Is this needed to get a good code run?

One of the things that I did initially was to break your filters apart and add the s_quote() function to wrap the variable vg_current_property_name in quotes. When running the option T, with the unquoted filter, no records were found, when runing it with the quoted vg_current_property_name, it returns the proper 1 record found.

Wait for your answer.

Tom Baker

[peterg000]

Re: Query options??

Tom .... no, you dont need operation to test. Without the changes you mention, if i run with debug, and check the query count, it does find records for the query. Run debug .... put a break at "if between(postdate,poststartdate,postenddate)" ... then step through the loop. Watch 'tblb.unitweek' and watch the query pointer 'qryb' as soon as the unitweek hits '01507' (seven records into the loop), you see the query pointer get dropped.

I am going to play around with the filter per your test to see if this changes, but still the question - why would the 'qryb' pointer get dropped? and if you notice as you step through, it gets dropped on the execution of the lookup() ... wierd.

Glenn

[peterg000]

Re: Query options??

Tom,
I changes all my filters in the gencharge script to use quote() around global var 'vg_current_property_name' ... I get the same exact thing. If you get a chance, step through debug per my previous post and I think you will see whats happening.

Thanks again for checking it out ....

Glenn

[Tbaker]

Re: Query options??

Peter

I have been working on and off for several hours on your test app.

I have gone over every line of code and cannot find anything wrong other than the fact that in this scenario Alpha will not allow the "T" option. When using the "T" query.option, the query drops around the 8th record in the TSM_unitalloc table. With any other option (including "" - no option) the code goes to finish and the qryb query is not dropped until qryb.drop() is used.

The only thing that I can say, it that the T option produces a query that is temporary and can be overwritten if another slot is needed and I suppose Alpha needs to use the slot after 8 iterations and therefore drops the query.

I would say that this is a bug but I tried this in version 9 and it produced the same dropped query if the T option was used.

Since you already have a test app, you might consider sending this in as a bug and see what happens that is unless someone else can see something that both you and I can not.

Wish I had better news but the workaround is just using query.option="" if you do not want to use an index.

I tried you app in Version 8 but there must some new things using V10 on the form and such so it would not load. So I can't say whether it was the same in Version 8.

Tom Baker

[peterg000]

Re: Query options??

Tom .... thanks so much for checking this out .... and thanks to the others who have commented on best practices for using query dot variables. I'm not going to turn in a bug, because I cant really say the 'T' option is not doing what it is supposed to ... just wish I new why it would drop the query after 8 cycles through loop and why its the lookup() that actually causes the drop .... there are supposed to be 16 slots available .... but being confused about the number of slots is not enough for a bug report. Originally, I didn't even specify a query.options in my first version of the script ... probably picked up the 'T' option from another script ... as other have said, I need to make sure my options are cleared or explicitly setup ... will try and practice this in the future.

Thanks again ...

Glenn

[Tbaker]

Re: Query options??

Glenn

I was looking at your code again. The way your script is written the TSM_unilalloc table is open when you are doing the lookups so if you change the script at line 229 from this

if vl_uflag = .t.
				
	uactive = lookup("tsm_unitalloc","Ua_property_name = "+quote(var->vg_current_property_name)+" .and. "+"Unitweek = "+quote(tblb.Unitweek),"Ua_active_flag")
	if uactive=.f. then
	    goto next_unitweek
	end if
end if

to this

if vl_uflag = .t.
'since the tsm_unitalloc table is already open you do not need to look up
'the ua_active_flag use the table directly
				
	    if tblb.Ua_active_flag = .f. then
	           goto next_unitweek
	    end if
end if

It will cut out the lookups and the code will run much faster in posting the transactions. It even runs with this coding with the "T" option - so that tells me that running that lookup is the culprit in having the query dropped after 8 iterations but still don't know why.

At least some of the mystery is gone.

Tom Baker

[Tbaker]

Re: Query options??

Glenn

You can disregard my prior post. I had looked at the lookup() on the TSM_unitalloc table and misread the second part of the filter "unitweek =" quote(tblb.unitweek)

Without that filter, the app updates the wrong number of records.

But we know now that the culprit was the lookup() just don't know why.

Tom Baker

[martinwcole]

Re: Query options??

I recently ran into the same issue when converting an app from V8 to V10 with lookup()

It may be worth reporting as a bug if you can produce a simple example for Alpha.

[Tom Cone Jr]

Re: Query options??

The lookup() function opens a new instance of the table, and runs its own query each time it's called. This can be pretty slow. Often a better solution is to open the table once, set the desired index, and then do a series of fetch_find()'s (so to speak) to retrieve the desired records. This will perform much faster and does not involve running repeated queries.

[peterg000]

Re: Query options??

So Tom(Cone) ..... your saying that the lookup() uses a query slot?

[peterg000]

Re: Query options??

TomB .... thanks for the heads up on the lookup() and whether to take out or not.

Glenn

[peterg000]

Re: Query options??

Martin .... can you describe the issue you had. The problem I'm having with reporting as a bug is .... if the lookup() is supposed to use a query slot, then I would understand why slots would get used up and I would understand why my query would get dropped after the slots got used up if I used the option 'T'.

The question still out there ..... why does query.options = "" work?? Wouldnt the query eventually get dropped anyway ... I would think all slots would eventually be used up by the recursive lookup() within the loop .... it definitely loops through more than 16 times.

???????

Glenn

[Tom Cone Jr]

Re: Query options??

So Tom(Cone) ..... your saying that the lookup() uses a query slot?

I don't know. It seems likely. I've never bumped into this because when I discover I need to loop through a sequence that uses the slower LOOKUP() function I always implement the much faster fetch_find() sequence.

[peterg000]

Re: Query options??

Tome(Cone) .... I hear ya ... but a good understanding of the query process, when it gets used(user or alpha), number of slots, and the different options only helps when deciding which program path to take. Lookup() may be slow in certain cases, but in cases where it might not matter, its very convenient. I guess I just like to know how things tick. I guess this is just another one of those .... "if it hurts doing that ... dont do that" ... kinda things ...:)

[Tom Cone Jr]

Re: Query options??

To each his own.

Lookup() will always be slower. So I rarely use it, and never inside a loop where it will be called over and over.