CODEWITHSUNDEEP
sqloracle-databasesql-order-bycasedecode
Tom
Tom

Reputation: 171

SQL order by DECODE and CASE return different data for the same conditions

I have a select query in ORACLE with order by and I've tried using both DECODE and CASE with the same conditions, but the ordering is done differently and can't understand why. I thought the problem is in NULL(null = null is unknown), but order by CASE gives the expected result.

This is the order by using DECODE: 

order  by decode(seq_diff, abs(seq_diff), seq_diff, null) asc,
          decode(seq_diff, abs(seq_diff), null, seq_diff) desc,
          abs(dist_diff);

This is the order by using CASE:

order by  (case
             when seq_diff = abs(seq_diff) then seq_diff
             else null
           end) asc,
          (case
             when seq_diff = abs(seq_diff) then null
             else seq_diff
           end) desc,
           abs(dist_diff);

Below is the result for DECODE. (I included those decodes in the select to see the values).

For the positive values it sorts by seq_ref asc, but then for the negative values it seems to sort them also asc, not desc. And what is strange at row 28-29 it goes desc and then again asc.

DECODE

The order by CASE sorts the positive values by seq_ref asc and the negative values desc, as expected. This is the result for order by CASE:

CASE

Can somebody please explain why does this happen with decode?

Upvotes: 2

Views: 1542

Answers (1)

Littlefoot
Littlefoot

Reputation: 142968

I asked what the datatype was because sorting looked like sorting strings, not numbers.

Have a look at this. First, only one expression in order by clause:

SQL> with test (seq_diff) as
  2    (select -990 from dual union all
  3     select -610 from dual union all
  4     select -1350 from dual union all
  5     select -1340 from dual
  6    )
  7  select *
  8  from test
  9  order by decode(seq_diff, abs(seq_diff), seq_diff, null);

  SEQ_DIFF
----------
      -990
     -1340
     -1350
      -610

SQL>

How are they sorted? They aren't. According to sample data set, no seq_diff is equal to abs(seq_diff) so ordering moves to null which causes "randomly" ordered values. They aren't sorted at all.

Now, let's add another decode into order by:

SQL> with test (seq_diff) as
  2    (select -990 from dual union all
  3     select -610 from dual union all
  4     select -1350 from dual union all
  5     select -1340 from dual
  6    )
  7  select *
  8  from test
  9  order by decode(seq_diff, abs(seq_diff), seq_diff, null),
 10           decode(seq_diff, abs(seq_diff), null, seq_diff) desc;

  SEQ_DIFF
----------
      -990
      -610
     -1350
     -1340

SQL>

The first decode didn't do anything, as if it doesn't exist so we move on to the second decode. Again, according to data set, no seq_diff is equal to abs(seq_diff), but this time it returns seq_diff. Documentation (as @krokodilko mentioned in their comment) says:

  • The DECODE function returns a value that is the same datatype as the first result in the list.
  • If the first result is NULL, then the return value is converted to VARCHAR2.
    • this is our case, so return value (seq_diff) is converted to varchar2
  • If the first result has a datatype of CHAR, then the return value is converted to VARCHAR2.
  • If no matches are found, the default value is returned.
  • If default is omitted and no matches are found, then NULL is returned.

Once again: our case is the second one:

decode(seq_diff, abs(seq_diff), null, seq_diff)
                                ----
                                the first result is NULL

Therefore, seq_diff is converted to a string and values are sorted as such. Let's check that:

SQL> with test (seq_diff) as
  2    (select -990 from dual union all
  3     select -610 from dual union all
  4     select -1350 from dual union all
  5     select -1340 from dual
  6    )
  7  select *
  8  from test
  9  order by to_char(seq_diff) desc;

  SEQ_DIFF
----------
      -990
      -610
     -1350
     -1340

SQL>

See? The same result as we got with order by decode(seq_diff, abs(seq_diff), null, seq_diff) desc;

The final part of your order by clause is trivial (abs(dist_diff)), I guess there's no need to explain that.

That's why you got strange result with DECODE; actually, that's expected behavior.

Upvotes: 2

Related Questions