Null Value - SQL Considerations (Magic xpa 4.x)
Nulls represent missing and unknown data. All SQL databases support null values. A field that has a null value is different from a blank field in an ISAM file. Null means that the value is not known. Null values require special handling. If you attempt to do arithmetical operations on a numeric column and one or more of the values are null, then the result will be null. If an alpha field allows null values, and you select all records in which the alpha field is blank, records with the null value in the alpha field will NOT be selected.
Null values do not participate in index searches. It is highly recommended not to define indexes on columns that are null-allowed.
For example, this SELECT statement
SELECT *
FROM Table1
WHERE Fld1>=4 or Fld1<4
will return all the records in Table1 except for the records where Fld1 is null.
Nulls are represented in a different sort value in each database. For example, if we perform this SELECT statement in Oracle and in MSSQL, we’ll receive a different order of records in each database.
SELECT *
FROM Table1
Order by Fld1 ASC
In Oracle nulls are saved as the highest value in the database, so records with nulls in column Fld1 will appear as the last records of this SELECT statement.
In MSSQL nulls they are saved as the lowest value in the database, so records with nulls in column Fld1 will appear as the first records of this SELECT statement.
When sending an element with a Null value, the element will not be sent. For example, when updating a column with a Null value, the column element will not be sent in the request.
For ranges:
-
When both the From and To values are Null values, the XML request will contain only the column name without the Min and Max elements.
-
When performing only a From (or To) range on a Null value and the To (or From) is not defined, the XML request will not contain the column name. When doing only From (or To) range on a Null value and the To (or From) is a non-Null value, the XML request will contain only the To (or from) with the non-Null value.
-
When a CNDRange function is defined in the range (either of From or To) and the CNDRange expression is evaluated to False, the corresponding Min or Max Range elements will not be sent in the XML request.
-
When both the From and To are not defined (or when the CNDRange() has False expression), the entire column will not be sent in the XML request. This is different then the case when both the From and To are Null values.
When an element is missing from a response, Magic xpa will not update the variable and the default value will be used. It is not possible to specifically get a Null value from the database.
