Skip to Main Content
  • Questions

  • I want to understand this decode function which applied in this query

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Baizeed.

Asked: July 03, 2018 - 10:03 am UTC

Last updated: July 06, 2018 - 9:22 am UTC

Version: 11g

Viewed 1000+ times

You Asked 

 SELECT   a.syrmn, a.orgcode, SUBSTR (a.accode, 1, 3) || '0000000' caccode,
            SUBSTR (a.accode, 1, 6) || '0000' saccode, accode,
            DECODE (SIGN (SUM (DECODE (a.sntb, 'D', a.samnt, -a.samnt))),
                    1, SUM (DECODE (a.sntb, 'D', a.samnt, -a.samnt)),
                    0
                   ) debitbal,
            DECODE (SIGN (SUM (DECODE (a.sntb, 'C', a.samnt, -a.samnt))),
                    1, SUM (DECODE (a.sntb, 'C', a.samnt, -a.samnt)),
                    0
                   ) creditbal
       FROM slbal a
   GROUP BY a.syrmn,
            a.orgcode,
            SUBSTR (a.accode, 1, 3) || '0000000',
            SUBSTR (a.accode, 1, 6) || '000000',
            accode;

and Chris said...

Looks like it's doing a DIY pivot.

The debitbal expression is:

- Checking if sntb = D. If it is returning samnt. If not, negating samnt
- Taking the sign of this. This returns 1 for positive values, 0 for zero, and -1 for negative.
- If sign returns 1 (the value is positive) it returns the positive balance. Otherwise 0

Creditbal is the reverse of this.

The sign function is unnecessary complexity if you ask me. But then, the whole statement is!

It's much easier to do something like this with the in-built pivot:

with trans as (
  select 1 acct, 'D' tp, 100 val from dual union all
  select 1 acct, 'D' tp, 0.99 from dual union all
  select 1 acct, 'C' tp, 49.99 from dual union all
  select 1 acct, 'C' tp, 5 from dual 
)
  select * 
  from   trans
  pivot (
    sum ( val ) for tp in ( 'D', 'C')
  );

ACCT   'D'      'C'     
     1   100.99   54.99

Rating

  (4 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

questioners fld list doesn't match group by list

Missy Medlin, July 05, 2018 - 6:09 pm UTC

I've done my share of DIY pivots in my day, that's for sure. But just commenting here that the original SQLs field list and group-by list don't match. That's a bug right there in whatever source code this is.

SUBSTR(a.accode, 1, 6) || '000000' saccode, -- '0000' saccode, -- changed to 6 zeros

Chris Saxon
July 06, 2018 - 9:21 am UTC

Yes, good catch.

trying out original code

Missy Medlin, July 05, 2018 - 8:08 pm UTC

It looks like they are saying "a negative debit is really a credit, and a negative credit is really a debit" - so that the totals columns for Credit and Debit are always displayed positive. I think...

-- debit positive total of -2
insert into slbal values ('DEBIT_POS_TOTAL','ABB','ACCXCD','D',3);
insert into slbal values ('DEBIT_POS_TOTAL','ABB','ACCXCD','D',-1);
-- debit negative total of -2
insert into slbal values ('DEBIT_NEG_TOTAL','ABB','ACCXCD','D',-3);
insert into slbal values ('DEBIT_NEG_TOTAL','ABB','ACCXCD','D',1);
-- credit positive total of 2
insert into slbal values ('CREDIT_POS_TOTAL','ABB','ACCXCD','C',3);
insert into slbal values ('CREDIT_POS_TOTAL','ABB','ACCXCD','C',-1);
-- credit negative total
insert into slbal values ('CREDIT_NEG_TOTAL','ABB','ACCXCD','C',-3);
insert into slbal values ('CREDIT_NEG_TOTAL','ABB','ACCXCD','C',1);
commit;

Chris Saxon
July 06, 2018 - 9:22 am UTC

Could be... we'd need to see some sample data from the OP!

Chuck Jolley, July 11, 2018 - 1:52 am UTC

Some financial systems run on journals that have 'C' and 'D' plus an amount.
Others use separate credit and debit columns.
This code is for transferring data from the first to the second.
Getting rid of the negative amounts may, or may not, be actually necessary.
Anyway, that's definitely what this code is for.

Baizeed, August 01, 2018 - 5:04 pm UTC


more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.