{"id":167,"date":"2007-08-26T19:46:05","date_gmt":"2007-08-26T18:46:05","guid":{"rendered":"http:\/\/bar-solutions.com\/wordpress\/?p=167"},"modified":"2007-08-26T19:46:05","modified_gmt":"2007-08-26T18:46:05","slug":"tri-state-boolean","status":"publish","type":"post","link":"https:\/\/blog.bar-solutions.com\/?p=167","title":{"rendered":"Tri-state boolean"},"content":{"rendered":"

All data types in the Oracle<\/a> database implement the null value. Null is a very special value which means ‘Unknown’. That means that the value is really unknown. Null is not<\/strong> equal to null, because both are not known. Therefore you cannot compare them to each other.<\/p>\n

Even the boolean value has this null value implemented. Where you should expect a boolean value to hold either true<\/em> or false<\/em>, in Oracle a boolean value can also hold null as a value. So a boolean value has three possibilities instead of the two you normally expect.<\/p>\n

<\/p>\n

Normally you would have this truth table for comparing boolean values:<\/p>\n\n\n\n\n\n\n\n
left<\/strong><\/td>\nright<\/strong><\/td>\nand<\/strong><\/td>\nor<\/strong><\/td>\n<\/tr>\n
true<\/td>\ntrue<\/td>\ntrue<\/td>\ntrue<\/td>\n<\/tr>\n
true<\/td>\nfalse<\/td>\nfalse<\/td>\ntrue<\/td>\n<\/tr>\n
false<\/td>\ntrue<\/td>\nfalse<\/td>\ntrue<\/td>\n<\/tr>\n
false<\/td>\nfalse<\/td>\nfalse<\/td>\nfalse<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

According to this<\/a> page, you have the following truth table for comparing boolean values:<\/p>\n\n\n\n\n\n\n\n\n\n\n\n\n
left<\/strong><\/td>\nright<\/strong><\/td>\nand<\/strong><\/td>\nor<\/strong><\/td>\n<\/tr>\n
true<\/td>\ntrue<\/td>\ntrue<\/td>\ntrue<\/td>\n<\/tr>\n
true<\/td>\nfalse<\/td>\nfalse<\/td>\ntrue<\/td>\n<\/tr>\n
true<\/td>\nnull<\/td>\nnull<\/td>\ntrue<\/td>\n<\/tr>\n
false<\/td>\ntrue<\/td>\nfalse<\/td>\ntrue<\/td>\n<\/tr>\n
false<\/td>\nfalse<\/td>\nfalse<\/td>\nfalse<\/td>\n<\/tr>\n
false<\/td>\nnull<\/td>\nfalse<\/td>\nnull<\/td>\n<\/tr>\n
null<\/td>\ntrue<\/td>\nnull<\/td>\ntrue<\/td>\n<\/tr>\n
null<\/td>\nfalse<\/td>\nfalse<\/td>\nnull<\/td>\n<\/tr>\n
null<\/td>\nnull<\/td>\nnull<\/td>\nnull<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

This means you either have to build you software around these possibilities or you can treat the null value as if it were false, i.e. just test for true value. This is mostly how Oracle PL\/SQL treats this value. In an if statement everything that results in null, will mean the else<\/em> branch will be executed.<\/p>\n

I have for example an overloaded function in my utilities package implementing an ifelse function.<\/p>\n

\n
  <\/span>FUNCTION<\/span> ifelse(\n    if_in                          <\/span>IN<\/span>      <\/span>BOOLEAN<\/span>\n  , then_in                        <\/span>IN<\/span>      <\/span>BOOLEAN<\/span>\n  , else_in                        <\/span>IN<\/span>      <\/span>BOOLEAN<\/span> )\n    <\/span>RETURN<\/span> <\/span>BOOLEAN<\/span>;\n<\/span><\/div><\/pre>\n
<\/div>\n<\/p>\n
this function is overloaded for different datatypes, but the implementation is roughly the same for all versions:<\/p>\n
\n
  <\/span>FUNCTION<\/span> ifelse(\n    if_in                          <\/span>IN<\/span>      <\/span>BOOLEAN<\/span>\n  , then_in                        <\/span>IN<\/span>      <\/span>BOOLEAN<\/span>\n  , else_in                        <\/span>IN<\/span>      <\/span>BOOLEAN<\/span> )\n    <\/span>RETURN<\/span> <\/span>BOOLEAN<\/span>\n  <\/span>IS<\/span>\n    lv_returnvalue                           <\/span>BOOLEAN<\/span>;\n  <\/span>BEGIN<\/span>\n    <\/span>IF<\/span> if_in <\/span>THEN<\/span>\n      lv_returnvalue   :<\/span>=<\/span> then_in;\n    <\/span>ELSE<\/span>\n      lv_returnvalue   :<\/span>=<\/span> else_in;\n    <\/span>END<\/span> <\/span>IF<\/span>;\n\n    <\/span>RETURN<\/span> lv_returnvalue;\n  <\/span>END<\/span>;\n<\/span><\/div><\/pre>\n
<\/div>\n
This implementation treats every value other than true as a false value. So, if I use this function if will have a work around for the null value and I can use boolean values as if they were (normal) two-state booleans. I can, for instance, use this function as a wrapper to work around possible null values in my if-statements.<\/p>\n
Where I would have currently have this code:<\/p>\n
\n
if<\/span> (a <\/span>=<\/span> b) <\/span>and<\/span> (c <\/span><><\/span> d)  <\/span>then<\/span><\/div><\/pre>\n
<\/div>\n<\/p>\n
where the values of the variables could be null independently, I would make this code:<\/p>\n
\n
if<\/span> bar$util.ifelse((a <\/span>=<\/span> b) <\/span>and<\/span> (c <\/span><><\/span> d),<\/span>true<\/span>,<\/span>false<\/span>)  <\/span>then<\/span><\/div><\/pre>\n
<\/div>\n<\/p>\n
Now I am sure that the null values are treated as if they were false and I don’t have to worry about what the values really are.<\/p>\n