Notes on Null Values in the Queries

For correct customization of queries it is important to understand how queries work in regard to 'null' values. This is especially important for fields with boolean values, which should be created in a way precluding 'null' (in the sense of 'empty') values. See the explanation and examples below.

How databases treat NULL values

All database systems treat NULL values as 'unknown' values. Consequently, SQL queries with any operator (except IS) never return rows with NULL values. An unknown value means neither 'No' nor 'Yes' - it is simply unknown.

For example, let's consider table EMPLOYEES (total 1000 rows) with column MARRIED containing 'Y' for married employees, 'N' for single or divorced employees and NULL if the information is unknown.

select * from EMPLOYEES where

• MARRIED = 'Y' > returns 500 rows
• MARRIED = 'N' > returns 400 rows
• MARRIED IS NULL -> returns 100 rows (notice that IS operator must be used)
• MARRIED <> 'Y' -> returns 400 rows (not 500 rows as one might expect - rows with NULL values are not returned)
• MARRIED <> 'N' -> returns 500 rows (not 600 rows as one might expect - rows with NULL values are not returned)
• MARRIED IS NOT NULL returns 900 rows

The following make no sense (you'll always get 0 rows, no matter what data there are)

• MARRIED = NULL -> returns 0 rows
• MARRIED <> NULL -> returns 0 rows

This is how databases treat NULL values. Valuemation searches reflect this behaviour.

NULL in Valuemation Searches

Queries created in Valuemation (e.g. in the Column, Field or Expert search) get converted to SQL statements.

Examples:

1. Boolean example

   In the catalog of services, query 'isServiceTemplate != true'

   

   gets converted to SQL:

   select * from AMA_SERVICE where IS_SERVICE_TEMPLATE <> 'Y'

   As seen in the EMPLOYEES example above, the query will not return rows with NULL values.

2. Other data types

   The same behaviour applies to 'non-boolean' attributes. For instance, filtering Persons by "middleName != 'John' " will return persons with middle names other than 'John' but not those who have no middle name.

Consequences for customization

It is necessary to take the above described behaviour into consideration when creating queries. For example:

1. To prevent the problematic 'isServiceTemplate != true' query in example 1, the corresponding database column must be NOT NULL with some default value (depending on the business logic either 'Y' or 'N').

   e.g.alter table AMA_SERVICES add IS_SERVICE_TEMPLATE char(1) DEFAULT 'N' NOT NULL

2. To make sure that the query in example 2 returns also persons with no middle name, the query has to be "middleName != 'Jan' || middleName == null"