While troubleshooting a problem in the Order entry module of Oracle Applications (11.0.3), I query the column open_flag in the order header table so_headers_all for the order number in question. The column open_flag is supposed to be a Y/N flag that tells me whether the order is open or closed. Instead, for this order, the open_flag is NULL.
Come on! how am I supposed to know the meaning of NULL here? Does null mean ‘Y’ (order open) or ‘N’ (order closed). There should have been a NOT NULL and a CHECK constraint on the open_flag column.
In my definition, a flag is supposed to have two, and only two, values, Y/N, 0/1… Why allow a flag to have a third value, NULL? It only adds to the confusion and is definitely a bad design.
Relying on a NULL to give a meaning to a column is a bad practice.
Possibly related:
- Writing/Reading images to/from DB
- Saving/Downloading files to/from Oracle using ColdFusion
- LOBs Gotcha in ColdFusion
- Forgotten SQL Function: LEAST
- Rename column or constraint in Oracle
Tagged flag, null | Post a Comment
Oh well, guess I have to figure something else out...
………. Great links to “posts” (I couldn’t find them...
Is it ever possible for the “open_flag” to be irrelevant or not applicable? That would be an appropriate use of NULL for a flag.
Query the table for all cases where open_flag is NULL and look for a common thread. Maybe they’re all a special type of order that is never open nor closed. Without knowing more about the business, its hard to really conclude when/if NULL is appropriate.
April 4th, 2006, at 9:49 am #Well this is just the old argument about NULL. Arguably all columns in a database should be NOT NULL, with suitable values decided upon for each meaning we might assign to NULL (inapplicable, unknown, file not found, etc). This is, of course, particularly tricky to implement for numeric, without using magic numbers. I know of no database anywhere that does this. We all live with NULLs.
Still this did remind me of a happy few weeks I spent on a Forms 3 project many years ago. One of the clients wanted all the flag columns to be trivalent. I got chosen to go through all the Forms, making the flag columns optional, removing the default values and changing the code to handle null values. Even now it still sends shudders down my spine.
Cheers, APC
April 4th, 2006, at 9:57 am #It could be a clue to the problem (especially if there aren’t any other rows with it null). If the record was created but the process failed before it was able to set open_flag…. As I remember we’ve seen cases in Oracle Apps 11.5.4 where dates aren’t set even though they should be (though obviously not required) because the entire process didn’t complete successfully.
April 4th, 2006, at 10:00 am #The open_flag is always relevant. An order can either be open or closed, no third value. That’s the business rule.
It turns out that the standard Oracle Applications (11.0.3) functionality sets open_flag to NULL (instead of ‘N’) when the order is closed, and to ‘Y’ when the order is open. That explains why the majority of rows have NULL in the open_flag (old closed orders).
So basically, in this case, NULL was given the meaning of ‘N’.
NULL should mean nothing, and any design that relies on NULL to mean something, is, in my opinion, a bad design.
April 4th, 2006, at 10:20 am #maybe ‘designed’ for performance, to keep the index on open_flag as small as possible?
April 4th, 2006, at 2:23 pm #welcome in the wonderfull world of Oracle Applications. Wait till Fusion when it will support multiple databases.