field-name<(occurrence)> | key-name operator value field-name* operator field-name* field-name<(occurrence)> | key-name range-operator low-value * high-value
field-name<(occurrence)>
| key-namewhere address(3) contains dallas selects
those records where the third occurrence of ADDRESS contains DALLAS.
where
address(all) contains dallaswhere
address(any) contains dallas selects those records where
any occurrence of ADDRESS contains DALLAS. You could have used ADDRESS(*)
instead.
where ytd-wages*<ytd-commission*
where ship-quant*=order-quantity*selects those records where the ship-quantity is equal to the order-quantity.
where number spans 2300*2400
where lastname spans 'A'*'Smith'selects those employees with last names up to Smith. See Character Fields in View WHERE Clauses for details about the use of quotation marks.
where cost=.50 & (type=ansi12 | class=sorry)
where cost=.50 & not (type=ansi12 | class=sorry)
where avail eq y | avail eq w
where part=9846 & on-hand>2.Oe+6
where lastname=Smithextracts data for SMITH, and the next example extracts data for Smith:
where lastname='Smith'
where name=Andersonselects all records where NAME is ANDERSON. The WHERE clause
where city='TRUTH OR CONSEQUENCES' | stzip='NM 87901'selects all records where CITY is TRUTH OR CONSEQUENCES or STZIP is NM 87901. Notice in the first condition that quotation marks prevent OR from being used as an operator. In the second condition, they prevent the extra space between NM and 87901 from being removed.
where shop='Joe''s Garage' where shop=”Joe;s Garage”selects all records where SHOP is Joe's Garage. Because the value is enclosed in quotation marks, the two consecutive single quotation marks are treated as one quotation mark. You can also use double quotation marks around a value. Also, two consecutive double quotation marks become one double quotation mark if enclosed in double quotation marks. If two consecutive double quotation marks are enclosed in single quotation marks, they remain two double quotation marks and conversely.
where code=f1fextracts the data for CODE equals 10 (F1F0).
|
Condition
|
Request Sent to CA-DATACOM/DB
|
|
|---|---|---|
|
cost=.003
|
Error
|
(underflow: field has two decimals)
|
|
cost>.003
|
cost>0.00
|
(truncated)
|
|
cost>3.0052
|
cost>3
|
(truncated)
|
|
cost<.0001
|
cost ≤ 0.00
|
(truncated, < changed
to ≤ )
|
|
cost<20.001
|
cost ≤ 20
|
(truncated, < changed
to ≤ )
|
where zipcode eq 7#8selects all records with ZIP codes that have a 7 in the first position and an 8 in the third position. The condition
where lastname contains m#nselects all records with last names such as Mendoza, Harman, and Warminsky.
where lastname eq m#nwould select records with last names such as Mendoza, McNeal, and Monroe. Names such as Harman or Warminsky would not qualify.
where init-id=\jde\27#\selects all records where the initials are JDE and the ID number starts with 27. Your value for ID is padded on the right with mask characters, so the entire value is treated as if you had specified JDE27#####.
where init-id=jde27#####also selects all records where the initials are JDE and the ID number starts with 27, just as in the previous example. No delimiters are required here because
JDE27##### is 10 characters
long, which is the same size as the key field.
where weight-sex=78mselects all records where weight equals 78 and sex equals M. The value is treated as if it had been specified as \78\m\.
where age-degree=25bsselects all records where age equals 25 and degree equals BS. The value is treated as if it had been specified as \25\bs \.