Using QMF

Writing expressions

You can write expressions in conditions by using any of the following symbols:

Condition

QBE Keyword

Equal

=

Not equal

¬=

Greater than

>

Greater than or equal

>=

Less than

<

Less than or equal

<=

Multiple conditions

AND, OR

Values within a range

BETWEEN

Values from a list

IN (x, y, z)

A certain string of characters

LIKE '%abc%'

Ignore certain characters

LIKE '_abc_'

Negative conditions

NOT

See Appendix A, Query-by-Example for descriptions of these keywords.

Note:

The QBE language does not recognize the following:

• Concatenation (||) operator
• Not-greater-than (¬>)
• Not-less-than (¬<)
• Not-equal-to (<>

If you use one of these operators, QMF displays an error message.

Order of evaluation:

1. Built-in column functions
2. A plus sign or minus sign before a single value

3. Multiplication or division of two values

4. Addition or subtraction of two values

QMF evaluates operations at the same level of precedence from left to right.

You can change the order of evaluation with parentheses just as you would use them in a mathematical formula. For example, the following two expressions are equivalent:

A * - B / C + D / E       ((A*(-B))/C) + (D/E)

When you create a table, each column in it holds a certain type of data.

QMF performs arithmetic operations only on numeric data types.

Rules for quotation marks

Do not enclose numeric data in quotation marks.

You need to enclose character data that is used in conditions in quotation marks only when:

• The data contains blanks (as in 'ROOM 27'), or any characters besides digits, letters, #, $, or @, (as in 'P.D.Q.', 'BOW-WOW').
• The data contains a single quotation mark or apostrophe. (In this case you have to double the quotation mark inside the data, as in 'O''BRIEN').
• To distinguish the constants 'NULL' and 'USER' from the keywords NULL and USER.
• The data contains all double-byte characters.
• Character data that is entirely digits, as in '849276552'.
• The data type is DATE, TIME, or TIMESTAMP.

Do NOT enclose values to compare with columns of numeric data in quotation marks.

Arithmetic overflow

When an operation in a query would produce a result outside an allowable range, the situation is called "arithmetic overflow". It is possible for the result of an arithmetic operation to be outside the range allowed for the data type of the result. For example, 1000000 is an allowable value in a column with data type INTEGER, but 1000000 * 1000000 cannot have the data type INTEGER. Also, division of any number by 0 produces an overflow.