Mysteries of Query and Lookup Syntax

by Dr. Peter Wayne

Many Alpha Five users are unsure how to create proper lookup and query expressions. There are 4 places that filter expressions are used in Alpha Five programming:

1. in a query.filter expression
2. in a form's filter_expression
3. in a lookup() function
4. in a tablexxx() function

All filter expressions are applied to a table, and you can filter Alpha Five tables on a character, numeric, date, or logical field.

All filter expressions are character expressions

Every filter expression must evaluate to a strict character expression. I'll illustrate in these examples by writing some filters for tables in the Invoice.adb that comes in your /samples/Invoice/ folder with Alpha Five. Open the Invoice application, then close it and get to the Control Panel. Then click on View, Code Editor:

Fig. 1. Click on View, Code Editor

Next, click on the Interactive tab of the Code Editor. This opens the Interactive window in which you can type Xbasic statements for immediate execution:

Fig. 2. The Interactive Window

Let's write a query that will select all customers whose last name begins with "P". We start in the Interactive window by opening a table pointer to the customer table, and then we try to construct a query filter along the lines of left(lastname,1)="P". Our first attempt at typing this query filter in the Interactive window results in an error message:

cust=table.open("customer")
query.filter="left(lastname,1)="P""
ERROR: Extra characters at end of expression

Fig. 3. Output of Interactive window

Alpha Five can't handle the nested quotation marks in the query.filter expression. We can make our intent intelligible to Alpha Five in one of two ways:

• we can use the escape character of \ to tell Alpha Five that the next quotation mark is not the end of an expression but is a nested quote, or
• we can use single quotation marks nested inside the double quotation marks

The two approaches are equivalent, as far as Alpha Five is concerned. For the first approach we write
query.filter="left(lastname,1)=\"P\""
and for the second, we write
query.filter="left(lastname,1)='P'"

Most people find the second approach easier to follow, and you will see that used most often. Let's finish the query in the interactive window and test its result:

Fig. 3. The properly constructed query works correctly.

Using variables in a query

So far we constructed a query filter using the literal character of "P". What if we want to use a variable, first_letter, that stores the value of the first letter of interest? There are several ways to construct a query with a variable in Alpha Five. We'll start with the most straightforward and easiest to understand, by writing

dim first_letter as c
first_letter="G"
query.filter="left(lastname,1)=var->first_letter"
cust.query_create()
? cust.lastname
= "Graham              "

Using the var-> syntax to reference a variable in a query.

Alpha Five's query_create() method is smart enough to substitute the value of the first_letter variable into the query.filter expression at the time that the query is created. We can verify this by checking the value of query.filter just before and just after query_create() has taken place:

Fig. 4. Query_create() forces an evaluation of query.filter

Before the query actually executes with cust.query_create(), the value of query.filter is still the same literal characters we typed in, namely, left(lastname,1)=var->first_letter. But once we executed the query with cust.query_create(), variable substitution is forced and the query.filter takes on the value of left(lastname,1)=\"G\". This should remind you of the very first approach we took to writing query filters, using the escape character.

There are other ways to write the same query. The query_create() method is smart enough to recognize a variable when it sees one, even without an explicit var-> prefix. A second way to write the query filter is this code, copied from the Interactive window:

first_letter="P"
query.filter="left(lastname,1)=first_letter"
? query.filter
= "left(lastname,1)=first_letter"
cust.query_create()
? query.filter
= "left(lastname,1)=\"P\""

Once again, the query_create() method forces evaluation of the filter.

A third and final way to write the code is for you, as the Xbasic programmer, to explicitly construct the query filter instead of asking query_create() to do the work:

first_letter="R"
query.filter="left(lastname,1)='" + first_letter + "'"
? query.filter
= "left(lastname,1)='R'"
cust.query_create()
? query.filter
= "left(lastname,1)='R'"

