filter expression is not logical

[Anis]

I keep getting a msg that says : query filter expression is not logical after the tbl.query_create() line.
is something wrong with this code ?

Thanks
dim global tick as N
tbl = table.open("tracking")
tick=VAL(ui_get_number("FUJITICKETNUMBER","Enter Ticket Number","0","00000000"))
query.description="ticket"
query.filter = "TRACKING->FUJITICKETNUMBER=VAR->tick"
query.option="I"
tbl.query_create()
:Form.load("main")

[BRochford]

RE: filter expression is not logical

Suggest you go to Learnalpha.com and **PRINT** the following 2 articles (both articles and the numerous examples included have saved me numerous hours of agony setting up queries).

"Query Filters Alternative...." by Tom Cone
"Mysteries of Query & Lookup Syntax" by Dr. Wayne

Just looking at your example I would suggest something like this:
'"+var->tick+"'. Remember the entire expression must evaluate to characters.
-Barry

[csda1]

RE: filter expression is not logical

Anis,

I'm 1st going to assume TRACKING->FUJITICKETNUMBER is a numeric field. Assuming it is, you want to compare it to another number, as you tried to. You even defined "tick" as a global numeric, which was a correct attempt. However, the query operation does not know about global variables (although it probably should). So the correct method would be


tbl = table.open("tracking")
tick=VAL(ui_get_number("FUJITICKETNUMBER","Enter Ticket Number","0","00000000"))
query.description="ticket"
query.filter = "TRACKING->FUJITICKETNUMBER="+LTRIM(STR(tick))
query.option="I"
tbl.query_create()
:Form.load("main")

Regards,

Ira J. Perlow
Computer Systems Design & Associates
[email protected]

[Anis]

RE: filter expression is not logical

I just tried your code Ira and I got the same message. Filter expression is not logical.
You're right; the ticketnumber field is numeric field and so tick.

[robert bannerman]

RE: filter expression is not logical

While it may not be of much help, I just went through the same process this weekend. I used the interactive window to trace the values that the filter expression evaluates to to come up with the syntax that works. The article by Tom Cone is an excellent place to look for query syntax.

Finally, you should turn on the recorder window, and record the expression that a manual query using the values that you want looks like. I then pasted the information into my script, and ... problems solved (mostly) :?) Robert.

[Anis]

RE: filter expression is not logical

That's exactly what I did, I recorded the expression and copied it to the Button. If you look at the script, nothing seems to be out of order, other scripts work exactly the same way.

[JohnZaleski]

RE: filter expression is not logical

Below is a script that is on a button on a form that resembles yours. This code works. I really think yours should work also unless there is different behavior. Try
tb1.FUJITICKETNUMBER = var->tick
John


'rem find unpaid balance

DIM CONTENTS as C
DIM SHARED CLIMAN as C
DIM SHARED NEWAMOUNT as N

TB1 = TABLE.CURRENT()
TB1.index_primary_put("CLIENT_N1")

CLIMAN = ui_get_number("Enter invoice amount","Enter Amount")
NEWAMOUNT = VAL(CLIMAN)

if NEWAMOUNT = 0 then
goto thatsall
end if
CONTENTS = "Unpaid Balance Search"
query.description = CONTENTS
query.order = "clinv->CLIENT_NO"
query.filter = "clinv->BALANCE = var->NEWAMOUNT"
query.options = "I"
TB1.query_create()

thatsall:
end

[Anis]

RE: filter expression is not logical

Thanks for all the help.
Finally found it. the line:query.filter = "TRACKING->FUJITICKETNUMBER="+LTRIM(STR(tick))
should have "tracking" in lower case.

[JohnZaleski]

RE: filter expression is not logical

Ira,
I'm a little confused. I understand why yours worked, but take a look at the script above (my message). This script works, I ran it before I posted it. The two scripts are nearly identical. The query in my case does seem to recognize the variable and I have instances of queries with variables in a number of places. I wonder if there was something conflicting with the "fieldname" or the "variable name" (index,calc field or other) that caused this particular query not to work rather than a query's inability to deal with variables. Or is something going on in my script that I am not seeing.
John

[csda1]

RE: filter expression is not logical

John,

Your method should work with the VAR inside the quotes, but since Anis was having problems, I suggested not having it evaluated in the expression, which may have potentially been the problem. Nevertheless, as I'll try to explain, my method should be the way to always do it.

For the purposes of the query filter expression, the VAR value is a constant. In other words, it does not change between 1 record and the next as the query process takes place. Placing the VAR as a name forces Alpha 5 to evaluate the constant for every record. Let's use my example here (which uses character values);

