DELETE FROM DC2.OR_100 WHERE PV_USER = '413';
commit;
INSERT INTO DC2.OR_100 (PV_KEY,PV_USER,PV1,PV2,PV3,PV4,PV5,PV6,PV7,PV8,PV9)
SELECT LPAD(TO_CHAR(ROWNUM),20,0),TABLE_SELECT.* FROM (
SELECT '413'
 ,SUBSTRB(TO_CHAR(T007.NEN),1,100) AS A001
 ,SUBSTRB(TO_CHAR(T007.NENGETSU),1,100) AS A002
 ,SUBSTRB(T001.SOUKO,1,100) AS A003
 ,SUBSTRB(MAX(T006.TOR_MEI),1,100) AS A004
 ,SUBSTRB(T002.SHI_CD,1,100) AS A005
 ,SUBSTRB(MAX(T004.TOR_MEI),1,100) AS A006
 ,SUBSTRB(TO_CHAR(ROUND(AVG(T001.URI_TAN),0)),1,100) AS A007
 ,SUBSTRB(TO_CHAR(SUM(T001.TOTAL_QTY)),1,100) AS A008
 ,SUBSTRB(TO_CHAR(SUM(T001.URI_KIN_ZEINUKI)),1,100) AS A009
FROM
DC2_DEMO.SAM_DENPYO T001
LEFT OUTER JOIN DC2_DEMO.SAM_SYOHIN T002 ON
 T001.DEN_SYO_CD=T002.SYO_CD
LEFT OUTER JOIN DC2_DEMO.SAM_DAY T007 ON
 T001.DEN_DATE=T007.HIDUKE
LEFT OUTER JOIN DC2_DEMO.SAM_TORIHIKI T006 ON
 T001.SOUKO=T006.TOR_CD
 AND T006.TOR_KB='0'
LEFT OUTER JOIN DC2_DEMO.SAM_TORIHIKI T004 ON
 T002.SHI_CD=T004.TOR_CD
 AND T004.TOR_KB='1'
WHERE
 T001.DEN_KB='2'
 AND T007.NENGETSU BETWEEN '20090101' AND '20091201'
GROUP BY
  T007.NEN
 ,T007.NENGETSU
 ,T001.SOUKO
 ,T002.SHI_CD
ORDER BY
  T007.NEN
 ,T007.NENGETSU
 ,T001.SOUKO
 ,T002.SHI_CD
) TABLE_SELECT
;
commit;
EXIT;


