OK, here's an answer to part of your observations...
'string' || NULL does indeed return NULL, and it should, according to
SQL92 rules. NULLification is consistent for all types, and *any*
operator with a NULL operand should return NULL as a result. NULL
means "has no value".
Now, as it turns out Oracle equates '' - the empty string - with NULL,
the value-with-no-value. This is *wrong* and indeed, Oracle's own
documentation claims they may fix it someday and that code shouldn't
rely on this bug.
Unfortunately, ACS Classic (the Oracle version) does depend on this in
many places. It treats NULL and '' as synonyms, i.e. varchar fields
aren't given an explicit "default ''" clause if they're going to be
used as operands to the concatenation operator, or alternatively don't
check via "case" as you suggest doing.
This works because in Oracle NULL || 'abc' returns 'abc', which makes
as much sense as "1 + NULL" returning "1", or "1 > NULL" returning
"true" (or "false", for that matter).
Grrr...these are hard to spot "by inspection" when porting queries.
We should try to implement a consistent approach to the problem for
the ACS 4.0 port. It's unrealistic to expect aD to avoid this
'feature', because Oracle gives absolutely no help via warnings, etc,
when you take advantage of this mistreatment of '' as NULL.
