Skip to Main Content

Oracle Database Discussions

Announcement

For appeals, questions and feedback, please email oracle-forums_moderators_us@oracle.com

EQUIVALENT function (instead of DECODE)

User_1871Jul 17 2022 — edited Jul 17 2022

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.

Comments
Post Details
Added on Jul 17 2022
0 comments
89 views