Home » SQL & PL/SQL » SQL & PL/SQL » PIVOT - sum( case statement) when joining giving wrong results (Oracle 10g)
PIVOT - sum( case statement) when joining giving wrong results [message #435153] Mon, 14 December 2009 13:43 Go to next message
minig
Messages: 3
Registered: December 2009
Junior Member
Hi,
I am trying to PIVOT on 'ACT_OR_PROJ' values 'A' for actual and 'P' Projected, for VALUES REG_HRS,PROJ_HRS,ACT_AMT,PROJ_AMT

The following sql statement when joining to a second table is giving the resulting 'SUM' wrong. If there are 2 rows of data in the fact table, it's duplicating for(REG_HRS,PROJ_HRS,ACT_AMT,PROJ_AMT
is duplicating for CRANE)

I can only use 'sql' to get the results and not pl/sql. Please let me know how this can be resolved.


select DW_EMP.LASTNAME as LASTNAME,
DW_EMP.JOB_TYPE as JOB_TYPE,
DW_PAYROLL_FACT.FUND_GRP as FUND_GROUP,
DW_PAYROLL_FACT.DISTRICT_CD as DISTRICT_CD,
DW_PAYROLL_FACT.EMPLOYEE_ID as EMPLOYEE_ID,
DW_PAYROLL_FACT.PAY_PERIOD_ID as PAY_PERIOD_ID,
DW_PAYROLL_FACT.ACCOUNT_CLASS_ID as ACCOUNT_CLASS_ID
,sum(case when DW_PAYROLL_FACT.ACT_OR_PROJ = 'A' then reg_hrs end) reg_hrs
,sum(case when DW_PAYROLL_FACT.ACT_OR_PROJ = 'P' then reg_hrs end) proj_hrs
,sum(case when DW_PAYROLL_FACT.ACT_OR_PROJ = 'A' then total_amt end) act_amt
,sum(case when DW_PAYROLL_FACT.ACT_OR_PROJ = 'P' then total_amt end) proj_amt
from DW_PAYROLL_FACT, DW_EMP
where
DW_EMP.employee_id =DW_PAYROLL_FACT.EMPLOYEE_ID and
DW_PAYROLL_FACT.employee_id IN ('39201','39433') and DW_PAYROLL_FACT.pay_period_id='1084'
group by
DW_EMP.LASTNAME,
DW_EMP.JOB_TYPE,
DW_PAYROLL_FACT.FUND_GRP,
DW_PAYROLL_FACT.DISTRICT_CD,
DW_PAYROLL_FACT.EMPLOYEE_ID,
DW_PAYROLL_FACT.PAY_PERIOD_ID,
DW_PAYROLL_FACT.ACCOUNT_CLASS_ID

LASTNAME,JOB_TYPE,FUND_GROUP,DISTRICT_CD,EMPLOYEE_ID,PAY_PERIOD_ID,ACCOUNT_CLASS_ID,REG_HRS,PROJ_HRS,ACT_AMT,PROJ_AMT
CRANE,ATTORNEY,B,GAM,39201,1084,1488,160,160,10539.2,10539.2
RUSHING,PARALEGAL,B,GAM,39433,1084,1956,80,80,2583.06,1787.19


Source Data:
EMPLOYEE_ID,PAY_PERIOD_ID,ACCOUNT_CLASS_ID,ACT_OR_PROJ,COMPENSATION_TYPE_ID,TOTAL_AMT,REG_HRS

