Skip to Main Content
Forums

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Issue with group by in Create table as select

RAMEL OlivierMay 10 2018 — edited May 14 2018

I have an issue very strange with my Query on Oracle. I don't have the same result for a select query if I'm creating a table from the result of this query or not.

1/ select query only :

SELECT PRJ_OBJECT_ID,SUBSTR(YEAR,1,4) YEAR,'ETC' UNIT, SUM(JAN) JAN,SUM(FEB) FEB,SUM(MAR) MAR,SUM(APR) APR,SUM(MAY) MAY,SUM(JUN) JUN, SUM(JUL) JUL,SUM(AUG) AUG,SUM(SEP) SEP,SUM(OCTO) OCT,SUM(NOV) NOV,SUM(DECE) DECE,sum(total) totalFROM( SELECT PRJ_OBJECT_ID,TO_CHAR(SLICE_DATE,'YYYYMM') YEAR, DECODE(TO_CHAR(SLICE_DATE,'YYYYMM'),SUBSTR(TO_CHAR(SLICE_DATE,'YYYYMM'),1,4)||TO_CHAR('01'),SUM(SLICE),0) JAN, DECODE(TO_CHAR(SLICE_DATE,'YYYYMM'),SUBSTR(TO_CHAR(SLICE_DATE,'YYYYMM'),1,4)||TO_CHAR('02'),SUM(SLICE),0) FEB, DECODE(TO_CHAR(SLICE_DATE,'YYYYMM'),SUBSTR(TO_CHAR(SLICE_DATE,'YYYYMM'),1,4)||TO_CHAR('03'),SUM(SLICE),0) MAR, DECODE(TO_CHAR(SLICE_DATE,'YYYYMM'),SUBSTR(TO_CHAR(SLICE_DATE,'YYYYMM'),1,4)||TO_CHAR('04'),SUM(SLICE),0) APR, DECODE(TO_CHAR(SLICE_DATE,'YYYYMM'),SUBSTR(TO_CHAR(SLICE_DATE,'YYYYMM'),1,4)||TO_CHAR('05'),SUM(SLICE),0) MAY, DECODE(TO_CHAR(SLICE_DATE,'YYYYMM'),SUBSTR(TO_CHAR(SLICE_DATE,'YYYYMM'),1,4)||TO_CHAR('06'),SUM(SLICE),0) JUN, DECODE(TO_CHAR(SLICE_DATE,'YYYYMM'),SUBSTR(TO_CHAR(SLICE_DATE,'YYYYMM'),1,4)||TO_CHAR('07'),SUM(SLICE),0) JUL, DECODE(TO_CHAR(SLICE_DATE,'YYYYMM'),SUBSTR(TO_CHAR(SLICE_DATE,'YYYYMM'),1,4)||TO_CHAR('08'),SUM(SLICE),0) AUG, DECODE(TO_CHAR(SLICE_DATE,'YYYYMM'),SUBSTR(TO_CHAR(SLICE_DATE,'YYYYMM'),1,4)||TO_CHAR('09'),SUM(SLICE),0) SEP, DECODE(TO_CHAR(SLICE_DATE,'YYYYMM'),SUBSTR(TO_CHAR(SLICE_DATE,'YYYYMM'),1,4)||TO_CHAR('10'),SUM(SLICE),0) OCTO, DECODE(TO_CHAR(SLICE_DATE,'YYYYMM'),SUBSTR(TO_CHAR(SLICE_DATE,'YYYYMM'),1,4)||TO_CHAR('11'),SUM(SLICE),0) NOV, DECODE(TO_CHAR(SLICE_DATE,'YYYYMM'),SUBSTR(TO_CHAR(SLICE_DATE,'YYYYMM'),1,4)||TO_CHAR('12'),SUM(SLICE),0) DECE, NVL(SUM(SLICE),0) TOTAL FROM PRJ_BLB_SLICES_M_ETC INNER JOIN PRASSIGNMENT PA ON PA.PRID=PRJ_OBJECT_ID WHERE SLICE_REQUEST_ID=113 AND TO_CHAR(SLICE_DATE, 'YYYY') >= TO_CHAR(ADD_MONTHS(SYSDATE,-3), 'YYYY') and PRJ_OBJECT_ID = 5002239 group by PRJ_OBJECT_ID,TO_CHAR(SLICE_DATE,'YYYYMM') ) group by

PRJ_OBJECT_ID,SUBSTR(YEAR,1,4)

The result : I have only one line

resultatquery.PNG

