When you use the unknown value in a WHERE clause with the DataServer, the unknown value satisfies only the equals (=) operator. You can also use a zero-length character string in a WHERE clause. The unknown value and zero-length character string are not the same. The unknown value translates to a NULL, which is a special marker in a data source that supports NULLs used to represent missing information. On the other hand, zero-length strings and blank columns contain actual values. Both of the following statements find the first customer record with a zero-length string in the address2 field. Notice the space between the quotation marks in the first statement:
FIND FIRST customer WHERE customer.address2 = " ".
FIND FIRST customer WHERE customer.address2 = "".
Although "" and " " evaluate the same way in a WHERE clause, they have different results when you use them with the BEGINS function. For example, the following statement retrieves all customer names except those that have the unknown value:
FOR EACH customer WHERE customer.name BEGINS "":
The following statement uses "" to retrieve only those names that begin with a space:
FOR EACH customer WHERE customer.name BEGINS " ":
The following statement is not meaningful to an ODBC data source. It generates the error message "Illegal operator for unknown value or zero length character string:"
FIND FIRST customer WHERE customer.address2 > ?.
This restriction has been relaxed for columns of the DATE data type. For example, the following statement is valid:
FIND FIRST order WHERE order.orderdate > ?.
DB2 UDB considers all zero-length character strings as equal to a single space. Therefore, DB2 UDB considers "" and a string of blank spaces to be effectively the same thing.
