When you work with query language, you sometimes need to replace the NULL value with some other value. You might need this to get warehouse balances for all the products. This article covers the usage of the ISNULL() query language function in such scenarios and also considers other possible solutions.
The ISNULL function
The query language includes the ISNULL() function, which is intended to replace an expression with another one if its value is NULL. It uses the following syntax:
ISNULL(<Expression being checked>, <Substitution expression>)
This function returns the value of the first parameter if it is not NULL, otherwise it returns the value of the second parameter.
SELECT CatalogItems.Description, ISNULL(ItemAccountingBalance.AmountBalance, 0) AS QuantityBalance FROM Catalog.Items AS CatalogItems LEFT JOIN AccumulationRegister.ItemAccounting.Balance AS ItemAccountingBalance ON ItemAccountingBalance.Items = CatalogItems.Ref WHERE CatalogItems.IsFolder = FALSE
This code gets all the items of the products catalog, and then gets the current balances for each product in the accumulation register. Since the virtual table does not return records for products that have no balances, as a result of the join the ItemAccountingBalance.QuantityBalance field will store the NULL values for the products without balances. In order to have 0 instead of NULL in the query result, we are using the function ISNULL() that performs the required substitution.
Using the CASE operation
Another method of dealing with the described situation is using the CASE query language operation. A query similar to the one above will look like this:
SELECT CatalogItems.Description, CASE WHEN ItemAccountingBalance.QuantityBalance IS NULL THEN 0 ELSE ItemAccountingBalance.QuantityBalance AS QuantityBalance FROM Catalog.Items AS CatalogItems LEFT JOIN AccumulationRegister.ItemAccounting.Balance AS ItemAccountingBalance ON ItemAccountingBalance.Items = CatalogItems.Ref WHERE CatalogItems.IsFolder = FALSE
The result of this query is identical to that of the query in the previous section.
Using the ISNULL() function is preferable to using the CASE operation for the following reasons: the code with ISNULL() is shorter, which means better readability. In addition, when the expression being checked is a complex function (aggregate function, among others), the ISNULL() expression can be calculated faster than the CASE expression.
Specifics of the ISNULL function
While ISNULL() is an equivalent of the CASE operation that checks whether a value is NULL, there is still a difference: when the expression is a string or a number, the substitution expression is converted to that type.
For example, when the expression being checked has String(5) type and the substitution expression has String(10) type, the result is converted to String(5) type. So when the function returns the substitution expression, its value is cut to five characters.
It is similar for numeric expressions: the value is converted to the type of the expression being checked, i.e. the substitute value might be cut. If a value cannot be converted, the query language aborts query execution with an error. For example, converting the number 1000 to Number(2) type results in an error.