Notice that we are not relying on the query_create() method to do any of the translating. The filter expression does not change as the query is created, because we have constructed a completely valid filter expression without the use of any variables.

So why bother?

If Alpha Five is so successful at translating query filters, why should we ever construct explicit filters ourselves? The answer is simple: Alpha Five is not universally capable of translating all your filter expressions. Although the query_create() method is pretty smart about simple variables, it fails entirely with more complicated expressions. Let's try to look up a value in the invoice_header table based on a field in the customer table:

hdr=table.open("invoice_header")
query.filter="customer_id=cust.customer_id"
? query.filter
= "customer_id=cust.customer_id"
hdr.query_create()
ERROR: no such field

The query_create() method was completely unable to make the substitution for the value of cust.customer_id. However, with what we now know, we can force the evaluation ourselves:

query.filter="lastname='Mesner'"   ' find a customer
cust.query_create()
? cust.customer_id
= "00000004"
' now switch to invoice_header table
query.filter="customer_id='" + cust.customer_id + "'"
? query.filter
= "customer_id='00000004'"
hdr.query_create()
? hdr.customer_id
= "00000004"

The explicitly constructed query works properly on the invoice_header table.

Querying by other field types

So far, we've only shown queries on character fields. What we learned above generalizes to the other field types as well. For example, let's construct a query on the date field of invoice_header:

date1={1/1/95}
date2={12/1/95}
query.filter="between(date,date1,date2)"
? query.filter
= "between(date,date1,date2)"
hdr.query_create()
? query.filter
= "between(date,{01/01/1995},{12/01/1995})"

We see again that for a simple variable, or even 2 variables in this example, the query_create() method evaluates the variables properly. The explicitly constructed query filter is more awkward to write:

date1={1/1/95}
date2={12/31/95}
query.filter="between(date,{"+dtoc(date1)+"},{"+dtoc(date2)+"})"
? query.filter
= "between(date,{01/01/1995},{12/31/1995})"

Since query filters are always character expressions, the date fields have to be converted into characters to construct the query filter. This certainly is a little extra work, but remember, if you want to construct a query filter containing anything more than simple variables, you will have to force the filter evaluation yourself: Alpha Five won't be able to do it for you.

What about the other filter expressions?

The other functions that use filter expressions are not as smart as query_create(). For example, the lookup() function interprets simple variables only if they are preceeded by the var-> prefix:

first_letter="P"
? lookup("customer","left(lastname,1)=first_letter","lastname")
= <No data returned>
? lookup("customer","left(lastname,1)=var->first_letter","lastname")
= "Peabody             "

As before, if we do the explicit evaluation ourselves we always can get the desired result:

first_letter="G"
? lookup("customer","left(lastname,1)='"+first_letter+"'","lastname")
= "Graham              "

The same syntax can be applied to a form's filter_expression for a base table:

first_letter="G"
f=form.load("Customer Information")
' check the filter_expression now - it should be blank
? f:Tables:customer.filter_expression
= ""
f:Tables:customer.filter_expression="left(lastname,1)='"+first_letter+"'"
f.query()
' let's check the filter_expression again
? f:Tables:customer.filter_expression  
= "left(lastname,1)='G'"
' good - let's verify that it works
f.resynch()
? f:Tables:customer.lastname
= "Graham              "

This example applies a filter expression to the Customer Information form.

A magician, it ain't

The moral of this article is that although Alpha Five makes a commendable effort to interpret your filter expressions, it can't read your mind. There are lots of places in which filter expressions are used in Alpha Five. Sometimes Alpha Five will be able to interpret simple variables, but other times it will be stymied unless you explicitly construct the filter expression. Since your goal as an Alpha Five programmer is to make sure your programs work all the time, I recommend you don't try the short cuts but place the extra quotes yourself in all your filter expressions. Then you don't have to worry whether your expression is too complex for Alpha Five to interpret.

5/1/99

Don't forget, we need your feedback to make this site better!

Return to home