Scalar subqueries, '' = NULL, and the element that never hid

I wanted something small: an APEX cards region where each card shows its latest note, as a Note: line. When an order has no note, the line should disappear instead of showing Note: followed by nothing.

It took two wrong fixes and a revert. Both bugs came from two rules of Oracle NULL handling that everyone “knows” and still gets caught by. Here they are, in the order they bit me.

The setup#

Notes live in an append-only table, one row per save, so “the latest note” means the newest row:

create table order_notes (
  note_id     number generated always as identity primary key,
  order_id    number not null,
  note_text   varchar2(4000),
  created_on  date default sysdate not null
);

The card’s HTML hides the line with an inline style that comes from a computed column, so no JavaScript is needed:

<p class="card-note" style="&NOTE_STYLE.">Note: &LATEST_NOTE.</p>

When NOTE_STYLE is display:none; the line is hidden. When it is NULL, the substitution is empty and the line shows. All the logic is in one column of the region query.

First attempt: the CASE inside the subquery#

select o.order_id,
       o.customer_name,
       (select case when note_text is null then 'display:none;' else '' end
          from (select note_text
                  from order_notes n
                 where n.order_id = o.order_id
                 order by n.created_on desc, n.note_id desc)
         where rownum = 1) as note_style
  from orders o

It looks right: no note text, so hide. It isn’t. For an order that has no row at all in ORDER_NOTES, the line was still visible.

Rule 1: a scalar subquery that finds no rows returns NULL#

The inline view returns zero rows, rownum = 1 matches nothing, and a scalar subquery with no row to return evaluates to NULL. The CASE inside it never runs, because there is no row to run it on. So note_style is NULL, the substitution is empty, and the line stays.

The CASE only handles “a note row exists but its text is empty”. The common case, “no note row”, never reaches it.

Second attempt: wrap it in NVL#

The natural patch is to catch the NULL outside:

nvl((select case when note_text is null then 'display:none;' else '' end
       from (... same inline view ...)
      where rownum = 1), 'display:none;') as note_style

Now orders without notes hide the line. So does every other order, including the ones that have a note. I reverted it.

Rule 2: in Oracle, ‘’ is NULL#

In Oracle, a zero-length VARCHAR2 is NULL. So else '' is exactly the same as else null. For an order with a note, the inner CASE returns '', which is NULL, and NVL replaces it with display:none;. The “show” value and the “no row” value had become the same value, NULL, and no outer function can tell them apart.

The fix: test for NULL outside, and drop the ELSE#

Let the scalar subquery return the note itself, and decide outside it:

case
  when (select note_text
          from (select note_text
                  from order_notes n
                 where n.order_id = o.order_id
                 order by n.created_on desc, n.note_id desc)
         where rownum = 1) is null
  then 'display:none;'
end as note_style

There is no ELSE. The “show” case is the implicit NULL, which is what the template needs: an empty substitution. And because I never write '', rule 2 can’t bite.

If you only need to know whether a note exists, exists says it more directly:

case when not exists (select 1 from order_notes n
                       where n.order_id = o.order_id
                         and n.note_text is not null)
     then 'display:none;'
end as note_style

I kept the scalar-subquery version in the real report because the same query also shows the latest note’s text, so the lookup was already there.

Rules I now follow#

• A scalar subquery can always return NULL, even when you “know” there is a row. Handle NULL on the outside, not in a CASE inside it.
• Never write else '' in Oracle if anything later will test the result for NULL or wrap it in NVL. Leave the ELSE out and let the NULL mean something on purpose, or return a real value like 'display:block;'.
• Build the truth table before you ship. Three rows (value, empty value, no row) would have caught both bugs before a user did.

Both rules are database behaviour, not APEX behaviour: they apply to any Oracle SQL, in any APEX version.