Correction of date conversion format error after Oracle adds a row total

1. Problem description ： In the total of the company's increase statement , There are two ways to increase the total . One way is to add a column after the queried data （ Last blog has written ）; Another way is to add a row total after the queried data . But there will be date conversion errors in the data you query , This is because Oracle Of decode The return value of the built-in function is not of date type . At this time, you need to convert the character format .

1. Wrong date SQL Sum graph

SELECT DECODE (GROUPING( Finished product item no )+GROUPING( Finished product specification )+GROUPING( The repair order )+GROUPING( Stock in quantity )+GROUPING( Stock in No )+GROUPING( Case number )+GROUPING( Operator )+GROUPING( Storage time ),8,' total ', Finished product item no ) Finished product item no ,  
       DECODE (GROUPING( Finished product item no )+GROUPING( Finished product specification )+GROUPING( The repair order )+GROUPING( Stock in quantity )+GROUPING( Stock in No )+GROUPING( Case number )+GROUPING( Operator )+GROUPING( Storage time ),8,'-', Finished product specification ) Finished product specification ,
       DECODE (GROUPING( Finished product item no )+GROUPING( Finished product specification )+GROUPING( The repair order )+GROUPING( Stock in quantity )+GROUPING( Stock in No )+GROUPING( Case number )+GROUPING( Operator )+GROUPING( Storage time ),8,'-', The repair order ) The repair order ,  
       DECODE (GROUPING( Finished product item no )+GROUPING( Finished product specification )+GROUPING( The repair order )+GROUPING( Stock in quantity )+GROUPING( Stock in No )+GROUPING( Case number )+GROUPING( Operator )+GROUPING( Storage time ),8,'-', Stock in quantity ) Stock in quantity ,
       DECODE (GROUPING( Finished product item no )+GROUPING( Finished product specification )+GROUPING( The repair order )+GROUPING( Stock in quantity )+GROUPING( Stock in No )+GROUPING( Case number )+GROUPING( Operator )+GROUPING( Storage time ),8,'-', Case number ) Case number ,
       DECODE (GROUPING( Finished product item no )+GROUPING( Finished product specification )+GROUPING( The repair order )+GROUPING( Stock in quantity )+GROUPING( Stock in No )+GROUPING( Case number )+GROUPING( Operator )+GROUPING( Storage time ),8,'-', Operator ) Operator ,
       DECODE (GROUPING( Finished product item no )+GROUPING( Finished product specification )+GROUPING( The repair order )+GROUPING( Stock in quantity )+GROUPING( Stock in No )+GROUPING( Case number )+GROUPING( Operator )+GROUPING( Storage time ),8,'-', Storage time ,) Storage time ,
       SUM( Stock in quantity ) FROM 
(SELECT B.PART_NO AS  Finished product item no ,B.SPEC1 AS  Finished product specification  ,A.WORK_ORDER AS  The repair order  ,A.QTY AS  Stock in quantity ,A.BILL_NO AS  Stock in No ,A.CONTAINER_NO AS  Case number ,C.EMP_NAME AS  Operator ,A.UPDATE_TIME AS  Storage time 
FROM SAJET.WMS_PRODUCTS_IN A LEFT JOIN SAJET.SYS_PART  B ON A.PART_ID=B.PART_ID 
LEFT JOIN SAJET.SYS_EMP C ON A.UPDATE_USERID=C.EMP_ID
WHERE 1=1
AND A.BILL_NO IN(SELECT LIST_NO FROM SAJET.TBLK3_BILL_RECORD WHERE  ORDER_TYPE IN('1','2') AND ISOK='1')
AND A.WORK_ORDER='WORK014084'
--[AND B.PART_NO=:PARAM1]
--[AND A.WORK_ORDER=:PARAM2]
--[AND A.BILL_NO=:PARAM3]
--[AND A.UPDATE_TIME BETWEEN:PARAM4]
ORDER BY A.UPDATE_TIME DESC) GROUP BY ROLLUP(( Finished product item no , Finished product specification , The repair order , Stock in quantity , Stock in No , Case number , Operator , Storage time ))

chart ：

2. Correct SQL Sum graph