dim global varname as C
varname="abc"
query.filter="charfieldname=trim(var->varname)"

For each record A5 must get the value of varname, trim it, then compare it to charfieldname.

Now suppose we do it my way

varname="abc "
query.filter="charfieldname='"+trim(var->varname)+"'"

In this 2nd case, the query.filter evaluates to

query.filter="charfieldname='abc'"

There are several advantages to the 2nd method.

1. No use of a global variable
2. The final expression is simpler, meaning the query will be significantly faster
3. Alpha 5 potentially could apply LQO (Lightning Query Optimization) if the charfieldname was indexed.

Since variables will always be constant (is that an oxymoron???) in the context of the expression of the query, there is nothing gained by putting in the variable name.

There is one exception possibly, but might be very version specific. A constant (like "abc") might take longer to access than a variable that had been set to that value. In that case, using the variable in place of the constant might be slightly faster, but that would definitely preclude potential use of LQO. The amount to be gained in this case is probably extremely small.

Regards,

Ira J. Perlow
Computer Systems Design & Associates
[email protected]

[Stephen Williams]

RE: filter expression is not logical

Thanks, Ira! Put this in the book!

[csda1]

RE: filter expression is not logical

To All,

With a little e-mail prompting with John, here is some cool stuff I found to add to the above.

Apparently, when you run the query, the query.filter has it var->variablename changed to a constant text string if it is character type. If it it is logical type, it is untouched. Thus (for the character case) if the filter is

varname="abc"
query.filter="fieldname=trim(var->varname)"

after the query it becomes
query.filter="fieldname=trim("abc")"

implying that A5 is smart enough to at least substitute the constant.

However, testing also indicated that there is a small amount of overhead associated with substituting the constant.

The reason I placed trim() in the test is to illustrate the important difference so that LQO would and would not take place based upon methods.

So to repeat and reiterate,

1. No use of a global variable (global variables should only be used when absolutely required)
2. The final expression is simpler, meaning the query will be significantly faster
- In the trivial case this has little importance. However if the part of the expression "trim(var->varname)" were alot more complex (but still evaluating to a constant), then it could make a big difference.
3. Alpha 5 potentially could apply LQO (Lightning Query Optimization) if the fieldname was indexed and the expression were acceptable, e.g.

query.filter="between(fieldname,"+chr(34)+trim(var->varname)+chr(34)+","+chr(34)+trim(var->varname)+chr(34)+")"

With LQO in a test case with 35000 records, it took .44 sec instead of 4.34 seconds.

Those wishing to try some variations, here is some code I created. Change the code as indicated to reflect your database table and indexed field. View the results in the trace window.


' Change the tablename, char comparison value and
' comparison field below (Search for CARID below and change. This field is indexed)
Tablename="datatabl"
dim global chr as C
char="FEDE"

'-------------------------

dim global filt as L
dim global filt1 as L
filt=.T.
filt1=.T.

query.description = "Query Speed Test"
query.order=""
query.options="I"

