WITH

WITH

Top  Previous  Next

 

The WITH keyword introduces a selection clause.

 

 

Format

 

WITH {EVERY} condition {rel.op {EVERY} condition...}

 

where

 

condition isfield1 operator field2        to compare two fields
orfield1 operator value        to compare a field with a literal value

 

rel.op isAND or OR

 

operator isany of the query processor operators:

EQ

=

EQUAL

 

 

NE

#

NOT

<>

><

LT

<

LESS

BEFORE

 

LE

<=

=<

 

 

GT

>

GREATER

AFTER

 

GE

>=

=>

 

 

LIKE

MATCHES

MATCHING

 

 

UNLIKE

NOT.MATCHING

 

 

 

SAID

SPOKEN

~

 

 

NO

 

 

 

 

BETWEEN

 

 

 

 

 

 

A selection clause specifies criteria governing which records are processed by the command. If omitted, all records are processed.

 

The relational operators may be followed by the keyword NO.CASE to apply a case insensitive comparison. This also occurs if the QUERY.NO.CASE mode of the OPTION command is in effect.

 

The EVERY keyword indicates that every value or subvalue of field1 must match field2 in the manner defined by the operator. For example, the command

LIST EXAM.RESULTS STUDENTS SUBJECTS WITH EVERY GRADE = "A"

might be used to report a list of students achieving grade A in every examination. The SUBJECTS and GRADE fields in this example are a pair of associated multivalued fields recording examination subjects and grades.

 

 

Testing for Empty Fields

 

Use of the WITH clause with a field name but no operator or second field name tests whether the field is not empty. This allows queries such as

LIST SALES WITH PAYMENT

or

LIST SALES WITH NO PAYMENT

The second of these examples can also be written as

LIST SALES WITHOUT PAYMENT

 

Note that the implementation of this construct is inconsistent across different multivalue products if the field is multivalued. In QM, the second of the examples above shows only records in which all values in the PAYMENT field are empty.

 

 

Complex Conditions

 

The AND and OR operators may be used to build complex conditions. For example,

LIST STOCK WITH QTY < REORDER AND SUPPLIER = 26

selects only those records where the content of the QTY field is less than the content of the REORDER field and the SUPPLIER field contains the value 26.

 

The AND and OR operators are of equal priority and, if both appear in a single WITH clause, are evaluated left to right. Brackets may be used to modify the evaluation sequence. For example,

LIST STOCK WITH QTY < REORDER AND (SUPPLIER = 26 OR WAREHOUSE = 14)

 

Multiple WITH Clauses

 

A query may contain more than one WITH clause. There is normally an implied AND relationship between these clauses. Thus the command

LIST STOCK WITH QTY < REORDER WITH SUPPLIER = 26

is identical in effect to

LIST STOCK WITH QTY < REORDER AND SUPPLIER = 26

The WITH.IMPLIES.OR mode of the OPTION command changes the effect of multiple WITH clauses to have an implied OR between the clauses so that the above query with two WITH clauses is equivalent to.

LIST STOCK WITH QTY < REORDER OR SUPPLIER = 26

 

Fields with Conversion Codes

 

Field comparisons are performed using the internal format of field1, converting the field2 or value item to its internal form if a conversion code is present. Thus a field holding an internal date, for example, may be compared with the more natural external form of the date. For example,

LIST INVOICES WITH ISSUE.DATE > "12 OCT 96"

will list all invoice records with an issue date after 12 October 1996.

 

This leads to a potential problem if the conversion code is not "reversible", where data converted from internal form to external form cannot be converted back again. As an example, a date conversion that returns just the month in which the date lies is not reversible. An internal date of 15171 (14 July 2009) would convert to "July" but this cannot be converted back to the original date.

 

The date conversions are defined such that a date with no year is assumed to be in the current year and a date with no day number is assumed to refer to the first day of the month. Thus a query of the form

LIST SALES WITH MONTH = "July"

is actually asking for orders placed on the first of July in the current year.

 

 

Short forms

 

The query processor offers a variety of short forms for selection clause elements.

 

 

Implicit field names

If two or more tests are to be performed against the same field, the field name only needs to appear in the first test. A relational operator without a preceding field name uses the same field as in the previous operator or the record id if this is the first relational operator.

 

In each of the following examples, the second query is an abbreviated form of the first

 

LIST VOC WITH TYPE = F OR TYPE = Q

LIST VOC WITH TYPE = F OR = Q

 

LIST ORDERS WITH DATE AFTER '31 DEC 99' AND DATE BEFORE '1 JAN 01'

LIST ORDERS WITH DATE AFTER '31 DEC 99' AND BEFORE '1 JAN 01'

 

LIST SALES WITH @ID > 10000

LIST SALES > 10000

 

 

Implicit OR relation

A relational operator followed by a series of values tests each of the values against the given field in an implicit OR relationship.

 

LIST ORDERS WITH REGION = 'SOUTH' OR REGION = 'NORTH'

LIST ORDERS WITH REGION = 'SOUTH' 'NORTH'