SELECT DECODE (GROUPING( Finished product item no )+GROUPING( Finished product specification )+GROUPING( The repair order )+GROUPING( Stock in quantity )+GROUPING( Stock in No )+GROUPING( Case number )+GROUPING( Operator )+GROUPING(TO_CHAR( Storage time ,'YYYY-MM-DD HH24:MI:SS')),8,' total ', Finished product item no ) Finished product item no ,  
       DECODE (GROUPING( Finished product item no )+GROUPING( Finished product specification )+GROUPING( The repair order )+GROUPING( Stock in quantity )+GROUPING( Stock in No )+GROUPING( Case number )+GROUPING( Operator )+GROUPING(TO_CHAR( Storage time ,'YYYY-MM-DD HH24:MI:SS')),8,'-', Finished product specification ) Finished product specification ,
       DECODE (GROUPING( Finished product item no )+GROUPING( Finished product specification )+GROUPING( The repair order )+GROUPING( Stock in quantity )+GROUPING( Stock in No )+GROUPING( Case number )+GROUPING( Operator )+GROUPING(TO_CHAR( Storage time ,'YYYY-MM-DD HH24:MI:SS')),8,'-', The repair order ) The repair order ,  
       DECODE (GROUPING( Finished product item no )+GROUPING( Finished product specification )+GROUPING( The repair order )+GROUPING( Stock in quantity )+GROUPING( Stock in No )+GROUPING( Case number )+GROUPING( Operator )+GROUPING(TO_CHAR( Storage time ,'YYYY-MM-DD HH24:MI:SS')),8,'-', Stock in quantity ) Stock in quantity ,
       DECODE (GROUPING( Finished product item no )+GROUPING( Finished product specification )+GROUPING( The repair order )+GROUPING( Stock in quantity )+GROUPING( Stock in No )+GROUPING( Case number )+GROUPING( Operator )+GROUPING(TO_CHAR( Storage time ,'YYYY-MM-DD HH24:MI:SS')),8,'-', Case number ) Case number ,
       DECODE (GROUPING( Finished product item no )+GROUPING( Finished product specification )+GROUPING( The repair order )+GROUPING( Stock in quantity )+GROUPING( Stock in No )+GROUPING( Case number )+GROUPING( Operator )+GROUPING(TO_CHAR( Storage time ,'YYYY-MM-DD HH24:MI:SS')),8,'-', Operator ) Operator ,
       DECODE (GROUPING( Finished product item no )+GROUPING( Finished product specification )+GROUPING( The repair order )+GROUPING( Stock in quantity )+GROUPING( Stock in No )+GROUPING( Case number )+GROUPING( Operator )+GROUPING(TO_CHAR( Storage time ,'YYYY-MM-DD HH24:MI:SS')),8,'-',TO_CHAR( Storage time ,'YYYY-MM-DD HH24:MI:SS')) Storage time ,
       SUM( Stock in quantity ) FROM 
(SELECT B.PART_NO AS  Finished product item no ,B.SPEC1 AS  Finished product specification  ,A.WORK_ORDER AS  The repair order  ,A.QTY AS  Stock in quantity ,A.BILL_NO AS  Stock in No ,A.CONTAINER_NO AS  Case number ,C.EMP_NAME AS  Operator ,A.UPDATE_TIME AS  Storage time 
FROM SAJET.WMS_PRODUCTS_IN A LEFT JOIN SAJET.SYS_PART  B ON A.PART_ID=B.PART_ID 
LEFT JOIN SAJET.SYS_EMP C ON A.UPDATE_USERID=C.EMP_ID
WHERE 1=1
AND A.BILL_NO IN(SELECT LIST_NO FROM SAJET.TBLK3_BILL_RECORD WHERE  ORDER_TYPE IN('1','2') AND ISOK='1')
AND A.WORK_ORDER='WORK014084'
--[AND B.PART_NO=:PARAM1]
--[AND A.WORK_ORDER=:PARAM2]
--[AND A.BILL_NO=:PARAM3]
--[AND A.UPDATE_TIME BETWEEN:PARAM4]
ORDER BY A.UPDATE_TIME DESC) GROUP BY ROLLUP(( Finished product item no , Finished product specification , The repair order , Stock in quantity , Stock in No , Case number , Operator ,TO_CHAR( Storage time ,'YYYY-MM-DD HH24:MI:SS')))

chart ：