Posts Tagged ‘null’

Cool Undocumented SQL Function SYS_OP_MAP_NONNULL and Some Alternatives

In Oracle, NULL does not equal NULL:

EDDIE@XE> SELECT ‘hi there’ 2 FROM DUAL 3 WHERE NULL = NULL;

no rows selected

But sometimes, if not most of the times, you do want the condition NULL = NULL to return true. For example:

[…]

One more reason to upgrade your Oracle 8i

On my Oracle database 8i (8.1.7.4) instance, I ran the following:

SQL> SELECT 1 my_number 2 FROM DUAL 3 UNION 4 SELECT NULL my_number 5 FROM DUAL 6 / SELECT 1 my_number * ERROR at line 1: ORA-01790: expression […]

A binary flag with three values

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 […]

NVL, NVL2 or COALESCE?

In Oracle, as in other RDBMSs, nulls should be given a special treatment. In Oracle, there are three (maybe more?) functions that deal with nulls, NVL, NVL2 and COALESCE. I would like to ask you a question, but first, let me quickly refresh my memory:

NVL2 in Oracle

Did you know that in addition to the function NVL, Oracle also has the function NVL2? which is quite different than its cousin NVL. So, what does NVL2 do?