I'm looking for an NVL2() substitute. Someone has suggested using case,
but I've always seen case in scripts, not in a single statement. For
example, NVL2() can be used in a single SQL statement like:

SELECT g3e_fid, hs_prefix, hs_number, hs_suffix,
NVL2 (hs_prefix,
hs_prefix || '-' || hs_number || hs_suffix,
hs_number || hs_suffix
) addr_number
FROM parcel_labels_o1

Anyone have an example of using case in a single statement?

"Sean Nakasone" <seannakasone (AT) yahoo (DOT) com> wrote

How about using COALESCE:

SELECT g3e_fid, hs_prefix, hs_number, hs_suffix,
COALESCE (hs_prefix || '-' || hs_number || hs_suffix,
hs_number || hs_suffix
) addr_number
FROM parcel_labels_o1;

The CASE version would be:

SELECT g3e_fid, hs_prefix, hs_number, hs_suffix,
CASE WHEN hs_prefix IS NOT NULL
THEN hs_prefix || '-' || hs_number || hs_suffix
ELSE hs_number || hs_suffix
END addr_number
FROM parcel_labels_o1;

Both versions are ANSI/ISO SQL.

--
David Portas