WITH Clause Comparison Operators

DQL Statements › WITH Clauses › WITH Clause Comparison Operators

WITH Clause Comparison Operators

►►─┬────────┬─ operand1 ─ comparison-operator ─ operand2 ─────────────────────►◄
   └─ WITH ─┘

Explanation

Comparison-operator in the preceding diagram indicates the type of comparison to be performed between operand1 and operand2. The following chart lists the operators you can use in a WITH clause, shows alternate entries (if they exist), and gives examples on how to use the operators.

Note: See WITH Clauses for restrictions on using AND with EQ or NE.

Operator	Alternate Entry	Explanation/Example
EQUAL	EQ, =	CA Dataquery selects only those rows for which the value of operand1 equals the value of operand2. WITH STATE EQUAL 'TX' In addition, you can use THRU with the EQUAL operation to specify a range of values for a numeric column. WITH YTD-SALES = 1000 THRU 2000 You can include a series of values for operand2 in a list fashion using the operator EQUAL. WITH STATE = 'TX','NY', 'CA'
NOT EQUAL	NE, ,=	CA Dataquery selects only those rows for which operand1 is not equal to the value of operand2. WITH STATE NE 'TX','NY', 'CA'
GREATER THAN	GT, >	CA Dataquery selects only those rows for which the value of operand1 is greater than the value of operand2 WITH YTD-SALES GT 1000
GREATER THAN OR EQUAL TO	GTE	CA Dataquery selects only those rows for which the value of operand1 is greater than or equal to the value of operand2. WITH YTD-SALES GTE 1000
LESS THAN	LT, <	CA Dataquery selects only those rows for which the value of operand1 is less than the value of operand2. WITH YTD-SALES LT 1000
LESS THAN OR EQUAL TO	LTE	CA Dataquery selects only those rows for which the value of operand1 is less than or equal to the value of operand2. WITH YTD-SALES LTE 1000
CONTAINING	(none)	CA Dataquery selects only those rows for which the value of operand1 contains the value of operand2 in any position of operand1. The operand2 value must be a literal. This instructs CA Dataquery to select only those rows which contain the literal. WITH CITY CONTAINING 'DA'
NOT	,	CA Dataquery selects all rows for which the value of operand1 does not compare with value for operand2. You can use NOT with any of the operators listed preceding except NOT EQUAL. WITH YTD-SALES NOT LT 1000

Operator

Alternate Entry

Explanation/Example

EQUAL

EQ, =

CA Dataquery selects only those rows for which the value of operand1 equals the value of operand2.

WITH STATE EQUAL 'TX'

In addition, you can use THRU with the EQUAL operation to specify a range of values for a numeric column.

WITH YTD-SALES = 1000 THRU 2000

You can include a series of values for operand2 in a list fashion using the operator EQUAL.

WITH STATE = 'TX','NY', 'CA'

NOT EQUAL

NE, ,=

CA Dataquery selects only those rows for which operand1 is not equal to the value of operand2.

WITH STATE NE 'TX','NY', 'CA'

GREATER THAN

GT, >

CA Dataquery selects only those rows for which the value of operand1 is greater than the value of operand2

WITH YTD-SALES GT 1000

GREATER THAN OR EQUAL TO

GTE

CA Dataquery selects only those rows for which the value of operand1 is greater than or equal to the value of operand2.

WITH YTD-SALES GTE 1000

LESS THAN

LT, <

CA Dataquery selects only those rows for which the value of operand1 is less than the value of operand2.

WITH YTD-SALES LT 1000

LESS THAN OR EQUAL TO

LTE

CA Dataquery selects only those rows for which the value of operand1 is less than or equal to the value of operand2.

WITH YTD-SALES LTE 1000

CONTAINING

(none)

CA Dataquery selects only those rows for which the value of operand1 contains the value of operand2 in any position of operand1. The operand2 value must be a literal. This instructs CA Dataquery to select only those rows which contain the literal.

WITH CITY CONTAINING 'DA'

NOT

CA Dataquery selects all rows for which the value of operand1 does not compare with value for operand2. You can use NOT with any of the operators listed preceding except NOT EQUAL.

WITH YTD-SALES NOT LT 1000