Use boolean functions to manipulate any values in SELECT, INSERT, UPDATE, or DELETE queries. DBISAM's SQL supports the following boolean functions:

IF Function

The IF function performs inline IF..ELSE boolean expression handling:

IF(boolean expression THEN result expression
  ELSE result expression)
IF(boolean expression, result expression,
  result expression)

Both result expressions must be of the same data type. Use the CAST function to ensure that both expressions are of the same data type.

In the following example, if the Category column contains the value 'WRASSE', then the column value returned will be the Common_Name column, otherwise it will be the Species Name column:

SELECT IF(Upper(Category)='WRASSE'
THEN Common_Name
ELSE "Species Name") AS Name
FROM Biolife

The IF function can be used in WHERE clause comparisons to cause a conditional comparison:

SELECT *
FROM Employee
WHERE IF(LastName='Young' THEN PhoneExt='233' ELSE PhoneExt='22')

IFNULL Function

The IFNULL function performs inline IF..ELSE boolean expression handling specifically on NULL values:

IFNULL(expression THEN result expression
      ELSE result expression)
IFNULL(expression, result expression,
      result expression)

Both result expressions must be of the same data type. Use the CAST function to ensure that both expressions are of the same data type.

In the following example, if the Category column contains a NULL value, then the column value returned will be the Common_Name column, otherwise it will be the Species Name column:

SELECT IFNULL(Category THEN Common_Name
ELSE "Species Name") AS Name
FROM Biolife

The IFNULL function can be used in WHERE clause comparisons to cause a conditional comparison:

SELECT *
FROM Employee
WHERE IFNULL(Salary THEN 10000 ELSE Salary) > 8000
 

NULLIF Function

The NULLIF function returns a NULL if the two values passed as parameters are equal:

NULLIF(expression,expression)

Both expressions must be of the same data type. Use the CAST function to ensure that both expressions are of the same data type.

In the following example, if the EmpNo column contains the value 14, then the value returned will be NULL, otherwise it will be the EmpNo column value:

SELECT NULLIF(EmpNo,14) AS EmpNo
FROM Orders

The NULLIF function can be used in WHERE clause comparisons to cause a conditional comparison:

SELECT *
FROM Employee
WHERE NULLIF(Salary,10000) > 8000
 

COALESCE Function

The COALESCE function returns the first non-NULL value from a list of expressions:

COALESCE(expression [, expression [, expression]])

All expressions must be of the same data type. Use the CAST function to ensure that all expressions are of the same data type.

In the following example, if the Category column contains a NULL value, then the column value returned will be the Common_Name column. If the Common_name column contains a NULL, then the literal string 'No Name' will be returned:

SELECT COALESCE(Category,Common_Name,'No Name') AS Name
FROM Biolife