Comparison Predicate

A comparison predicate compares two expressions using a comparison operator. The predicate evaluates to TRUE if the first expression is related to the second expression as specified in the comparison operator.

Scope

SQL Data Manipulation Statements

SQL Syntax

  Expression { =  
               <>  
               >  
               >=  
               <  
               <=} [ Expression
                     SubQuery    ]

Parameters

Expression: specifies a value used to identify columns, screen rows, or define new column values. The syntax of expressions is defined in Chapter 8 “Expressions” Both numeric and non-numeric expressions are allowed in comparison predicates. Predicates cannot include LONG columns.
SubQuery: is a QueryExpression whose result is used in evaluating another query. The syntax of QueryExpression is presented in the description of the SELECT statement.
=: is equal to. A comparison predicate using = is also known as an EQUAL predicate.
<>: is not equal to.
>: is greater than.
>=: is greater than or equal to.
<: is less than.
<=: is less than or equal to.

Description

Character strings are compared according to the HP eight-bit ASCII collating sequence for ASCII data, or the collation rules for the native language of the DBEnvironment for NLS data. Column data would either be ASCII data or NLS data depending on how the column was declared upon its creation. Constants are ASCII data or NLS data depending on whether you are using NLS or not.
If a case insensitive ASCII expression is compared to a case insensitive NLS expression, the two expressions are compared using the NLS collation rules. The case insensitive NLS comparison is done by using the NLSCANMOVE and NLSCOLLATE intrinsics. The same ASCII characters in upper and lower case are equivalent. Accent characters (extended character) in upper and lower case are also equivalent. However, an accent character may not be the same as its ASCII equivalent, depending on the specific language collation table.
Extended upper and lower case characters are not equivalent to the ASCII expression. They are compared to the NLS collation table.
If a case sensitive character column is compared to a character column that is not case sensitive, both columns are treated as case sensitive. If a string constant is compared to a column that is not case sensitive, then the string constant is treated as not case sensitive.
Refer to Chapter 7 “Data Types” for type conversion that ALLBASE/SQL performs when you compare values of different types.
For purposes of the Comparison Predicate, a NULL value on either or both sides of the predicate causes it to evaluate to unknown. Thus, two NULL values on either side of an equals predicate will not result in a TRUE result but rather in unknown.
A NULL value in an expression causes comparison operators to evaluate to unknown. Refer to the "Search Condition" section at the beginning of this chapter for more information on evaluation of operators.
A subquery must return a single value (one column of one row). If the subquery returns more than one value, an error is given. If the subquery returns no rows, the predicate evaluates to unknown.

Example

The part numbers of parts that require fewer than 20 days for delivery are retrieved.

   SELECT PartNumber
     FROM PurchDB.SupplyPrice
    WHERE DeliveryDays < 20

Comparison Predicate

Technical documentation

» Table of Contents

» Index

Scope

SQL Syntax

Parameters

Description

Example