Oracle 我想了解此查询中应用的此解码函数

 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;

看起来它在做一个自己动手做的枢轴。

debitbal表达式为:

-检查sntb = D。如果它正在返回samnt。如果没有，否定samnt
-带着这个迹象。这对于正值返回1，对于零返回0，对于负值返回-1。
-如果符号返回1 (值为正)，则返回正余额。否则0

信用余额与此相反。

如果你问我，符号功能是不必要的复杂性。但是，整个声明是!

使用内置的枢轴做这样的事情要容易得多:

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