NULL Predicate

Scope

SQL Syntax

  {ColumnName
    :HostVariable[[INDICATOR]:IndicatorVariable]
    ?
    :LocalVariable
    :ProcedureParameter
    ::Built-inVariable
    AddMonthsFunction
    AggregateFunction
    Constant
    DateTimeFunction
    CurrentFunction
    LongColumnFunction
    StringFunction
    CASTFunction
    TIDFunction
    (Expression)   } IS [NOT] NULL

Parameters

ColumnName

is the name of a column from which a value is to be taken; column names are defined in Chapter 6 "Names".

HostVariable

contains a value in an application program being input to the expression.

IndicatorVariable

names an indicator variable, whose value determines whether the associated host variable contains a NULL value:

> = 0

the value is not NULL

< 0

the value is NULL (The value in the host variable will be ignored.)

?

is a place holder for a dynamic parameter in a prepared SQL statement in an application program. The value of the dynamic parameter is supplied at run time.

LocalVariable

contains a value in a procedure.

ProcedureParameter

contains a value that is passed into or out of a procedure.

Built-inVariable

is one of the following built-in variables used for error handling:

• ::sqlcode

• ::sqlerrd2

• ::sqlwarn0

• ::sqlwarn1

• ::sqlwarn2

• ::sqlwarn6

• ::activexact

The first six of these have the same meaning that they have as fields in the SQLCA in application programs. Note that in procedures, sqlerrd2 returns the number of rows processed for all host languages. However, in application programs, sqlerrd3 is used in COBOL, Fortran, and Pascal, while sqlerr2 is used in C. ::activexact indicates whether a transaction is in progress or not. For additional information, refer to the application programming guides and to Chapter 4 "Constraints, Procedures, and Rules"

AddMonthsFunction

returns a value that represents a DATE or DATETIME value with a certain number of months added to it.

AggregateFunction

is a computed value; aggregate functions are defined in this chapter.

Constant

is a specific value; constants are defined later in this chapter.

ConversionFunction

returns a value that is a conversion of a date/time data type into an INTEGER or CHAR value, or from a CHAR value.

CurrentFunction

returns a value that represents the current DATE, TIME, or DATETIME.

LongColumnFunction

returns information from a long column descriptor.

StringFunction

returns a partial value or attribute of string data.

TIDFunction

returns the database address of a row (or rows for a BULK SELECT) of a table or an updatable view. You cannot use mathematical operators with this function except to compare it to a value, host variable, or dynamic parameter (using =, or <>).

(Expression)

is one or more of the above primaries, enclosed in parentheses.

NOT

reverses the value of the predicate that follows it.

Description

Example

   SELECT *
     FROM PurchDB.Vendors
    WHERE ContactName IS NULL