39201, 1084, 1488, P, 1000, 5269.6, 80
39201, 1084, 1488, A, 1000, 5269.6, 80
39433, 1084, 1956, P, 1000, 1787.19, 80
39433, 1084, 1956, A, 1025, 795.87,
39433, 1084, 1956, A, 1000, 1787.19, 80
Re: PIVOT - sum( case statement) when joining giving wrong results [message #435155 is a reply to message #435153] Mon, 14 December 2009 13:53 Go to previous messageGo to next message
BlackSwan
Messages: 24912
Registered: January 2009
Senior Member
It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/
It would be helpful if you provided DDL for tables involved.
It would be helpful if you provided DML for test data.
It would be helpful if you provided expected/desired results & a detailed explanation how & why the test data gets transformed or organized.

SELECT   dw_emp.lastname                  AS lastname,
         dw_emp.job_type                  AS job_type,
         dw_payroll_fact.fund_grp         AS fund_group,
         dw_payroll_fact.district_cd      AS district_cd,
         dw_payroll_fact.employee_id      AS employee_id,
         dw_payroll_fact.pay_period_id    AS pay_period_id,
         dw_payroll_fact.account_class_id AS account_class_id,
         Sum(CASE
               WHEN dw_payroll_fact.act_or_proj = 'A'
               THEN reg_hrs
             END) reg_hrs,
         Sum(CASE
               WHEN dw_payroll_fact.act_or_proj = 'P'
               THEN reg_hrs
             END) proj_hrs,
         Sum(CASE
               WHEN dw_payroll_fact.act_or_proj = 'A'
               THEN total_amt
             END) act_amt,
         Sum(CASE
               WHEN dw_payroll_fact.act_or_proj = 'P'
               THEN total_amt
             END) proj_amt
FROM     dw_payroll_fact,
         dw_emp
WHERE    dw_emp.employee_id = dw_payroll_fact.employee_id
         AND dw_payroll_fact.employee_id IN ('39201','39433')
         AND dw_payroll_fact.pay_period_id = '1084'
GROUP BY dw_emp.lastname,
         dw_emp.job_type,
         dw_payroll_fact.fund_grp,
         dw_payroll_fact.district_cd,
         dw_payroll_fact.employee_id,
         dw_payroll_fact.pay_period_id,
         dw_payroll_fact.account_class_id 
icon12.gif  Re: PIVOT - sum( case statement) when joining giving wrong results [message #435158 is a reply to message #435153] Mon, 14 December 2009 14:15 Go to previous messageGo to next message
LKBrwn_DBA
Messages: 487
Registered: July 2003
Location: WPB, FL
Senior Member
To avoid NULLs, try this:

SELECT -- etc ---
        Sum(CASE
               WHEN dw_payroll_fact.act_or_proj = 'A'
               THEN reg_hrs ELSE 0
             END) reg_hrs,
-- etc --

Shocked
Re: PIVOT - sum( case statement) when joining giving wrong results [message #435159 is a reply to message #435158] Mon, 14 December 2009 14:30 Go to previous messageGo to next message
Michel Cadot
Messages: 63810
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SUM (as all aggregates functions) does not care about NULL.
Once more a wrong (or useless or ...) answer with an irrelevant smiley or do you show you are shocked by the silliness of the answers you give?

Regards
Michel
icon10.gif  Re: PIVOT - sum( case statement) when joining giving wrong results [message #435161 is a reply to message #435159] Mon, 14 December 2009 15:20 Go to previous messageGo to next message
LKBrwn_DBA
Messages: 487
Registered: July 2003
Location: WPB, FL
Senior Member
Michel Cadot wrote on Mon, 14 December 2009 15:30
SUM (as all aggregates functions) does not care about NULL.
Once more a wrong (or useless or ...) answer with an irrelevant smiley or do you show you are shocked by the silliness of the answers you give?

Regards
Michel

If the "smilies" annoy you, it may be a sign of stress...maybe you need to take a rest...or take a vacation Michel!

With respect to the null's, it's just that I remembered that it was not always so. I myself many years ago had the same issue of incorrect sums and it was fixed by either using the NVL() function or defaulting to zero -- But maybe that was in the 8i pre-history?

Razz
Re: PIVOT - sum( case statement) when joining giving wrong results [message #435164 is a reply to message #435161] Mon, 14 December 2009 15:43 Go to previous messageGo to next message
minig
Messages: 3
Registered: December 2009
Junior Member
Hi,
I tried the NVl() and also defaulting the values to zero, it still adds up the rows where ever there are multiple lines.
Thanks,
MiniG
Re: PIVOT - sum( case statement) when joining giving wrong results [message #435165 is a reply to message #435164] Mon, 14 December 2009 16:35 Go to previous messageGo to next message
minig
Messages: 3
Registered: December 2009
Junior Member
DDL for test tables:
CREATE TABLE DW_EMP
(
  DISTRICT_CD                VARCHAR2(5 BYTE),
  PAY_PERIOD_ID              NUMBER(10),
  EMPLOYEE_ID                NUMBER(10),
  LASTNAME                   VARCHAR2(75 BYTE)
)

INSERT INTO DW_EMP VALUES ('DC',1084,39201,'CRANE');
INSERT INTO DW_EMP VALUES ('DC',1085,39433,'RUSHING');
INSERT INTO DW_EMP VALUES ('DC',1085,39201,'CRANE');

commit;


CREATE TABLE DW_PAYROLL_FACT
(
  DISTRICT_CD           VARCHAR2(5 BYTE)        NOT NULL,
  EMPLOYEE_ID           NUMBER(10)              NOT NULL,
  PAY_PERIOD_ID         NUMBER(10)              NOT NULL,
  ACT_OR_PROJ           VARCHAR2(1 BYTE)        NOT NULL,
  ACCOUNT_CLASS_ID      NUMBER(10)              NOT NULL,
  COMPENSATION_TYPE_ID  NUMBER(10)              NOT NULL,
  TOTAL_AMT             NUMBER(7,2)             NOT NULL,
  REG_HRS               NUMBER(6,2),
  FUND_GRP              VARCHAR2(1 BYTE)
)

INSERT INTO DW_PAYROLL_FACT VALUES ('DC','39201', 1084,  'P',  1488, 1000,  5269.6, 80,'B');
INSERT INTO DW_PAYROLL_FACT VALUES('DC','39201', 1084,   'A',   1488, 1000,  5269.6,    80,'B');
INSERT INTO DW_PAYROLL_FACT VALUES('DC','39433', 1084,   'P',  1956,1000,  1787.19,    80,'B');
INSERT INTO DW_PAYROLL_FACT VALUES('DC','39433', 1084,   'A', 1956, 1025,  795.87,0,'B');
INSERT INTO DW_PAYROLL_FACT VALUES('DC','39433', 1084,   'A', 1956, 1000,  1787.19,    80,'B');


pivot data for ACTUAL or PROJECTED
      select DW_EMP.LASTNAME as LASTNAME, 
      DW_PAYROLL_FACT.FUND_GRP  as  FUND_GROUP,
       DW_PAYROLL_FACT.DISTRICT_CD  as  DISTRICT_CD,
       DW_PAYROLL_FACT.EMPLOYEE_ID  as  EMPLOYEE_ID,
       DW_PAYROLL_FACT.PAY_PERIOD_ID  as  PAY_PERIOD_ID,
       DW_PAYROLL_FACT.ACCOUNT_CLASS_ID  as  ACCOUNT_CLASS_ID
,sum(case when  DW_PAYROLL_FACT.ACT_OR_PROJ = 'A'     then reg_hrs end) reg_hrs
      ,sum(case when  DW_PAYROLL_FACT.ACT_OR_PROJ = 'P'     then reg_hrs end) proj_hrs
      ,sum(case when  DW_PAYROLL_FACT.ACT_OR_PROJ = 'A'     then total_amt end) act_amt
      ,sum(case when  DW_PAYROLL_FACT.ACT_OR_PROJ = 'P'      then total_amt  end) proj_amt
      from  DW_PAYROLL_FACT, DW_EMP
       where 
       DW_EMP.employee_id =DW_PAYROLL_FACT.EMPLOYEE_ID and 
       DW_PAYROLL_FACT.employee_id IN ('39201','39433') and DW_PAYROLL_FACT.pay_period_id='1084'
        group by 
       DW_EMP.LASTNAME,
       DW_PAYROLL_FACT.FUND_GRP,
      DW_PAYROLL_FACT.DISTRICT_CD,
       DW_PAYROLL_FACT.EMPLOYEE_ID,
       DW_PAYROLL_FACT.PAY_PERIOD_ID,
      DW_PAYROLL_FACT.ACCOUNT_CLASS_ID



RETURNING RESULT from above sql:
CRANE,B,DC,39201,1084,1488,160,160,10539.2,10539.2

RUSHING,B,DC,39433,1084,1956,80,80,2583.06,1787.19

EXPECTED RESULT
LASTNAME,FUND_GROUP,DISTRICT_CD,EMPLOYEE_ID,PAY_PERIOD_ID,ACCOUNT_CLASS_ID,REG_HRS,PROJ_HRS,ACT_AMT,PROJ_AMT
CRANE,B,DC,39201,1084,1488,80,80,5269.6,5269.6

RUSHING,B,DC,39433,1084,1956,80,80,2583.06,1787.19
Re: PIVOT - sum( case statement) when joining giving wrong results [message #435166 is a reply to message #435165] Mon, 14 December 2009 18:28 Go to previous messageGo to next message
rishg
Messages: 9
Registered: March 2008
Junior Member
You need to sum first before pivoting and then apply max to collapse the rows after pivoting.

 select DW_EMP.LASTNAME as LASTNAME, 
      DW_PAYROLL_FACT.FUND_GRP  as  FUND_GROUP,
       DW_PAYROLL_FACT.DISTRICT_CD  as  DISTRICT_CD,
       DW_PAYROLL_FACT.EMPLOYEE_ID  as  EMPLOYEE_ID,
       DW_PAYROLL_FACT.PAY_PERIOD_ID  as  PAY_PERIOD_ID,
       DW_PAYROLL_FACT.ACCOUNT_CLASS_ID  as  ACCOUNT_CLASS_ID
       ,[b]max([/b]case when DW_PAYROLL_FACT.ACT_OR_PROJ = 'A' then reg_hrs end) reg_hrs
,[b]max([/b]case when DW_PAYROLL_FACT.ACT_OR_PROJ = 'P' then reg_hrs end) proj_hrs
,[b]max([/b]case when DW_PAYROLL_FACT.ACT_OR_PROJ = 'A' then total_amt end) act_amt
,[b]max([/b]case when DW_PAYROLL_FACT.ACT_OR_PROJ = 'P' then total_amt end) proj_amt
      from   DW_EMP,
/* calculate the sums in the inner query. Modify the partition by clause as needed */
           [b]( select DISTRICT_CD , EMPLOYEE_ID,  PAY_PERIOD_ID, ACT_OR_PROJ, ACCOUNT_CLASS_ID , COMPENSATION_TYPE_ID,
             sum(TOTAL_AMT) over (partition by employee_id, pay_period_id, act_or_proj) as total_amt ,
             sum(REG_HRS) over (partition by employee_id, pay_period_id, act_or_proj) as reg_hrs, FUND_GRP from dw_payroll_fact
            ) dw_payroll_fact[/b]
       where 
       DW_EMP.employee_id =DW_PAYROLL_FACT.EMPLOYEE_ID and 
       DW_PAYROLL_FACT.employee_id IN ('39201','39433') and DW_PAYROLL_FACT.pay_period_id='1084'
        group by 
       DW_EMP.LASTNAME,
       DW_PAYROLL_FACT.FUND_GRP,
      DW_PAYROLL_FACT.DISTRICT_CD,
       DW_PAYROLL_FACT.EMPLOYEE_ID,
       DW_PAYROLL_FACT.PAY_PERIOD_ID,
      DW_PAYROLL_FACT.ACCOUNT_CLASS_ID

[Updated on: Mon, 14 December 2009 18:30]

Report message to a moderator

Re: PIVOT - sum( case statement) when joining giving wrong results [message #435176 is a reply to message #435161] Tue, 15 December 2009 00:09 Go to previous message
Michel Cadot
Messages: 63810
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
it's just that I remembered that it was not always so

This is wrong , it has ALWAYS been the same behaviour, it is in the standard and it is in Oracle since the very first version.

Smiley means something, it is also named "emoticon", it emphasizes on your emotion, put one when it is relevant (that is when your sentence cannot show your emotion and you owant to show it) and not the wrong one everywhere.

Regards
Michel
Previous Topic: Update multiple rows
Next Topic: Materialized View not refreshing properly
Goto Forum:
  


Current Time: Thu Sep 29 12:25:22 CDT 2016

Total time taken to generate the page: 0.16858 seconds