BOOLEAN
As of TDV version 7.0.2, BOOLEAN complies with ANSI/ISO 2011 (draft), with the exceptions noted in the remarks below. Previous behavior is deprecated, although you can force the old behavior using a server configuration parameter, as described in
Overriding Standard-Compliant BOOLEAN Behavior.
• Character string literals “true” “false” and “unknown” can be CAST to BOOLEAN values TRUE, FALSE and UNKNOWN (NULL), respectively. The literal values are case-insensitive.
• Any other input values raise an error. Specifically, implicit conversion of non-zero numeric values to TRUE, and numeric values of zero to FALSE, raises an error.
• BOOLEAN types cannot be compared with other types without a CAST.
• Values of non-BOOLEAN types cannot be assigned to BOOLEAN targets directly. without a CAST. You must use a CASE to convert values of other types to TRUE, FALSE, or UNKNOWN, and then CAST those values to BOOLEAN. For example, you cannot directly CAST(1 as BOOLEAN) to TRUE.
• Cannot Convert from BOOLEAN to non-BOOLEAN types or vice versa.
• BOOLEAN values cannot be function arguments. Specifically, the previous behavior of allowing BOOLEAN arguments to the following functions raises an error: CONCAT, DLE_DST, LE_DST, POSITION, REPEAT, TRIM, TS_FIRST_VALUE, and XMLTEXT.
• BOOLEAN types and values cannot be mixed with non-BOOLEAN types without a suitable CAST.
• Exception to the standard: TDV does not support {IS | IS NOT} {TRUE | FALSE | UNKNOWN} on BOOLEAN arguments.
Overriding Standard-Compliant BOOLEAN Behavior
You can use a configuration parameter to suppress the new, ANSI-compliant behavior and enable legacy BOOLEAN support. Legacy BOOLEAN support consists of mixing of BOOLEAN and non-BOOLEAN types without a CAST.
Legacy BOOLEAN support is deprecated as of TDV version 7.0.2.
The default value of this parameter is False.
To override standard-compliant BOOLEAN behavior
1. Select Administration > Configuration from the main Studio menu.
2. Navigate to Server > SQL Engine > SQL Language.
3. Set the parameter Allow Numeric Boolean Comparisons Assignments to True.
Changing the value has no effect until the next server restart.