CHECKFORNULL

Top  Previous  Next

The CHECKFORNULL field switch applies to all SQL drivers

 

Usage:

 

In the External Name attribute:

‘field name | CHECKFORNULL’

 

When browsing through a table, it is sometimes necessary for the driver to request all rows that are at, or before, the current row. It does this by generating a WHERE clause. For example:

WHERE (field1 <= value) AND (field1 < value OR field2 <= value2)

 

The above example is for a two component key. For more components, the WHERE clause gets longer, and this will work well in most cases. However, in SQL, if a field has a NULL value, then field < value is false, field = value is false, and field > value is also false. So, if you are sorting on field components that contain NULL values, you need to set the external field name of the field to

 

‘field name | CHECKFORNULL’

 

This will force the driver to generate:

WHERE ((field1 <= value OR field1 IS NULL)) AND ((field1 < value OR field1 IS NULL) OR field2 <= value2)

 

So, in this example, the WHERE clause will also return rows that contain NULL values, instead of rejecting them.