Forum Stats

  • 3,875,565 Users
  • 2,266,946 Discussions


EQUIVALENT function (instead of DECODE)

User_1871 Member Posts: 247 Red Ribbon

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.
     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...

     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.

2 votes

Active · Last Updated