Hello,
We have noticed that the group by is not working as expected since its bringing back duplicate records … in this case we were expecting only 3 records instead of 6 records. I have tried both Union as well as Union all as a test and its bringing back the exact same result; can someone please have a look at the script below and advise on a possible solution? Thanks
Territory
Global Stat
Global OU
Amt.
CA
Y3170
AAZ
($957,260.10)
CA
Y3170
AAZ
$90,693.00
CA
Y3170
ACZ
($6,714.00)
CA
Y3170
ACZ
$22.50
CA
Y3170
ARZ
($148,460.20)
CA
Y3170
ARZ
$7,666.00
select rpad(’"’||‘CA’||’"’||’,’||’"’||substr(O124951.TERRITORY,1,2)||’"’||’,’||’"’||substr(O124951.GLOBAL_STAT_ACCT,1,5)
||’"’||’,’||’"’||‘2016’||’"’||’,’||’"’||‘03’ ||’"’||’,’||’"’||substr(O124951.GLOBAL_OU,1,3)||’"’||’,’||
to_char (sum (ENTERED_YEAR_TO_DATE_NUM * -1)),80, ’ ') AMT
FROM APPS.IPR_PWC_ACCT_INQ_SUMMARY O124951
WHERE
( O124951.CURRENCY_CODE = ‘STAT’ ) AND ( O124951.ACTUAL_FLAG = ‘A’ ) AND
(O124951.ENTERED_YEAR_TO_DATE_NUM <> 0.00) AND
( O124951.SEGMENT3 in (‘91101’,‘91201’,‘91301’,‘93201’,‘93211’,‘93202’,‘93212’,‘93208’,‘91102’,‘91103’,‘91104’,‘91105’,‘91106’,‘91107’,‘91108’,
‘91322’,‘91323’,‘91324’,‘91325’,‘91326’,‘91327’,‘91328’,‘91329’,‘91330’,‘91331’,‘91332’,‘91333’,‘91334’,‘91335’,‘91336’,‘91337’,‘93203’,‘93204’,‘93205’) )
AND ( O124951.PERIOD_NAME = ‘Sep-15’ )
group by substr(O124951.TERRITORY,1,2) ,substr(O124951.GLOBAL_STAT_ACCT,1,5),substr(O124951.GLOBAL_OU,1,3)
UNION ALL
select rpad(’"’||‘CA’||’"’||’,’||’"’||substr(O124951.TERRITORY,1,2)||’"’||’,’||’"’||substr(O124951.GLOBAL_STAT_ACCT,1,5)
||’"’||’,’||’"’||‘2016’||’"’||’,’||’"’||‘03’ ||’"’||’,’||’"’||substr(O124951.GLOBAL_OU,1,3)||’"’||’,’||
to_char (sum (ENTERED_YEAR_TO_DATE_NUM)),80, ’ ') AMT
FROM APPS.IPR_PWC_ACCT_INQ_SUMMARY O124951
WHERE
( O124951.CURRENCY_CODE = ‘STAT’ ) AND ( O124951.ACTUAL_FLAG = ‘A’ ) AND
(O124951.ENTERED_YEAR_TO_DATE_NUM <> 0.00) AND
( O124951.SEGMENT3 in (‘91501’,‘91502’,‘91503’,‘91504’,‘91505’,‘91506’,‘91507’,‘91508’,‘91509’,‘91510’,‘91511’,‘91512’,‘91513’,‘91514’,‘91537’) )
AND ( O124951.PERIOD_NAME = ‘Sep-15’ )
group by substr(O124951.TERRITORY,1,2) ,substr(O124951.GLOBAL_STAT_ACCT,1,5),substr(O124951.GLOBAL_OU,1,3)