2/ Create table from the result

CREATE TABLE "U_PR4_1"."MI_ETC_TEMP" as ( SELECT PRJ_OBJECT_ID,SUBSTR(YEAR,1,4) YEAR,'ETC' UNIT, SUM(JAN) JAN,SUM(FEB) FEB,SUM(MAR) MAR,SUM(APR) APR,SUM(MAY) MAY,SUM(JUN) JUN, SUM(JUL) JUL,SUM(AUG) AUG,SUM(SEP) SEP,SUM(OCTO) OCT,SUM(NOV) NOV,SUM(DECE) DECE,sum(total) totalFROM( SELECT PRJ_OBJECT_ID,TO_CHAR(SLICE_DATE,'YYYYMM') YEAR, DECODE(TO_CHAR(SLICE_DATE,'YYYYMM'),SUBSTR(TO_CHAR(SLICE_DATE,'YYYYMM'),1,4)||TO_CHAR('01'),SUM(SLICE),0) JAN, DECODE(TO_CHAR(SLICE_DATE,'YYYYMM'),SUBSTR(TO_CHAR(SLICE_DATE,'YYYYMM'),1,4)||TO_CHAR('02'),SUM(SLICE),0) FEB, DECODE(TO_CHAR(SLICE_DATE,'YYYYMM'),SUBSTR(TO_CHAR(SLICE_DATE,'YYYYMM'),1,4)||TO_CHAR('03'),SUM(SLICE),0) MAR, DECODE(TO_CHAR(SLICE_DATE,'YYYYMM'),SUBSTR(TO_CHAR(SLICE_DATE,'YYYYMM'),1,4)||TO_CHAR('04'),SUM(SLICE),0) APR, DECODE(TO_CHAR(SLICE_DATE,'YYYYMM'),SUBSTR(TO_CHAR(SLICE_DATE,'YYYYMM'),1,4)||TO_CHAR('05'),SUM(SLICE),0) MAY, DECODE(TO_CHAR(SLICE_DATE,'YYYYMM'),SUBSTR(TO_CHAR(SLICE_DATE,'YYYYMM'),1,4)||TO_CHAR('06'),SUM(SLICE),0) JUN, DECODE(TO_CHAR(SLICE_DATE,'YYYYMM'),SUBSTR(TO_CHAR(SLICE_DATE,'YYYYMM'),1,4)||TO_CHAR('07'),SUM(SLICE),0) JUL, DECODE(TO_CHAR(SLICE_DATE,'YYYYMM'),SUBSTR(TO_CHAR(SLICE_DATE,'YYYYMM'),1,4)||TO_CHAR('08'),SUM(SLICE),0) AUG, DECODE(TO_CHAR(SLICE_DATE,'YYYYMM'),SUBSTR(TO_CHAR(SLICE_DATE,'YYYYMM'),1,4)||TO_CHAR('09'),SUM(SLICE),0) SEP, DECODE(TO_CHAR(SLICE_DATE,'YYYYMM'),SUBSTR(TO_CHAR(SLICE_DATE,'YYYYMM'),1,4)||TO_CHAR('10'),SUM(SLICE),0) OCTO, DECODE(TO_CHAR(SLICE_DATE,'YYYYMM'),SUBSTR(TO_CHAR(SLICE_DATE,'YYYYMM'),1,4)||TO_CHAR('11'),SUM(SLICE),0) NOV, DECODE(TO_CHAR(SLICE_DATE,'YYYYMM'),SUBSTR(TO_CHAR(SLICE_DATE,'YYYYMM'),1,4)||TO_CHAR('12'),SUM(SLICE),0) DECE, NVL(SUM(SLICE),0) TOTAL FROM PRJ_BLB_SLICES_M_ETC INNER JOIN PRASSIGNMENT PA ON PA.PRID=PRJ_OBJECT_ID WHERE SLICE_REQUEST_ID=113 AND TO_CHAR(SLICE_DATE, 'YYYY') >= TO_CHAR(ADD_MONTHS(SYSDATE,-3), 'YYYY') and PRJ_OBJECT_ID = 5002239 group by PRJ_OBJECT_ID,TO_CHAR(SLICE_DATE,'YYYYMM') ) group by PRJ_OBJECT_ID,SUBSTR(YEAR,1,4))

Result in the table : one line by month

result table.JPG

Any idea ?

thanks for your help,
This post has been answered by Paulzip on May 10 2018
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jun 11 2018
Added on May 10 2018
13 comments
369 views