Modernizing IBM eServer iSeries Application Data Access - A Roadmap Cornerstone

196 Modernizing IBM Eserver iSeries Application Data Acess - A Roadmap Cornerstone

If you want to select NULL values, you have to add MyField IS NULL in the where clause. IS

NOT NULL can be used to select all rows that do not contain any NULL VALUES in a

particular column.

Example 9-13 shows how NULL values can be selected.

Example 9-13 Selection of NULL values

select *

from Order_Header

where (Order_Date = Current Date or Order_Date is NULL)

and Order_Delivery is not NULL

If you want to count rows using SQL column function COUNT(*), all rows are counted, even if

one or several rows contain only NULL values. If you use COUNT(FieldName) instead, and

FieldName contains NULL values, only the rows without NULL value are considered.

If you want to calculate the average using the SQL column function AVG(FieldName) and one

or more rows contain a NULL value, they are not considered. Let us assume that we have

three rows, containing, 2, 4, and NULL; the average will be 3.

Other SQL column functions like STDDEV (to calculate the biased standard deviation) and

VARIANCE (to calculate the biased variance) do not consider rows containing NULL values

either.

If you want to calculate the average, standard deviation, or variance over all rows, you have

to convert the NULL values into default values. This can be done by using the scalar function

COALESCE or VALUE.

The following example shows how the NULL value can be replaced by a zero using SQL

scalar function COALESCE:

SELECT Avg(Coalesce(ORER_TOTAL, 0)) FROM ORDER_HEADER

There is an SQL scalar function NULLIF that converts specified values into NULL values. The

following example shows how a zero value can be replaced through a NULL value using the

SQL scalar function NULLIF:

SELECT Avg(NullIf(ORER_TOTAL, 0)) FROM ORDER_HEADER

9.3 Date and time calculation

There are a lot of applications that restore all date and time information in numeric or

character fields. When modernizing our database, we should consider converting these

numeric or character fields into real date or time fields.

In the first step, we can add additional fields in our tables containing the date and time

information. Then we have to fill the new fields by translating the existent numeric or

character values into real date or time information. To guarantee that the new fields are

always updated, we can add before update triggers for the numeric and alphanumeric fields

that fill the date and time values.

After having modernized our tables, we can modernize the date and time calculation in our

programs, using the date and time fields instead of the numeric or character date and time

Note: COALESCE should be preferred for conformance to the SQL 1999 standard.