Comparison conditions compare one expression with another. The result of such a comparison can be TRUE, FALSE, or NULL.

When comparing numeric expressions, GGSA uses numeric precedence to determine whether the condition compares INTEGER, FLOAT, or BIGINT values.

Two objects of nonscalar type are comparable if they are of the same named type and there is a one-to-one correspondence between their elements.

A comparison condition specifies a comparison with expressions or view results.

Table 6-2 lists comparison conditions.

condition::=

(aggr_expr::= and non_mt_arg_list_set::=.

A logical condition combines the results of two component conditions to produce a single result based on them or to invert the result of a single condition. Table 6-3 lists logical conditions.

Table 6-4 shows the result of applying the NOT condition to an expression.

Table 6-5 shows the results of combining the AND condition to two expressions.

For example, in the WHERE clause of the following SELECT statement, the AND logical condition returns values only when both product.levelx is BRAND and v1.prodkey equals product.prodkey:

    select 
        v1.region, 
        v1.dollars, 
        v1.month_ 
    from 
        v1, 
        product 
    where 
        product.levelx = "BRAND" and v1.prodkey = product.prodkey

Table 6-6 shows the results of applying OR to two expressions.

For example, the following query returns the internal account identifier for RBK or RBR accounts with a risk of type 2:

 
    select ACCT_INTRL_ID from Acct 
    where (
        ((MANTAS_ACCT_BUS_TYPE_CD = "RBK") OR (MANTAS_ACCT_BUS_TYPE_CD = "RBR")) AND 
        (ACCT_EFCTV_RISK_NB != 2)
    )

Table 6-7 shows the results of applying XOR to two expressions.

For example, the following query returns c1 and c2 when c1 is 15 and c2 is 0.14 or when c1 is 20 and c2 is 100.1, but not both:

<query id="q6"><![CDATA[ 
    select 
        S2.c1, 
        S3.c2 
    from 
        S2[range 1000], S3[range 1000] 
    where
        (S2.c1 = 15 and S3.c2 = 0.14) xor (S2.c1 = 20 and S3.c2 = 100.1)
]]></query>

The LIKE condition specifies a test involving regular expression pattern matching. Whereas the equality operator (=) exactly matches one character value to another, the LIKE conditions match a portion of one character value to another by searching the first value for the regular expression pattern specified by the second. LIKE calculates strings using characters as defined by the input character set.

The LIKE condition with the syntax of the comparison String supports % for 0 or more characters and - for any single character in coherence.

like_condition::=

(arith_expr::= )

In this syntax:

• arith_expr is an arithmetic expression whose value is compared to const_string.

• const_string is a constant value regular expression to be compared against the arith_expr.

If any of arith_expr or const_string is null, then the result is unknown.

The const_string can contain any of the regular expression assertions and quantifiers that java.util.regex supports: that is, a regular expression that is specified in string form in a syntax similar to that used by Perl.

Table 6-8 describes the LIKE conditions.

For more information on Perl regular expressions, see http://perldoc.perl.org/perlre.html.

A range condition tests for inclusion in a range.

between_condition::=

(arith_expr::= )

Table 6-9 describes the range conditions.

A NULL condition tests for nulls. This is the only condition that you should use to test for nulls.

null_conditions::=

(Expressions).

Table 6-10 lists the null conditions.

A compound condition specifies a combination of other conditions.

compound_conditions::=

You can use the IN and NOT IN condition in the following ways:

• in_condition_set: Using IN and NOT IN as a Membership Condition

• in_condition_membership: Using IN and NOT IN as a Membership Condition.

When using the NOT IN condition, be aware of the effect of null values as NOT IN and Null Values describes.