query.filter=".T."
tbl=table.open(Tablename)
trace.writeln("Before Query.Filter="+chr(34)+query.filter+chr(34))
t1=now()
ndx=Tbl.query_create()
t2=now()
trace.writeln("After Query.Filter="+chr(34)+query.filter+chr(34))
reccnt=ndx.records_get()
ndx.drop()
tbl.close()
trace.writeln("Query.Filter="+chr(34)+query.filter+chr(34)+" Rec Count="+LTRIM(STR(reccnt))+"
Time="+LTRIM(STR(t2-t1,15,2)))
trace.writeln("")

query.filter="var->filt"
tbl=table.open(Tablename)
trace.writeln("Before Query.Filter="+chr(34)+query.filter+chr(34))
t1=now()
ndx=Tbl.query_create()
t2=now()
trace.writeln("After Query.Filter="+chr(34)+query.filter+chr(34))
reccnt=ndx.records_get()
ndx.drop()
tbl.close()
trace.writeln("Query.Filter="+chr(34)+query.filter+chr(34)+" Rec Count="+LTRIM(STR(reccnt))+"
Time="+LTRIM(STR(t2-t1,15,2)))
trace.writeln("")

query.filter="var->filt1.and..T."
tbl=table.open(Tablename)
trace.writeln("Before Query.Filter="+chr(34)+query.filter+chr(34))
t1=now()
ndx=Tbl.query_create()
t2=now()
trace.writeln("After Query.Filter="+chr(34)+query.filter+chr(34))
reccnt=ndx.records_get()
ndx.drop()
tbl.close()
trace.writeln("Query.Filter="+chr(34)+query.filter+chr(34)+" Rec Count="+LTRIM(STR(reccnt))+"
Time="+LTRIM(STR(t2-t1,15,2)))
trace.writeln("")

query.filter="var->filt1.and.var->filt"
tbl=table.open(Tablename)
trace.writeln("Before Query.Filter="+chr(34)+query.filter+chr(34))
t1=now()
ndx=Tbl.query_create()
t2=now()
trace.writeln("After Query.Filter="+chr(34)+query.filter+chr(34))
reccnt=ndx.records_get()
ndx.drop()
tbl.close()
trace.writeln("Query.Filter="+chr(34)+query.filter+chr(34)+" Rec Count="+LTRIM(STR(reccnt))+"
Time="+LTRIM(STR(t2-t1,15,2)))
trace.writeln("")

query.filter="Carid==trim(var->char)"
tbl=table.open(Tablename)
trace.writeln("Before Query.Filter="+chr(34)+query.filter+chr(34))
t1=now()
ndx=Tbl.query_create()
t2=now()
trace.writeln("After Query.Filter="+chr(34)+query.filter+chr(34))
reccnt=ndx.records_get()
ndx.drop()
tbl.close()
trace.writeln("Query.Filter="+chr(34)+query.filter+chr(34)+" Rec Count="+LTRIM(STR(reccnt))+"
Time="+LTRIM(STR(t2-t1,15,2)))
trace.writeln("")

query.filter="Carid==trim(var->char)"
tbl=table.open(Tablename)
trace.writeln("Before Query.Filter="+chr(34)+query.filter+chr(34))
t1=now()
ndx=Tbl.query_create()
t2=now()
trace.writeln("After Query.Filter="+chr(34)+query.filter+chr(34))
reccnt=ndx.records_get()
ndx.drop()
tbl.close()
trace.writeln("Query.Filter="+chr(34)+query.filter+chr(34)+" Rec Count="+LTRIM(STR(reccnt))+"
Time="+LTRIM(STR(t2-t1,15,2)))
trace.writeln("")

'query.filter="Carid=="+chr(34)+trim(char)+chr(34)
query.filter="between(Carid,"+chr(34)+trim(char)+chr(34)+","+chr(34)+trim(char)+chr(34)+")"
tbl=table.open(Tablename)
trace.writeln("Before Query.Filter="+chr(34)+query.filter+chr(34))
t1=now()
ndx=Tbl.query_create()
t2=now()
trace.writeln("After Query.Filter="+chr(34)+query.filter+chr(34))
reccnt=ndx.records_get()
ndx.drop()
tbl.close()
trace.writeln("Query.Filter="+chr(34)+query.filter+chr(34)+" Rec Count="+LTRIM(STR(reccnt))+"
Time="+LTRIM(STR(t2-t1,15,2)))
trace.writeln("")

query.filter="between(Carid,trim(var->char),trim(var->char))"
tbl=table.open(Tablename)
trace.writeln("Before Query.Filter="+chr(34)+query.filter+chr(34))
t1=now()
ndx=Tbl.query_create()
t2=now()
trace.writeln("After Query.Filter="+chr(34)+query.filter+chr(34))
reccnt=ndx.records_get()
ndx.drop()
tbl.close()
trace.writeln("Query.Filter="+chr(34)+query.filter+chr(34)+" Rec Count="+LTRIM(STR(reccnt))+"
Time="+LTRIM(STR(t2-t1,15,2)))
trace.writeln("")

query.filter="between(Carid,var->char,var->char)"
tbl=table.open(Tablename)
trace.writeln("Before Query.Filter="+chr(34)+query.filter+chr(34))
t1=now()
ndx=Tbl.query_create()
t2=now()
trace.writeln("After Query.Filter="+chr(34)+query.filter+chr(34))
reccnt=ndx.records_get()
ndx.drop()
tbl.close()
trace.writeln("Query.Filter="+chr(34)+query.filter+chr(34)+" Rec Count="+LTRIM(STR(reccnt))+"
Time="+LTRIM(STR(t2-t1,15,2)))
trace.writeln("")

END


Regards,

Ira J. Perlow
Computer Systems Design & Associates
[email protected]

[JerryBrightbill]

RE: filter expression is not logical

Ira,
Fascinating lesson on the proper use of expressions in a query. I have always struggled with the proper syntax when using a variable in the filter expression. After looking at your examples and playing with expression examples and sending the results to the trace window, the light finally came on. The concept that the filter expression result is just a line of text just didn't seem right. Fantastic and thanks for the clear explanation!

Jerry

[JohnZaleski]

RE: filter expression is not logical

Ira,

I agree with Jerry. That little exercise was very interesting.

John