28 March 2017

Good old BIN_TO_NUM to check the overall status

A good while ago Chris Saxon, member of the AskTom answer team, asked on twitter which datatype you use when defining tables when you need a Boolean-representation. As you might know there is no Boolean datatype in SQL.
A lot of discussion followed which I'm not going to repeat.
Usually I use a VARCHAR2(1) with a check constraint for Y and N, but for a recent requirement I decided to use a NUMBER instead.

The requirement that I needed to implement was the following:

A number of tasks need to be fulfilled, the order doesn't matter, and they need to be checked by a supervisor. Before the supervisor gives his/her stamp of approval, the tasks all need to be completed.
For this example I will leave the authorization out, it is irrelevant.
create table tasks
(task_date       date   not null
,task_a          number not null check (task_a in (0,1))
,task_b          number not null check (task_b in (0,1))
,task_c          number not null check (task_c in (0,1))
,tasks_checked   number not null check (tasks_checked in (0,1))
)
/
The table contains a date for which the tasks need to be completed, indicators for the individual tasks (A,B, and C) and the Tasks Checked indicator.
All indicators are NOT NULL, datatype NUMBER and only allowed to contain a zero or one. An zero indicates FALSE, a one indicates TRUE.

The requirement states that the verification of the tasks should take place before the tasks are done. There are several ways to implement this requirement, and this time I'm going to use the combination of zeros and ones to create a binary representation.
I will add to overall status as a virtual column to the table using the built-in function BIN_TO_NUM.

alter table tasks
add (bin_status as (bin_to_num (task_a, task_b, task_c, tasks_checked)))
/

Now the (not so) magic part:
When the binary status equals 14, all tasks are completed and can be checked by a supervisor.
When the binary status is an odd number, the checking of the tasks is done before the tasks are completed and this is not allowed. The only exception is when the binary status equals fifteen (15), then all tasks are done and it is checked by a supervisor.
When the binary status an even number, all is good.
All this can easily be captured in a CHECK constraint:

alter table tasks
add constraint chk check (
   case when bin_status = 15 then 1
        when mod (bin_status, 2) = 0 then 1 
        else 0 end = 1
)
/

Finally two inserts to show that it works as expected.

insert into tasks
   (task_date
   ,task_a
   ,task_b
   ,task_c
   ,tasks_checked   
   )
values
   (sysdate
   ,0
   ,0
   ,1
   ,0
 14     );

1 row created.

insert into tasks
   (task_date
   ,task_a
   ,task_b
   ,task_c
   ,tasks_checked   
   )
values
   (sysdate
   ,1
   ,0
   ,1
   ,1
   );

insert into tasks
*
ERROR at line 1:
ORA-02290: check constraint (ALEX.CHK) violated

For the first time I've used LiveSQL to create the scripts that go along this blogpost, you can find that right here.

Links

3 comments:

  1. I'll concede that works but it seems convoluted.

    Wouldn't it be more self-documenting with something like this?

    ALTER TABLE tasks
    ADD(tasks_completed AS(task_a + task_b + task_c));

    ALTER TABLE tasks
    ADD CONSTRAINT chk CHECK((tasks_completed < 3 AND tasks_checked = 0) OR tasks_completed = 3);

    Now the constraint reads more like the description of the rule. Either all of the tasks are complete or if only some of them, then the supervisor can't check them.


    It could also be done without a virtual column, but I think adding one does help with readability, as well as providing some information directly. It's a count of progress; which would allow for averaging or other status activity across days.


    ALTER TABLE tasks
    ADD CONSTRAINT chk CHECK((task_a + task_b + task_c < 3 AND tasks_checked = 0) OR (task_a + task_b + task_c = 3));

    ReplyDelete
    Replies
    1. Thank you for your comments, appreciate the input.
      There are many ways to skin a cat which you showed in your comments.
      I don't necessarily agree with your comments that it is convoluted, using a virtual column with a simple check constraint is (for me) easier to understand than a large(r) piece of code.
      The example that I used in the blogpost is a simple variation of the problem we faced in the application that I'm working on at the moment. Abstracting the overall status to a single (virtual) column and a (easy to understand) two line check constraint works for us.

      Delete
  2. Thanks for sharing the very useful info about Oracle and please keep updating........

    ReplyDelete