decode関数テクニック

最近のプロジェクトでは、プロジェクト/非プロジェクトの工数を統計する必要があります.表には工数フィールドがあります.また、プロジェクトかどうかとステータスフィールドが2つあります.タイプ別に各種の工数を統計する必要があります.case whenでネストしようと思ったが、成功しなかった.その後、フォワードはほぼ2分でdecodeで問題を解決し、強くなった.
表の構造は次のとおりです.

-- Create table
create table T21_WORKTIME
(
  WORKDAY        VARCHAR2(10),
  USERNAME       VARCHAR2(12),
  WORKCOUNTS     NUMBER,
  ISPROJECT      VARCHAR2(1),
  STATUS         VARCHAR2(2),
  MYIDEA         VARCHAR2(512),
  DIRECTORIDEA   VARCHAR2(512),
  BRANCHIDEA     VARCHAR2(512),
  MANAGERIDEA    VARCHAR2(512),
  DIRECTORCOUNTS NUMBER,
  MANAGERCONTS   NUMBER,
  DEPTID         VARCHAR2(12),
  PROJECTID      VARCHAR2(12),
  ISADD          CHAR(1),
  WORKTIME_ID    VARCHAR2(22) not null,
  TYPE           VARCHAR2(2),
  D_CREATOR      VARCHAR2(32),
  D_CREATEDATE   CHAR(19),
  D_MODIFIER     VARCHAR2(32),
  D_MODIFYDATE   CHAR(19)
)
tablespace TSDAT02
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );
-- Add comments to the table 
comment on table T21_WORKTIME
  is '       ';
-- Add comments to the columns 
comment on column T21_WORKTIME.WORKDAY
  is '  ( )';
comment on column T21_WORKTIME.USERNAME
  is '    ';
comment on column T21_WORKTIME.WORKCOUNTS
  is '    ';
comment on column T21_WORKTIME.ISPROJECT
  is '      ';
comment on column T21_WORKTIME.STATUS
  is '    ';
comment on column T21_WORKTIME.MYIDEA
  is '     ';
comment on column T21_WORKTIME.DIRECTORIDEA
  is '      ';
comment on column T21_WORKTIME.BRANCHIDEA
  is '      ';
comment on column T21_WORKTIME.MANAGERIDEA
  is '     ';
comment on column T21_WORKTIME.DIRECTORCOUNTS
  is '       ';
comment on column T21_WORKTIME.MANAGERCONTS
  is '      ';
comment on column T21_WORKTIME.DEPTID
  is '    ';
comment on column T21_WORKTIME.PROJECTID
  is '    ';
comment on column T21_WORKTIME.ISADD
  is '    ';
comment on column T21_WORKTIME.WORKTIME_ID
  is '    ';
comment on column T21_WORKTIME.TYPE
  is '       ';
comment on column T21_WORKTIME.D_CREATOR
  is '   ';
comment on column T21_WORKTIME.D_CREATEDATE
  is '    ';
comment on column T21_WORKTIME.D_MODIFIER
  is '   ';
comment on column T21_WORKTIME.D_MODIFYDATE
  is '    ';
-- Create/Recreate primary, unique and foreign key constraints 
alter table T21_WORKTIME
  add constraint PK_T21_WORKTIME primary key (WORKTIME_ID)
  using index 
  tablespace TSDAT02
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );

 
 
 

select s.username as username,
       substr(s.workday, 0, 7) as month,
       sum(decode(isadd || s.isproject, '01', nvl(s.workcounts, 0), 0)),
       sum(decode(isadd || s.isproject, '11', nvl(s.workcounts, 0), 0)),
       sum(decode(isadd || s.isproject, '00', nvl(s.workcounts, 0), 0)),
       sum(decode(isadd || s.isproject, '10', nvl(s.workcounts, 0), 0)),
       sum(s.workcounts),
       sum(s.directorcounts),
       sum(s.managerconts),
       sum(s.workcounts + s.directorcounts + s.managerconts)
  from T21_Worktime s
 where 1 = 1
   and s.username = '1776'
   and s.workday >= '2012-12-01'
   and s.workday <= '2012-12-31'
 group by s.username, substr(s.workday, 0, 7);