As a novice, I wonder if it would be beneficial to have a built-in EQUIVALENT(…, …) function, as a more intuitive/succinct version of DECODE (for comparing values and handling nulls).
Oracle: Coding Around NULL Values
Decode is cool, because when comparing two different columns, it considers two NULL values to be equivalent.
In my experience, decode can be a bit verbose.
--Find values that don't match lookups.
where
decode(a.event_status, b.code, 'SAME', 'DIFFERENT') = 'DIFFERENT'
or decode(a.asset_class, c.code, 'SAME', 'DIFFERENT') = 'DIFFERENT'
or decode(a.strategy, d.code, 'SAME', 'DIFFERENT') = 'DIFFERENT'
or decode(a.activity, e.domain_code, 'SAME', 'DIFFERENT') = 'DIFFERENT'
or decode(a.project_lead, f.code, 'SAME', 'DIFFERENT') = 'DIFFERENT'
or decode(a.side, g.code, 'SAME', 'DIFFERENT') = 'DIFFERENT'
...might be better as...
where
equivalent(a.event_status, b.code ) = 'FALSE'
or equivalent(a.asset_class, c.code ) = 'FALSE'
or equivalent(a.strategy, d.code ) = 'FALSE'
or equivalent(a.activity, e.domain_code) = 'FALSE'
or equivalent(a.project_lead, f.code ) = 'FALSE'
or equivalent(a.side, g.code ) = 'FALSE'
If "equivalent" isn't quite the right word, or too long, then would "same" be better? SAME(…, …) Or something else?
Thoughts? Would there be enough of a benefit to warrant a new function?
I know using DECODE isn't difficult for the experts in the room. But for the rest of us, DECODE just seems a bit unintuitive and clunky for a world-class database like Oracle.