--********************************************* --*Žw’θ“ϊ•tŠΤ‚Μ”NEŒŽEƒOƒŒ[ƒh‚πŽζ“Ύ --* --********************************************* [lselectRecords] select CD_GRADE ,NAME_GREAD ,T.DTS_YEAR ,T.DTS_MONTH FROM M_GREAD MG ,( select DTS_YEAR ,DTS_MONTH ,CD_GREAD from T_MCP where DT_MCP >= to_date('&2', 'yyyy/mm/dd') and DT_MCP <= to_date('&4', 'yyyy/mm/dd') and (('&6' = '') or (CD_GREAD = ('&6' ::text) ::numeric)) union select DTS_YEAR ,DTS_MONTH ,CD_GREAD from T_DERIVERY_PRODUCT where DT_DERIVERY_PRODUCT >= to_date('&2', 'yyyy/mm/dd') and DT_DERIVERY_PRODUCT <= to_date('&4', 'yyyy/mm/dd') and (('&6' = '') or (CD_GREAD = ('&6' ::text) ::numeric)) order by DTS_YEAR, DTS_MONTH, CD_GREAD ) T where MG.CD_GRADE = T.CD_GREAD --********************************************* --*’Šo‚·‚ι”NŒŽ‚πŽζ“Ύ --* --********************************************* [lselectRecords5] select T2.DTS_YEAR ,T2.DTS_MONTH FROM ( select T.DTS_YEAR ,T.DTS_MONTH from M_GREAD MG ,( select DTS_YEAR ,DTS_MONTH from T_MCP where DT_MCP >= to_date('&2', 'yyyy/mm/dd') and DT_MCP <= to_date('&4', 'yyyy/mm/dd') union select DTS_YEAR ,DTS_MONTH from T_DERIVERY_PRODUCT where DT_DERIVERY_PRODUCT >= to_date('&2', 'yyyy/mm/dd') and DT_DERIVERY_PRODUCT <= to_date('&4', 'yyyy/mm/dd') union select DTS_YEAR ,DTS_MONTH from T_ADJUSTMENT where DT_ADJUSTMENT >= to_date('&2', 'yyyy/mm/dd') and DT_ADJUSTMENT <= to_date('&4', 'yyyy/mm/dd') ) T GROUP BY T.DTS_YEAR, T.DTS_MONTH order by T.DTS_YEAR, T.DTS_MONTH ) T2 where T2.DTS_YEAR is not null and T2.DTS_MONTH is not null --********************************************* --*1‚©ŒŽ•ͺ‚Μƒf[ƒ^‚πŽζ“Ύ --* --********************************************* [lselectRecords2] SELECT MCP2.DTS_YEAR ,MCP2.DTS_MONTH ,MCP2.CD_GREAD ,MCP2.AVE_MOIST ,MCP2.DIV_WEIGHT_DRIED ,SUM(coalesce(MCP2.GANSUI1, 0)) GANSUI1 ,SUM(coalesce(MCP2.GANSUI2, 0)) GANSUI2 ,SUM(coalesce(MCP2.GANSUI3, 0)) GANSUI3 ,SUM(coalesce(MCP2.GANSUI4, 0)) GANSUI4 ,SUM(coalesce(MCP2.GANSUI5, 0)) GANSUI5 ,SUM(coalesce(MCP2.GANSUI6, 0)) GANSUI6 ,SUM(coalesce(MCP2.GANSUI7, 0)) GANSUI7 ,SUM(coalesce(MCP2.GANSUI8, 0)) GANSUI8 ,SUM(coalesce(MCP2.GANSUI9, 0)) GANSUI9 ,SUM(coalesce(MCP2.GANSUI10, 0)) GANSUI10 ,SUM(coalesce(MCP2.GANSUI11, 0)) GANSUI11 ,SUM(coalesce(MCP2.GANSUI12, 0)) GANSUI12 ,SUM(coalesce(MCP2.GANSUI13, 0)) GANSUI13 ,SUM(coalesce(MCP2.GANSUI14, 0)) GANSUI14 ,SUM(coalesce(MCP2.GANSUI15, 0)) GANSUI15 ,SUM(coalesce(MCP2.GANSUI16, 0)) GANSUI16 ,SUM(coalesce(MCP2.GANSUI17, 0)) GANSUI17 ,SUM(coalesce(MCP2.GANSUI18, 0)) GANSUI18 ,SUM(coalesce(MCP2.GANSUI19, 0)) GANSUI19 ,SUM(coalesce(MCP2.GANSUI20, 0)) GANSUI20 ,SUM(coalesce(MCP2.GANSUI21, 0)) GANSUI21 ,SUM(coalesce(MCP2.GANSUI22, 0)) GANSUI22 ,SUM(coalesce(MCP2.GANSUI23, 0)) GANSUI23 ,SUM(coalesce(MCP2.GANSUI24, 0)) GANSUI24 ,SUM(coalesce(MCP2.GANSUI25, 0)) GANSUI25 ,SUM(coalesce(MCP2.GANSUI26, 0)) GANSUI26 ,SUM(coalesce(MCP2.GANSUI27, 0)) GANSUI27 ,SUM(coalesce(MCP2.GANSUI28, 0)) GANSUI28 ,SUM(coalesce(MCP2.GANSUI29, 0)) GANSUI29 ,SUM(coalesce(MCP2.GANSUI30, 0)) GANSUI30 ,SUM(coalesce(MCP2.GANSUI31, 0)) GANSUI31 ,SUM(coalesce(MCP2.FUKAN1, 0)) FUKAN1 ,SUM(coalesce(MCP2.FUKAN2, 0)) FUKAN2 ,SUM(coalesce(MCP2.FUKAN3, 0)) FUKAN3 ,SUM(coalesce(MCP2.FUKAN4, 0)) FUKAN4 ,SUM(coalesce(MCP2.FUKAN5, 0)) FUKAN5 ,SUM(coalesce(MCP2.FUKAN6, 0)) FUKAN6 ,SUM(coalesce(MCP2.FUKAN7, 0)) FUKAN7 ,SUM(coalesce(MCP2.FUKAN8, 0)) FUKAN8 ,SUM(coalesce(MCP2.FUKAN9, 0)) FUKAN9 ,SUM(coalesce(MCP2.FUKAN10, 0)) FUKAN10 ,SUM(coalesce(MCP2.FUKAN11, 0)) FUKAN11 ,SUM(coalesce(MCP2.FUKAN12, 0)) FUKAN12 ,SUM(coalesce(MCP2.FUKAN13, 0)) FUKAN13 ,SUM(coalesce(MCP2.FUKAN14, 0)) FUKAN14 ,SUM(coalesce(MCP2.FUKAN15, 0)) FUKAN15 ,SUM(coalesce(MCP2.FUKAN16, 0)) FUKAN16 ,SUM(coalesce(MCP2.FUKAN17, 0)) FUKAN17 ,SUM(coalesce(MCP2.FUKAN18, 0)) FUKAN18 ,SUM(coalesce(MCP2.FUKAN19, 0)) FUKAN19 ,SUM(coalesce(MCP2.FUKAN20, 0)) FUKAN20 ,SUM(coalesce(MCP2.FUKAN21, 0)) FUKAN21 ,SUM(coalesce(MCP2.FUKAN22, 0)) FUKAN22 ,SUM(coalesce(MCP2.FUKAN23, 0)) FUKAN23 ,SUM(coalesce(MCP2.FUKAN24, 0)) FUKAN24 ,SUM(coalesce(MCP2.FUKAN25, 0)) FUKAN25 ,SUM(coalesce(MCP2.FUKAN26, 0)) FUKAN26 ,SUM(coalesce(MCP2.FUKAN27, 0)) FUKAN27 ,SUM(coalesce(MCP2.FUKAN28, 0)) FUKAN28 ,SUM(coalesce(MCP2.FUKAN29, 0)) FUKAN29 ,SUM(coalesce(MCP2.FUKAN30, 0)) FUKAN30 ,SUM(coalesce(MCP2.FUKAN31, 0)) FUKAN31 ,SUM(coalesce(MCP2.SUIBUN1, 0)) SUIBUN1 ,SUM(coalesce(MCP2.SUIBUN2, 0)) SUIBUN2 ,SUM(coalesce(MCP2.SUIBUN3, 0)) SUIBUN3 ,SUM(coalesce(MCP2.SUIBUN4, 0)) SUIBUN4 ,SUM(coalesce(MCP2.SUIBUN5, 0)) SUIBUN5 ,SUM(coalesce(MCP2.SUIBUN6, 0)) SUIBUN6 ,SUM(coalesce(MCP2.SUIBUN7, 0)) SUIBUN7 ,SUM(coalesce(MCP2.SUIBUN8, 0)) SUIBUN8 ,SUM(coalesce(MCP2.SUIBUN9, 0)) SUIBUN9 ,SUM(coalesce(MCP2.SUIBUN10, 0)) SUIBUN10 ,SUM(coalesce(MCP2.SUIBUN11, 0)) SUIBUN11 ,SUM(coalesce(MCP2.SUIBUN12, 0)) SUIBUN12 ,SUM(coalesce(MCP2.SUIBUN13, 0)) SUIBUN13 ,SUM(coalesce(MCP2.SUIBUN14, 0)) SUIBUN14 ,SUM(coalesce(MCP2.SUIBUN15, 0)) SUIBUN15 ,SUM(coalesce(MCP2.SUIBUN16, 0)) SUIBUN16 ,SUM(coalesce(MCP2.SUIBUN17, 0)) SUIBUN17 ,SUM(coalesce(MCP2.SUIBUN18, 0)) SUIBUN18 ,SUM(coalesce(MCP2.SUIBUN19, 0)) SUIBUN19 ,SUM(coalesce(MCP2.SUIBUN20, 0)) SUIBUN20 ,SUM(coalesce(MCP2.SUIBUN21, 0)) SUIBUN21 ,SUM(coalesce(MCP2.SUIBUN22, 0)) SUIBUN22 ,SUM(coalesce(MCP2.SUIBUN23, 0)) SUIBUN23 ,SUM(coalesce(MCP2.SUIBUN24, 0)) SUIBUN24 ,SUM(coalesce(MCP2.SUIBUN25, 0)) SUIBUN25 ,SUM(coalesce(MCP2.SUIBUN26, 0)) SUIBUN26 ,SUM(coalesce(MCP2.SUIBUN27, 0)) SUIBUN27 ,SUM(coalesce(MCP2.SUIBUN28, 0)) SUIBUN28 ,SUM(coalesce(MCP2.SUIBUN29, 0)) SUIBUN29 ,SUM(coalesce(MCP2.SUIBUN30, 0)) SUIBUN30 ,SUM(coalesce(MCP2.SUIBUN31, 0)) SUIBUN31 ,SUM(coalesce(MCP2.TANAFU1, 0)) TANAFU1 ,SUM(coalesce(MCP2.TANAFU2, 0)) TANAFU2 ,SUM(coalesce(MCP2.TANAFU3, 0)) TANAFU3 ,SUM(coalesce(MCP2.TANAFU4, 0)) TANAFU4 ,SUM(coalesce(MCP2.TANAFU5, 0)) TANAFU5 ,SUM(coalesce(MCP2.TANAFU6, 0)) TANAFU6 ,SUM(coalesce(MCP2.TANAFU7, 0)) TANAFU7 ,SUM(coalesce(MCP2.TANAFU8, 0)) TANAFU8 ,SUM(coalesce(MCP2.TANAFU9, 0)) TANAFU9 ,SUM(coalesce(MCP2.TANAFU10, 0)) TANAFU10 ,SUM(coalesce(MCP2.TANAFU11, 0)) TANAFU11 ,SUM(coalesce(MCP2.TANAFU12, 0)) TANAFU12 ,SUM(coalesce(MCP2.TANAFU13, 0)) TANAFU13 ,SUM(coalesce(MCP2.TANAFU14, 0)) TANAFU14 ,SUM(coalesce(MCP2.TANAFU15, 0)) TANAFU15 ,SUM(coalesce(MCP2.TANAFU16, 0)) TANAFU16 ,SUM(coalesce(MCP2.TANAFU17, 0)) TANAFU17 ,SUM(coalesce(MCP2.TANAFU18, 0)) TANAFU18 ,SUM(coalesce(MCP2.TANAFU19, 0)) TANAFU19 ,SUM(coalesce(MCP2.TANAFU20, 0)) TANAFU20 ,SUM(coalesce(MCP2.TANAFU21, 0)) TANAFU21 ,SUM(coalesce(MCP2.TANAFU22, 0)) TANAFU22 ,SUM(coalesce(MCP2.TANAFU23, 0)) TANAFU23 ,SUM(coalesce(MCP2.TANAFU24, 0)) TANAFU24 ,SUM(coalesce(MCP2.TANAFU25, 0)) TANAFU25 ,SUM(coalesce(MCP2.TANAFU26, 0)) TANAFU26 ,SUM(coalesce(MCP2.TANAFU27, 0)) TANAFU27 ,SUM(coalesce(MCP2.TANAFU28, 0)) TANAFU28 ,SUM(coalesce(MCP2.TANAFU29, 0)) TANAFU29 ,SUM(coalesce(MCP2.TANAFU30, 0)) TANAFU30 ,SUM(coalesce(MCP2.TANAFU31, 0)) TANAFU31 FROM( select TDP.DTS_YEAR ,TDP.DTS_MONTH ,TDP.CD_GREAD ,MG.AVE_MOIST ,MD.DIV_WEIGHT_DRIED ,coalesce(sum(CASE WHEN TDP.DTS_DAY = 01 THEN WEIGHT_MOIST ELSE 0 END), 0) GANSUI1 ,coalesce(sum(CASE WHEN TDP.DTS_DAY = 02 THEN WEIGHT_MOIST ELSE 0 END), 0) GANSUI2 ,coalesce(sum(CASE WHEN TDP.DTS_DAY = 03 THEN WEIGHT_MOIST ELSE 0 END), 0) GANSUI3 ,coalesce(sum(CASE WHEN TDP.DTS_DAY = 04 THEN WEIGHT_MOIST ELSE 0 END), 0) GANSUI4 ,coalesce(sum(CASE WHEN TDP.DTS_DAY = 05 THEN WEIGHT_MOIST ELSE 0 END), 0) GANSUI5 ,coalesce(sum(CASE WHEN TDP.DTS_DAY = 06 THEN WEIGHT_MOIST ELSE 0 END), 0) GANSUI6 ,coalesce(sum(CASE WHEN TDP.DTS_DAY = 07 THEN WEIGHT_MOIST ELSE 0 END), 0) GANSUI7 ,coalesce(sum(CASE WHEN TDP.DTS_DAY = 08 THEN WEIGHT_MOIST ELSE 0 END), 0) GANSUI8 ,coalesce(sum(CASE WHEN TDP.DTS_DAY = 09 THEN WEIGHT_MOIST ELSE 0 END), 0) GANSUI9 ,coalesce(sum(CASE WHEN TDP.DTS_DAY = 10 THEN WEIGHT_MOIST ELSE 0 END), 0) GANSUI10 ,coalesce(sum(CASE WHEN TDP.DTS_DAY = 11 THEN WEIGHT_MOIST ELSE 0 END), 0) GANSUI11 ,coalesce(sum(CASE WHEN TDP.DTS_DAY = 12 THEN WEIGHT_MOIST ELSE 0 END), 0) GANSUI12 ,coalesce(sum(CASE WHEN TDP.DTS_DAY = 13 THEN WEIGHT_MOIST ELSE 0 END), 0) GANSUI13 ,coalesce(sum(CASE WHEN TDP.DTS_DAY = 14 THEN WEIGHT_MOIST ELSE 0 END), 0) GANSUI14 ,coalesce(sum(CASE WHEN TDP.DTS_DAY = 15 THEN WEIGHT_MOIST ELSE 0 END), 0) GANSUI15 ,coalesce(sum(CASE WHEN TDP.DTS_DAY = 16 THEN WEIGHT_MOIST ELSE 0 END), 0) GANSUI16 ,coalesce(sum(CASE WHEN TDP.DTS_DAY = 17 THEN WEIGHT_MOIST ELSE 0 END), 0) GANSUI17 ,coalesce(sum(CASE WHEN TDP.DTS_DAY = 18 THEN WEIGHT_MOIST ELSE 0 END), 0) GANSUI18 ,coalesce(sum(CASE WHEN TDP.DTS_DAY = 19 THEN WEIGHT_MOIST ELSE 0 END), 0) GANSUI19 ,coalesce(sum(CASE WHEN TDP.DTS_DAY = 20 THEN WEIGHT_MOIST ELSE 0 END), 0) GANSUI20 ,coalesce(sum(CASE WHEN TDP.DTS_DAY = 21 THEN WEIGHT_MOIST ELSE 0 END), 0) GANSUI21 ,coalesce(sum(CASE WHEN TDP.DTS_DAY = 22 THEN WEIGHT_MOIST ELSE 0 END), 0) GANSUI22 ,coalesce(sum(CASE WHEN TDP.DTS_DAY = 23 THEN WEIGHT_MOIST ELSE 0 END), 0) GANSUI23 ,coalesce(sum(CASE WHEN TDP.DTS_DAY = 24 THEN WEIGHT_MOIST ELSE 0 END), 0) GANSUI24 ,coalesce(sum(CASE WHEN TDP.DTS_DAY = 25 THEN WEIGHT_MOIST ELSE 0 END), 0) GANSUI25 ,coalesce(sum(CASE WHEN TDP.DTS_DAY = 26 THEN WEIGHT_MOIST ELSE 0 END), 0) GANSUI26 ,coalesce(sum(CASE WHEN TDP.DTS_DAY = 27 THEN WEIGHT_MOIST ELSE 0 END), 0) GANSUI27 ,coalesce(sum(CASE WHEN TDP.DTS_DAY = 28 THEN WEIGHT_MOIST ELSE 0 END), 0) GANSUI28 ,coalesce(sum(CASE WHEN TDP.DTS_DAY = 29 THEN WEIGHT_MOIST ELSE 0 END), 0) GANSUI29 ,coalesce(sum(CASE WHEN TDP.DTS_DAY = 30 THEN WEIGHT_MOIST ELSE 0 END), 0) GANSUI30 ,coalesce(sum(CASE WHEN TDP.DTS_DAY = 31 THEN WEIGHT_MOIST ELSE 0 END), 0) GANSUI31 ,coalesce(sum(CASE WHEN TDP.DTS_DAY = 01 THEN WEIGHT_DRY ELSE 0 END), 0) fukan1 ,coalesce(sum(CASE WHEN TDP.DTS_DAY = 02 THEN WEIGHT_DRY ELSE 0 END), 0) fukan2 ,coalesce(sum(CASE WHEN TDP.DTS_DAY = 03 THEN WEIGHT_DRY ELSE 0 END), 0) fukan3 ,coalesce(sum(CASE WHEN TDP.DTS_DAY = 04 THEN WEIGHT_DRY ELSE 0 END), 0) fukan4 ,coalesce(sum(CASE WHEN TDP.DTS_DAY = 05 THEN WEIGHT_DRY ELSE 0 END), 0) fukan5 ,coalesce(sum(CASE WHEN TDP.DTS_DAY = 06 THEN WEIGHT_DRY ELSE 0 END), 0) fukan6 ,coalesce(sum(CASE WHEN TDP.DTS_DAY = 07 THEN WEIGHT_DRY ELSE 0 END), 0) fukan7 ,coalesce(sum(CASE WHEN TDP.DTS_DAY = 08 THEN WEIGHT_DRY ELSE 0 END), 0) fukan8 ,coalesce(sum(CASE WHEN TDP.DTS_DAY = 09 THEN WEIGHT_DRY ELSE 0 END), 0) fukan9 ,coalesce(sum(CASE WHEN TDP.DTS_DAY = 10 THEN WEIGHT_DRY ELSE 0 END), 0) fukan10 ,coalesce(sum(CASE WHEN TDP.DTS_DAY = 11 THEN WEIGHT_DRY ELSE 0 END), 0) fukan11 ,coalesce(sum(CASE WHEN TDP.DTS_DAY = 12 THEN WEIGHT_DRY ELSE 0 END), 0) fukan12 ,coalesce(sum(CASE WHEN TDP.DTS_DAY = 13 THEN WEIGHT_DRY ELSE 0 END), 0) fukan13 ,coalesce(sum(CASE WHEN TDP.DTS_DAY = 14 THEN WEIGHT_DRY ELSE 0 END), 0) fukan14 ,coalesce(sum(CASE WHEN TDP.DTS_DAY = 15 THEN WEIGHT_DRY ELSE 0 END), 0) fukan15 ,coalesce(sum(CASE WHEN TDP.DTS_DAY = 16 THEN WEIGHT_DRY ELSE 0 END), 0) fukan16 ,coalesce(sum(CASE WHEN TDP.DTS_DAY = 17 THEN WEIGHT_DRY ELSE 0 END), 0) fukan17 ,coalesce(sum(CASE WHEN TDP.DTS_DAY = 18 THEN WEIGHT_DRY ELSE 0 END), 0) fukan18 ,coalesce(sum(CASE WHEN TDP.DTS_DAY = 19 THEN WEIGHT_DRY ELSE 0 END), 0) fukan19 ,coalesce(sum(CASE WHEN TDP.DTS_DAY = 20 THEN WEIGHT_DRY ELSE 0 END), 0) fukan20 ,coalesce(sum(CASE WHEN TDP.DTS_DAY = 21 THEN WEIGHT_DRY ELSE 0 END), 0) fukan21 ,coalesce(sum(CASE WHEN TDP.DTS_DAY = 22 THEN WEIGHT_DRY ELSE 0 END), 0) fukan22 ,coalesce(sum(CASE WHEN TDP.DTS_DAY = 23 THEN WEIGHT_DRY ELSE 0 END), 0) fukan23 ,coalesce(sum(CASE WHEN TDP.DTS_DAY = 24 THEN WEIGHT_DRY ELSE 0 END), 0) fukan24 ,coalesce(sum(CASE WHEN TDP.DTS_DAY = 25 THEN WEIGHT_DRY ELSE 0 END), 0) fukan25 ,coalesce(sum(CASE WHEN TDP.DTS_DAY = 26 THEN WEIGHT_DRY ELSE 0 END), 0) fukan26 ,coalesce(sum(CASE WHEN TDP.DTS_DAY = 27 THEN WEIGHT_DRY ELSE 0 END), 0) fukan27 ,coalesce(sum(CASE WHEN TDP.DTS_DAY = 28 THEN WEIGHT_DRY ELSE 0 END), 0) fukan28 ,coalesce(sum(CASE WHEN TDP.DTS_DAY = 29 THEN WEIGHT_DRY ELSE 0 END), 0) fukan29 ,coalesce(sum(CASE WHEN TDP.DTS_DAY = 30 THEN WEIGHT_DRY ELSE 0 END), 0) fukan30 ,coalesce(sum(CASE WHEN TDP.DTS_DAY = 31 THEN WEIGHT_DRY ELSE 0 END), 0) fukan31 ,coalesce(sum(CASE WHEN TDP.DTS_DAY = 01 THEN RATIO_MOIST ELSE 0 END), 0) SUIBUN1 ,coalesce(sum(CASE WHEN TDP.DTS_DAY = 02 THEN RATIO_MOIST ELSE 0 END), 0) SUIBUN2 ,coalesce(sum(CASE WHEN TDP.DTS_DAY = 03 THEN RATIO_MOIST ELSE 0 END), 0) SUIBUN3 ,coalesce(sum(CASE WHEN TDP.DTS_DAY = 04 THEN RATIO_MOIST ELSE 0 END), 0) SUIBUN4 ,coalesce(sum(CASE WHEN TDP.DTS_DAY = 05 THEN RATIO_MOIST ELSE 0 END), 0) SUIBUN5 ,coalesce(sum(CASE WHEN TDP.DTS_DAY = 06 THEN RATIO_MOIST ELSE 0 END), 0) SUIBUN6 ,coalesce(sum(CASE WHEN TDP.DTS_DAY = 07 THEN RATIO_MOIST ELSE 0 END), 0) SUIBUN7 ,coalesce(sum(CASE WHEN TDP.DTS_DAY = 08 THEN RATIO_MOIST ELSE 0 END), 0) SUIBUN8 ,coalesce(sum(CASE WHEN TDP.DTS_DAY = 09 THEN RATIO_MOIST ELSE 0 END), 0) SUIBUN9 ,coalesce(sum(CASE WHEN TDP.DTS_DAY = 10 THEN RATIO_MOIST ELSE 0 END), 0) SUIBUN10 ,coalesce(sum(CASE WHEN TDP.DTS_DAY = 11 THEN RATIO_MOIST ELSE 0 END), 0) SUIBUN11 ,coalesce(sum(CASE WHEN TDP.DTS_DAY = 12 THEN RATIO_MOIST ELSE 0 END), 0) SUIBUN12 ,coalesce(sum(CASE WHEN TDP.DTS_DAY = 13 THEN RATIO_MOIST ELSE 0 END), 0) SUIBUN13 ,coalesce(sum(CASE WHEN TDP.DTS_DAY = 14 THEN RATIO_MOIST ELSE 0 END), 0) SUIBUN14 ,coalesce(sum(CASE WHEN TDP.DTS_DAY = 15 THEN RATIO_MOIST ELSE 0 END), 0) SUIBUN15 ,coalesce(sum(CASE WHEN TDP.DTS_DAY = 16 THEN RATIO_MOIST ELSE 0 END), 0) SUIBUN16 ,coalesce(sum(CASE WHEN TDP.DTS_DAY = 17 THEN RATIO_MOIST ELSE 0 END), 0) SUIBUN17 ,coalesce(sum(CASE WHEN TDP.DTS_DAY = 18 THEN RATIO_MOIST ELSE 0 END), 0) SUIBUN18 ,coalesce(sum(CASE WHEN TDP.DTS_DAY = 19 THEN RATIO_MOIST ELSE 0 END), 0) SUIBUN19 ,coalesce(sum(CASE WHEN TDP.DTS_DAY = 20 THEN RATIO_MOIST ELSE 0 END), 0) SUIBUN20 ,coalesce(sum(CASE WHEN TDP.DTS_DAY = 21 THEN RATIO_MOIST ELSE 0 END), 0) SUIBUN21 ,coalesce(sum(CASE WHEN TDP.DTS_DAY = 22 THEN RATIO_MOIST ELSE 0 END), 0) SUIBUN22 ,coalesce(sum(CASE WHEN TDP.DTS_DAY = 23 THEN RATIO_MOIST ELSE 0 END), 0) SUIBUN23 ,coalesce(sum(CASE WHEN TDP.DTS_DAY = 24 THEN RATIO_MOIST ELSE 0 END), 0) SUIBUN24 ,coalesce(sum(CASE WHEN TDP.DTS_DAY = 25 THEN RATIO_MOIST ELSE 0 END), 0) SUIBUN25 ,coalesce(sum(CASE WHEN TDP.DTS_DAY = 26 THEN RATIO_MOIST ELSE 0 END), 0) SUIBUN26 ,coalesce(sum(CASE WHEN TDP.DTS_DAY = 27 THEN RATIO_MOIST ELSE 0 END), 0) SUIBUN27 ,coalesce(sum(CASE WHEN TDP.DTS_DAY = 28 THEN RATIO_MOIST ELSE 0 END), 0) SUIBUN28 ,coalesce(sum(CASE WHEN TDP.DTS_DAY = 29 THEN RATIO_MOIST ELSE 0 END), 0) SUIBUN29 ,coalesce(sum(CASE WHEN TDP.DTS_DAY = 30 THEN RATIO_MOIST ELSE 0 END), 0) SUIBUN30 ,coalesce(sum(CASE WHEN TDP.DTS_DAY = 31 THEN RATIO_MOIST ELSE 0 END), 0) SUIBUN31 ,0 tanafu1 ,0 tanafu2 ,0 tanafu3 ,0 tanafu4 ,0 tanafu5 ,0 tanafu6 ,0 tanafu7 ,0 tanafu8 ,0 tanafu9 ,0 tanafu10 ,0 tanafu11 ,0 tanafu12 ,0 tanafu13 ,0 tanafu14 ,0 tanafu15 ,0 tanafu16 ,0 tanafu17 ,0 tanafu18 ,0 tanafu19 ,0 tanafu20 ,0 tanafu21 ,0 tanafu22 ,0 tanafu23 ,0 tanafu24 ,0 tanafu25 ,0 tanafu26 ,0 tanafu27 ,0 tanafu28 ,0 tanafu29 ,0 tanafu30 ,0 tanafu31 from T_DERIVERY_PRODUCT TDP ,M_GREAD MG ,( SELECT CD_DERIVERY ,CD_CUSTOMER ,DIV_WEIGHT_DRIED FROM M_DERIVERY WHERE DT_START <= TO_DATE('&5', 'yyyy/mm/dd') AND DT_END >= TO_DATE('&5', 'yyyy/mm/dd') ) MD where TDP.DTS_MONTH = ('&2' ::text) ::numeric AND TDP.DTS_YEAR = ('&1' ::text) ::numeric AND (('&3' = '') OR (TDP.CD_GREAD = ('&3' ::text) ::numeric)) AND TDP.CD_GREAD = MG.CD_GRADE AND TDP.CD_DERIVERY = MD.CD_DERIVERY AND TDP.FLG_DELETE = 0 group by TDP.DTS_YEAR , TDP.DTS_MONTH , TDP.CD_GREAD, MG.AVE_MOIST, MD.DIV_WEIGHT_DRIED UNION select TA.DTS_YEAR ,TA.DTS_MONTH ,TA.CD_GREAD ,MG.AVE_MOIST ,MD.DIV_WEIGHT_DRIED ,0 GANSUI1 ,0 GANSUI2 ,0 GANSUI3 ,0 GANSUI4 ,0 GANSUI5 ,0 GANSUI6 ,0 GANSUI7 ,0 GANSUI8 ,0 GANSUI9 ,0 GANSUI10 ,0 GANSUI11 ,0 GANSUI12 ,0 GANSUI13 ,0 GANSUI14 ,0 GANSUI15 ,0 GANSUI16 ,0 GANSUI17 ,0 GANSUI18 ,0 GANSUI19 ,0 GANSUI20 ,0 GANSUI21 ,0 GANSUI22 ,0 GANSUI23 ,0 GANSUI24 ,0 GANSUI25 ,0 GANSUI26 ,0 GANSUI27 ,0 GANSUI28 ,0 GANSUI29 ,0 GANSUI30 ,0 GANSUI31 ,0 fukan1 ,0 fukan2 ,0 fukan3 ,0 fukan4 ,0 fukan5 ,0 fukan6 ,0 fukan7 ,0 fukan8 ,0 fukan9 ,0 fukan10 ,0 fukan11 ,0 fukan12 ,0 fukan13 ,0 fukan14 ,0 fukan15 ,0 fukan16 ,0 fukan17 ,0 fukan18 ,0 fukan19 ,0 fukan20 ,0 fukan21 ,0 fukan22 ,0 fukan23 ,0 fukan24 ,0 fukan25 ,0 fukan26 ,0 fukan27 ,0 fukan28 ,0 fukan29 ,0 fukan30 ,0 fukan31 ,0 SUIBUN1 ,0 SUIBUN2 ,0 SUIBUN3 ,0 SUIBUN4 ,0 SUIBUN5 ,0 SUIBUN6 ,0 SUIBUN7 ,0 SUIBUN8 ,0 SUIBUN9 ,0 SUIBUN10 ,0 SUIBUN11 ,0 SUIBUN12 ,0 SUIBUN13 ,0 SUIBUN14 ,0 SUIBUN15 ,0 SUIBUN16 ,0 SUIBUN17 ,0 SUIBUN18 ,0 SUIBUN19 ,0 SUIBUN20 ,0 SUIBUN21 ,0 SUIBUN22 ,0 SUIBUN23 ,0 SUIBUN24 ,0 SUIBUN25 ,0 SUIBUN26 ,0 SUIBUN27 ,0 SUIBUN28 ,0 SUIBUN29 ,0 SUIBUN30 ,0 SUIBUN31 ,sum(CASE WHEN TA.DTS_DAY = 01 THEN coalesce(TA.WEIGHT, 0) ELSE 0 END) tanafu1 ,sum(CASE WHEN TA.DTS_DAY = 02 THEN coalesce(TA.WEIGHT, 0) ELSE 0 END) tanafu2 ,sum(CASE WHEN TA.DTS_DAY = 03 THEN coalesce(TA.WEIGHT, 0) ELSE 0 END) tanafu3 ,sum(CASE WHEN TA.DTS_DAY = 04 THEN coalesce(TA.WEIGHT, 0) ELSE 0 END) tanafu4 ,sum(CASE WHEN TA.DTS_DAY = 05 THEN coalesce(TA.WEIGHT, 0) ELSE 0 END) tanafu5 ,sum(CASE WHEN TA.DTS_DAY = 06 THEN coalesce(TA.WEIGHT, 0) ELSE 0 END) tanafu6 ,sum(CASE WHEN TA.DTS_DAY = 07 THEN coalesce(TA.WEIGHT, 0) ELSE 0 END) tanafu7 ,sum(CASE WHEN TA.DTS_DAY = 08 THEN coalesce(TA.WEIGHT, 0) ELSE 0 END) tanafu8 ,sum(CASE WHEN TA.DTS_DAY = 09 THEN coalesce(TA.WEIGHT, 0) ELSE 0 END) tanafu9 ,sum(CASE WHEN TA.DTS_DAY = 10 THEN coalesce(TA.WEIGHT, 0) ELSE 0 END) tanafu10 ,sum(CASE WHEN TA.DTS_DAY = 11 THEN coalesce(TA.WEIGHT, 0) ELSE 0 END) tanafu11 ,sum(CASE WHEN TA.DTS_DAY = 12 THEN coalesce(TA.WEIGHT, 0) ELSE 0 END) tanafu12 ,sum(CASE WHEN TA.DTS_DAY = 13 THEN coalesce(TA.WEIGHT, 0) ELSE 0 END) tanafu13 ,sum(CASE WHEN TA.DTS_DAY = 14 THEN coalesce(TA.WEIGHT, 0) ELSE 0 END) tanafu14 ,sum(CASE WHEN TA.DTS_DAY = 15 THEN coalesce(TA.WEIGHT, 0) ELSE 0 END) tanafu15 ,sum(CASE WHEN TA.DTS_DAY = 16 THEN coalesce(TA.WEIGHT, 0) ELSE 0 END) tanafu16 ,sum(CASE WHEN TA.DTS_DAY = 17 THEN coalesce(TA.WEIGHT, 0) ELSE 0 END) tanafu17 ,sum(CASE WHEN TA.DTS_DAY = 18 THEN coalesce(TA.WEIGHT, 0) ELSE 0 END) tanafu18 ,sum(CASE WHEN TA.DTS_DAY = 19 THEN coalesce(TA.WEIGHT, 0) ELSE 0 END) tanafu19 ,sum(CASE WHEN TA.DTS_DAY = 20 THEN coalesce(TA.WEIGHT, 0) ELSE 0 END) tanafu20 ,sum(CASE WHEN TA.DTS_DAY = 21 THEN coalesce(TA.WEIGHT, 0) ELSE 0 END) tanafu21 ,sum(CASE WHEN TA.DTS_DAY = 22 THEN coalesce(TA.WEIGHT, 0) ELSE 0 END) tanafu22 ,sum(CASE WHEN TA.DTS_DAY = 23 THEN coalesce(TA.WEIGHT, 0) ELSE 0 END) tanafu23 ,sum(CASE WHEN TA.DTS_DAY = 24 THEN coalesce(TA.WEIGHT, 0) ELSE 0 END) tanafu24 ,sum(CASE WHEN TA.DTS_DAY = 25 THEN coalesce(TA.WEIGHT, 0) ELSE 0 END) tanafu25 ,sum(CASE WHEN TA.DTS_DAY = 26 THEN coalesce(TA.WEIGHT, 0) ELSE 0 END) tanafu26 ,sum(CASE WHEN TA.DTS_DAY = 27 THEN coalesce(TA.WEIGHT, 0) ELSE 0 END) tanafu27 ,sum(CASE WHEN TA.DTS_DAY = 28 THEN coalesce(TA.WEIGHT, 0) ELSE 0 END) tanafu28 ,sum(CASE WHEN TA.DTS_DAY = 29 THEN coalesce(TA.WEIGHT, 0) ELSE 0 END) tanafu29 ,sum(CASE WHEN TA.DTS_DAY = 30 THEN coalesce(TA.WEIGHT, 0) ELSE 0 END) tanafu30 ,sum(CASE WHEN TA.DTS_DAY = 31 THEN coalesce(TA.WEIGHT, 0) ELSE 0 END) tanafu31 from T_ADJUSTMENT TA ,M_GREAD MG ,( SELECT CD_DERIVERY ,CD_CUSTOMER ,DIV_WEIGHT_DRIED FROM M_DERIVERY WHERE DT_START <= TO_DATE('&5', 'yyyy/mm/dd') AND DT_END >= TO_DATE('&5', 'yyyy/mm/dd') ) MD where TA.DTS_MONTH = ('&2' ::text) ::numeric AND TA.DTS_YEAR = ('&1' ::text) ::numeric AND (('&3' = '') OR (TA.CD_GREAD = ('&3' ::text) ::numeric)) AND TA.CD_GREAD = MG.CD_GRADE AND TA.CD_DERIVERY = MD.CD_DERIVERY AND TA.FLG_DELETE = 0 AND DIV_WP_PRODUCT = 1 group by TA.DTS_YEAR , TA.DTS_MONTH , TA.CD_GREAD, MG.AVE_MOIST, MD.DIV_WEIGHT_DRIED ) MCP2 GROUP BY MCP2.DTS_YEAR, MCP2.DTS_MONTH, MCP2.CD_GREAD, MCP2.AVE_MOIST, MCP2.DIV_WEIGHT_DRIED --********************************************* --*ŒΜŽ†“Š“όΓή°ΐ’Šo‚r‚p‚k --* --********************************************* [lselectRecords3] SELECT DTS_YEAR ,DTS_MONTH ,CD_GRADE_PRODUCT ,SUM(HARIJURYOU1) HARIJURYOU1 ,SUM(HARIJURYOU2) HARIJURYOU2 ,SUM(HARIJURYOU3) HARIJURYOU3 ,SUM(HARIJURYOU4) HARIJURYOU4 ,SUM(HARIJURYOU5) HARIJURYOU5 ,SUM(HARIJURYOU6) HARIJURYOU6 ,SUM(HARIJURYOU7) HARIJURYOU7 ,SUM(HARIJURYOU8) HARIJURYOU8 ,SUM(HARIJURYOU9) HARIJURYOU9 ,SUM(HARIJURYOU10) HARIJURYOU10 ,SUM(HARIJURYOU11) HARIJURYOU11 ,SUM(HARIJURYOU12) HARIJURYOU12 ,SUM(HARIJURYOU13) HARIJURYOU13 ,SUM(HARIJURYOU14) HARIJURYOU14 ,SUM(HARIJURYOU15) HARIJURYOU15 ,SUM(HARIJURYOU16) HARIJURYOU16 ,SUM(HARIJURYOU17) HARIJURYOU17 ,SUM(HARIJURYOU18) HARIJURYOU18 ,SUM(HARIJURYOU19) HARIJURYOU19 ,SUM(HARIJURYOU20) HARIJURYOU20 ,SUM(HARIJURYOU21) HARIJURYOU21 ,SUM(HARIJURYOU22) HARIJURYOU22 ,SUM(HARIJURYOU23) HARIJURYOU23 ,SUM(HARIJURYOU24) HARIJURYOU24 ,SUM(HARIJURYOU25) HARIJURYOU25 ,SUM(HARIJURYOU26) HARIJURYOU26 ,SUM(HARIJURYOU27) HARIJURYOU27 ,SUM(HARIJURYOU28) HARIJURYOU28 ,SUM(HARIJURYOU29) HARIJURYOU29 ,SUM(HARIJURYOU30) HARIJURYOU30 ,SUM(HARIJURYOU31) HARIJURYOU31 ,SUM(TANAWP1) TANAWP1 ,SUM(TANAWP2) TANAWP2 ,SUM(TANAWP3) TANAWP3 ,SUM(TANAWP4) TANAWP4 ,SUM(TANAWP5) TANAWP5 ,SUM(TANAWP6) TANAWP6 ,SUM(TANAWP7) TANAWP7 ,SUM(TANAWP8) TANAWP8 ,SUM(TANAWP9) TANAWP9 ,SUM(TANAWP10) TANAWP10 ,SUM(TANAWP11) TANAWP11 ,SUM(TANAWP12) TANAWP12 ,SUM(TANAWP13) TANAWP13 ,SUM(TANAWP14) TANAWP14 ,SUM(TANAWP15) TANAWP15 ,SUM(TANAWP16) TANAWP16 ,SUM(TANAWP17) TANAWP17 ,SUM(TANAWP18) TANAWP18 ,SUM(TANAWP19) TANAWP19 ,SUM(TANAWP20) TANAWP20 ,SUM(TANAWP21) TANAWP21 ,SUM(TANAWP22) TANAWP22 ,SUM(TANAWP23) TANAWP23 ,SUM(TANAWP24) TANAWP24 ,SUM(TANAWP25) TANAWP25 ,SUM(TANAWP26) TANAWP26 ,SUM(TANAWP27) TANAWP27 ,SUM(TANAWP28) TANAWP28 ,SUM(TANAWP29) TANAWP29 ,SUM(TANAWP30) TANAWP30 ,SUM(TANAWP31) TANAWP31 FROM( SELECT DTS_YEAR ,DTS_MONTH ,CD_GRADE_PRODUCT ,coalesce(sum(CASE WHEN DTS_DAY = 01 THEN WEIGHT_WP ELSE 0 END), 0) HARIJURYOU1 ,coalesce(sum(CASE WHEN DTS_DAY = 02 THEN WEIGHT_WP ELSE 0 END), 0) HARIJURYOU2 ,coalesce(sum(CASE WHEN DTS_DAY = 03 THEN WEIGHT_WP ELSE 0 END), 0) HARIJURYOU3 ,coalesce(sum(CASE WHEN DTS_DAY = 04 THEN WEIGHT_WP ELSE 0 END), 0) HARIJURYOU4 ,coalesce(sum(CASE WHEN DTS_DAY = 05 THEN WEIGHT_WP ELSE 0 END), 0) HARIJURYOU5 ,coalesce(sum(CASE WHEN DTS_DAY = 06 THEN WEIGHT_WP ELSE 0 END), 0) HARIJURYOU6 ,coalesce(sum(CASE WHEN DTS_DAY = 07 THEN WEIGHT_WP ELSE 0 END), 0) HARIJURYOU7 ,coalesce(sum(CASE WHEN DTS_DAY = 08 THEN WEIGHT_WP ELSE 0 END), 0) HARIJURYOU8 ,coalesce(sum(CASE WHEN DTS_DAY = 09 THEN WEIGHT_WP ELSE 0 END), 0) HARIJURYOU9 ,coalesce(sum(CASE WHEN DTS_DAY = 10 THEN WEIGHT_WP ELSE 0 END), 0) HARIJURYOU10 ,coalesce(sum(CASE WHEN DTS_DAY = 11 THEN WEIGHT_WP ELSE 0 END), 0) HARIJURYOU11 ,coalesce(sum(CASE WHEN DTS_DAY = 12 THEN WEIGHT_WP ELSE 0 END), 0) HARIJURYOU12 ,coalesce(sum(CASE WHEN DTS_DAY = 13 THEN WEIGHT_WP ELSE 0 END), 0) HARIJURYOU13 ,coalesce(sum(CASE WHEN DTS_DAY = 14 THEN WEIGHT_WP ELSE 0 END), 0) HARIJURYOU14 ,coalesce(sum(CASE WHEN DTS_DAY = 15 THEN WEIGHT_WP ELSE 0 END), 0) HARIJURYOU15 ,coalesce(sum(CASE WHEN DTS_DAY = 16 THEN WEIGHT_WP ELSE 0 END), 0) HARIJURYOU16 ,coalesce(sum(CASE WHEN DTS_DAY = 17 THEN WEIGHT_WP ELSE 0 END), 0) HARIJURYOU17 ,coalesce(sum(CASE WHEN DTS_DAY = 18 THEN WEIGHT_WP ELSE 0 END), 0) HARIJURYOU18 ,coalesce(sum(CASE WHEN DTS_DAY = 19 THEN WEIGHT_WP ELSE 0 END), 0) HARIJURYOU19 ,coalesce(sum(CASE WHEN DTS_DAY = 20 THEN WEIGHT_WP ELSE 0 END), 0) HARIJURYOU20 ,coalesce(sum(CASE WHEN DTS_DAY = 21 THEN WEIGHT_WP ELSE 0 END), 0) HARIJURYOU21 ,coalesce(sum(CASE WHEN DTS_DAY = 22 THEN WEIGHT_WP ELSE 0 END), 0) HARIJURYOU22 ,coalesce(sum(CASE WHEN DTS_DAY = 23 THEN WEIGHT_WP ELSE 0 END), 0) HARIJURYOU23 ,coalesce(sum(CASE WHEN DTS_DAY = 24 THEN WEIGHT_WP ELSE 0 END), 0) HARIJURYOU24 ,coalesce(sum(CASE WHEN DTS_DAY = 25 THEN WEIGHT_WP ELSE 0 END), 0) HARIJURYOU25 ,coalesce(sum(CASE WHEN DTS_DAY = 26 THEN WEIGHT_WP ELSE 0 END), 0) HARIJURYOU26 ,coalesce(sum(CASE WHEN DTS_DAY = 27 THEN WEIGHT_WP ELSE 0 END), 0) HARIJURYOU27 ,coalesce(sum(CASE WHEN DTS_DAY = 28 THEN WEIGHT_WP ELSE 0 END), 0) HARIJURYOU28 ,coalesce(sum(CASE WHEN DTS_DAY = 29 THEN WEIGHT_WP ELSE 0 END), 0) HARIJURYOU29 ,coalesce(sum(CASE WHEN DTS_DAY = 30 THEN WEIGHT_WP ELSE 0 END), 0) HARIJURYOU30 ,coalesce(sum(CASE WHEN DTS_DAY = 31 THEN WEIGHT_WP ELSE 0 END), 0) HARIJURYOU31 ,0 TANAWP1 ,0 TANAWP2 ,0 TANAWP3 ,0 TANAWP4 ,0 TANAWP5 ,0 TANAWP6 ,0 TANAWP7 ,0 TANAWP8 ,0 TANAWP9 ,0 TANAWP10 ,0 TANAWP11 ,0 TANAWP12 ,0 TANAWP13 ,0 TANAWP14 ,0 TANAWP15 ,0 TANAWP16 ,0 TANAWP17 ,0 TANAWP18 ,0 TANAWP19 ,0 TANAWP20 ,0 TANAWP21 ,0 TANAWP22 ,0 TANAWP23 ,0 TANAWP24 ,0 TANAWP25 ,0 TANAWP26 ,0 TANAWP27 ,0 TANAWP28 ,0 TANAWP29 ,0 TANAWP30 ,0 TANAWP31 FROM T_SHIP_WP TSW WHERE DTS_YEAR = ('&1' ::text) ::numeric AND DTS_MONTH = ('&2' ::text) ::numeric AND (('&3' = '') OR (CD_GRADE_PRODUCT = ('&3' ::text) ::numeric)) AND FLG_DELETE = 0 GROUP BY DTS_YEAR, DTS_MONTH , CD_GRADE_PRODUCT union SELECT DTS_YEAR ,DTS_MONTH ,CD_GREAD CD_GRADE_PRODUCT ,0 HARIJURYOU1 ,0 HARIJURYOU2 ,0 HARIJURYOU3 ,0 HARIJURYOU4 ,0 HARIJURYOU5 ,0 HARIJURYOU6 ,0 HARIJURYOU7 ,0 HARIJURYOU8 ,0 HARIJURYOU9 ,0 HARIJURYOU10 ,0 HARIJURYOU11 ,0 HARIJURYOU12 ,0 HARIJURYOU13 ,0 HARIJURYOU14 ,0 HARIJURYOU15 ,0 HARIJURYOU16 ,0 HARIJURYOU17 ,0 HARIJURYOU18 ,0 HARIJURYOU19 ,0 HARIJURYOU20 ,0 HARIJURYOU21 ,0 HARIJURYOU22 ,0 HARIJURYOU23 ,0 HARIJURYOU24 ,0 HARIJURYOU25 ,0 HARIJURYOU26 ,0 HARIJURYOU27 ,0 HARIJURYOU28 ,0 HARIJURYOU29 ,0 HARIJURYOU30 ,0 HARIJURYOU31 ,coalesce(sum(CASE WHEN DTS_DAY = 01 THEN WEIGHT ELSE 0 END), 0) TANAWP1 ,coalesce(sum(CASE WHEN DTS_DAY = 02 THEN WEIGHT ELSE 0 END), 0) TANAWP2 ,coalesce(sum(CASE WHEN DTS_DAY = 03 THEN WEIGHT ELSE 0 END), 0) TANAWP3 ,coalesce(sum(CASE WHEN DTS_DAY = 04 THEN WEIGHT ELSE 0 END), 0) TANAWP4 ,coalesce(sum(CASE WHEN DTS_DAY = 05 THEN WEIGHT ELSE 0 END), 0) TANAWP5 ,coalesce(sum(CASE WHEN DTS_DAY = 06 THEN WEIGHT ELSE 0 END), 0) TANAWP6 ,coalesce(sum(CASE WHEN DTS_DAY = 07 THEN WEIGHT ELSE 0 END), 0) TANAWP7 ,coalesce(sum(CASE WHEN DTS_DAY = 08 THEN WEIGHT ELSE 0 END), 0) TANAWP8 ,coalesce(sum(CASE WHEN DTS_DAY = 09 THEN WEIGHT ELSE 0 END), 0) TANAWP9 ,coalesce(sum(CASE WHEN DTS_DAY = 10 THEN WEIGHT ELSE 0 END), 0) TANAWP10 ,coalesce(sum(CASE WHEN DTS_DAY = 11 THEN WEIGHT ELSE 0 END), 0) TANAWP11 ,coalesce(sum(CASE WHEN DTS_DAY = 12 THEN WEIGHT ELSE 0 END), 0) TANAWP12 ,coalesce(sum(CASE WHEN DTS_DAY = 13 THEN WEIGHT ELSE 0 END), 0) TANAWP13 ,coalesce(sum(CASE WHEN DTS_DAY = 14 THEN WEIGHT ELSE 0 END), 0) TANAWP14 ,coalesce(sum(CASE WHEN DTS_DAY = 15 THEN WEIGHT ELSE 0 END), 0) TANAWP15 ,coalesce(sum(CASE WHEN DTS_DAY = 16 THEN WEIGHT ELSE 0 END), 0) TANAWP16 ,coalesce(sum(CASE WHEN DTS_DAY = 17 THEN WEIGHT ELSE 0 END), 0) TANAWP17 ,coalesce(sum(CASE WHEN DTS_DAY = 18 THEN WEIGHT ELSE 0 END), 0) TANAWP18 ,coalesce(sum(CASE WHEN DTS_DAY = 19 THEN WEIGHT ELSE 0 END), 0) TANAWP19 ,coalesce(sum(CASE WHEN DTS_DAY = 20 THEN WEIGHT ELSE 0 END), 0) TANAWP20 ,coalesce(sum(CASE WHEN DTS_DAY = 21 THEN WEIGHT ELSE 0 END), 0) TANAWP21 ,coalesce(sum(CASE WHEN DTS_DAY = 22 THEN WEIGHT ELSE 0 END), 0) TANAWP22 ,coalesce(sum(CASE WHEN DTS_DAY = 23 THEN WEIGHT ELSE 0 END), 0) TANAWP23 ,coalesce(sum(CASE WHEN DTS_DAY = 24 THEN WEIGHT ELSE 0 END), 0) TANAWP24 ,coalesce(sum(CASE WHEN DTS_DAY = 25 THEN WEIGHT ELSE 0 END), 0) TANAWP25 ,coalesce(sum(CASE WHEN DTS_DAY = 26 THEN WEIGHT ELSE 0 END), 0) TANAWP26 ,coalesce(sum(CASE WHEN DTS_DAY = 27 THEN WEIGHT ELSE 0 END), 0) TANAWP27 ,coalesce(sum(CASE WHEN DTS_DAY = 28 THEN WEIGHT ELSE 0 END), 0) TANAWP28 ,coalesce(sum(CASE WHEN DTS_DAY = 29 THEN WEIGHT ELSE 0 END), 0) TANAWP29 ,coalesce(sum(CASE WHEN DTS_DAY = 30 THEN WEIGHT ELSE 0 END), 0) TANAWP30 ,coalesce(sum(CASE WHEN DTS_DAY = 31 THEN WEIGHT ELSE 0 END), 0) TANAWP31 FROM T_ADJUSTMENT WHERE DTS_YEAR = ('&1' ::text) ::numeric AND DTS_MONTH = ('&2' ::text) ::numeric AND (('&3' = '') OR (CD_GREAD = ('&3' ::text) ::numeric)) AND FLG_DELETE = 0 AND DIV_WP_PRODUCT = 0 GROUP BY DTS_YEAR, DTS_MONTH, CD_GREAD ) alias198 GROUP BY DTS_YEAR, DTS_MONTH, CD_GRADE_PRODUCT --********************************************* --*‚l‚b‚o‘€‹ΖΓή°ΐ’Šo‚r‚p‚k --* --********************************************* [lselectRecords4] SELECT DTS_YEAR ,DTS_MONTH ,CD_GREAD ,coalesce(sum(CASE WHEN DTS_DAY = 01 THEN TIME_OPERATE_PLAN ELSE 0 END), 0) SOUGYOUYOTEI1 ,coalesce(sum(CASE WHEN DTS_DAY = 02 THEN TIME_OPERATE_PLAN ELSE 0 END), 0) SOUGYOUYOTEI2 ,coalesce(sum(CASE WHEN DTS_DAY = 03 THEN TIME_OPERATE_PLAN ELSE 0 END), 0) SOUGYOUYOTEI3 ,coalesce(sum(CASE WHEN DTS_DAY = 04 THEN TIME_OPERATE_PLAN ELSE 0 END), 0) SOUGYOUYOTEI4 ,coalesce(sum(CASE WHEN DTS_DAY = 05 THEN TIME_OPERATE_PLAN ELSE 0 END), 0) SOUGYOUYOTEI5 ,coalesce(sum(CASE WHEN DTS_DAY = 06 THEN TIME_OPERATE_PLAN ELSE 0 END), 0) SOUGYOUYOTEI6 ,coalesce(sum(CASE WHEN DTS_DAY = 07 THEN TIME_OPERATE_PLAN ELSE 0 END), 0) SOUGYOUYOTEI7 ,coalesce(sum(CASE WHEN DTS_DAY = 08 THEN TIME_OPERATE_PLAN ELSE 0 END), 0) SOUGYOUYOTEI8 ,coalesce(sum(CASE WHEN DTS_DAY = 09 THEN TIME_OPERATE_PLAN ELSE 0 END), 0) SOUGYOUYOTEI9 ,coalesce(sum(CASE WHEN DTS_DAY = 10 THEN TIME_OPERATE_PLAN ELSE 0 END), 0) SOUGYOUYOTEI10 ,coalesce(sum(CASE WHEN DTS_DAY = 11 THEN TIME_OPERATE_PLAN ELSE 0 END), 0) SOUGYOUYOTEI11 ,coalesce(sum(CASE WHEN DTS_DAY = 12 THEN TIME_OPERATE_PLAN ELSE 0 END), 0) SOUGYOUYOTEI12 ,coalesce(sum(CASE WHEN DTS_DAY = 13 THEN TIME_OPERATE_PLAN ELSE 0 END), 0) SOUGYOUYOTEI13 ,coalesce(sum(CASE WHEN DTS_DAY = 14 THEN TIME_OPERATE_PLAN ELSE 0 END), 0) SOUGYOUYOTEI14 ,coalesce(sum(CASE WHEN DTS_DAY = 15 THEN TIME_OPERATE_PLAN ELSE 0 END), 0) SOUGYOUYOTEI15 ,coalesce(sum(CASE WHEN DTS_DAY = 16 THEN TIME_OPERATE_PLAN ELSE 0 END), 0) SOUGYOUYOTEI16 ,coalesce(sum(CASE WHEN DTS_DAY = 17 THEN TIME_OPERATE_PLAN ELSE 0 END), 0) SOUGYOUYOTEI17 ,coalesce(sum(CASE WHEN DTS_DAY = 18 THEN TIME_OPERATE_PLAN ELSE 0 END), 0) SOUGYOUYOTEI18 ,coalesce(sum(CASE WHEN DTS_DAY = 19 THEN TIME_OPERATE_PLAN ELSE 0 END), 0) SOUGYOUYOTEI19 ,coalesce(sum(CASE WHEN DTS_DAY = 20 THEN TIME_OPERATE_PLAN ELSE 0 END), 0) SOUGYOUYOTEI20 ,coalesce(sum(CASE WHEN DTS_DAY = 21 THEN TIME_OPERATE_PLAN ELSE 0 END), 0) SOUGYOUYOTEI21 ,coalesce(sum(CASE WHEN DTS_DAY = 22 THEN TIME_OPERATE_PLAN ELSE 0 END), 0) SOUGYOUYOTEI22 ,coalesce(sum(CASE WHEN DTS_DAY = 23 THEN TIME_OPERATE_PLAN ELSE 0 END), 0) SOUGYOUYOTEI23 ,coalesce(sum(CASE WHEN DTS_DAY = 24 THEN TIME_OPERATE_PLAN ELSE 0 END), 0) SOUGYOUYOTEI24 ,coalesce(sum(CASE WHEN DTS_DAY = 25 THEN TIME_OPERATE_PLAN ELSE 0 END), 0) SOUGYOUYOTEI25 ,coalesce(sum(CASE WHEN DTS_DAY = 26 THEN TIME_OPERATE_PLAN ELSE 0 END), 0) SOUGYOUYOTEI26 ,coalesce(sum(CASE WHEN DTS_DAY = 27 THEN TIME_OPERATE_PLAN ELSE 0 END), 0) SOUGYOUYOTEI27 ,coalesce(sum(CASE WHEN DTS_DAY = 28 THEN TIME_OPERATE_PLAN ELSE 0 END), 0) SOUGYOUYOTEI28 ,coalesce(sum(CASE WHEN DTS_DAY = 29 THEN TIME_OPERATE_PLAN ELSE 0 END), 0) SOUGYOUYOTEI29 ,coalesce(sum(CASE WHEN DTS_DAY = 30 THEN TIME_OPERATE_PLAN ELSE 0 END), 0) SOUGYOUYOTEI30 ,coalesce(sum(CASE WHEN DTS_DAY = 31 THEN TIME_OPERATE_PLAN ELSE 0 END), 0) SOUGYOUYOTEI31 ,coalesce(sum(CASE WHEN DTS_DAY = 01 THEN TIME_OPERATE ELSE 0 END), 0) PM1 ,coalesce(sum(CASE WHEN DTS_DAY = 02 THEN TIME_OPERATE ELSE 0 END), 0) PM2 ,coalesce(sum(CASE WHEN DTS_DAY = 03 THEN TIME_OPERATE ELSE 0 END), 0) PM3 ,coalesce(sum(CASE WHEN DTS_DAY = 04 THEN TIME_OPERATE ELSE 0 END), 0) PM4 ,coalesce(sum(CASE WHEN DTS_DAY = 05 THEN TIME_OPERATE ELSE 0 END), 0) PM5 ,coalesce(sum(CASE WHEN DTS_DAY = 06 THEN TIME_OPERATE ELSE 0 END), 0) PM6 ,coalesce(sum(CASE WHEN DTS_DAY = 07 THEN TIME_OPERATE ELSE 0 END), 0) PM7 ,coalesce(sum(CASE WHEN DTS_DAY = 08 THEN TIME_OPERATE ELSE 0 END), 0) PM8 ,coalesce(sum(CASE WHEN DTS_DAY = 09 THEN TIME_OPERATE ELSE 0 END), 0) PM9 ,coalesce(sum(CASE WHEN DTS_DAY = 10 THEN TIME_OPERATE ELSE 0 END), 0) PM10 ,coalesce(sum(CASE WHEN DTS_DAY = 11 THEN TIME_OPERATE ELSE 0 END), 0) PM11 ,coalesce(sum(CASE WHEN DTS_DAY = 12 THEN TIME_OPERATE ELSE 0 END), 0) PM12 ,coalesce(sum(CASE WHEN DTS_DAY = 13 THEN TIME_OPERATE ELSE 0 END), 0) PM13 ,coalesce(sum(CASE WHEN DTS_DAY = 14 THEN TIME_OPERATE ELSE 0 END), 0) PM14 ,coalesce(sum(CASE WHEN DTS_DAY = 15 THEN TIME_OPERATE ELSE 0 END), 0) PM15 ,coalesce(sum(CASE WHEN DTS_DAY = 16 THEN TIME_OPERATE ELSE 0 END), 0) PM16 ,coalesce(sum(CASE WHEN DTS_DAY = 17 THEN TIME_OPERATE ELSE 0 END), 0) PM17 ,coalesce(sum(CASE WHEN DTS_DAY = 18 THEN TIME_OPERATE ELSE 0 END), 0) PM18 ,coalesce(sum(CASE WHEN DTS_DAY = 19 THEN TIME_OPERATE ELSE 0 END), 0) PM19 ,coalesce(sum(CASE WHEN DTS_DAY = 20 THEN TIME_OPERATE ELSE 0 END), 0) PM20 ,coalesce(sum(CASE WHEN DTS_DAY = 21 THEN TIME_OPERATE ELSE 0 END), 0) PM21 ,coalesce(sum(CASE WHEN DTS_DAY = 22 THEN TIME_OPERATE ELSE 0 END), 0) PM22 ,coalesce(sum(CASE WHEN DTS_DAY = 23 THEN TIME_OPERATE ELSE 0 END), 0) PM23 ,coalesce(sum(CASE WHEN DTS_DAY = 24 THEN TIME_OPERATE ELSE 0 END), 0) PM24 ,coalesce(sum(CASE WHEN DTS_DAY = 25 THEN TIME_OPERATE ELSE 0 END), 0) PM25 ,coalesce(sum(CASE WHEN DTS_DAY = 26 THEN TIME_OPERATE ELSE 0 END), 0) PM26 ,coalesce(sum(CASE WHEN DTS_DAY = 27 THEN TIME_OPERATE ELSE 0 END), 0) PM27 ,coalesce(sum(CASE WHEN DTS_DAY = 28 THEN TIME_OPERATE ELSE 0 END), 0) PM28 ,coalesce(sum(CASE WHEN DTS_DAY = 29 THEN TIME_OPERATE ELSE 0 END), 0) PM29 ,coalesce(sum(CASE WHEN DTS_DAY = 30 THEN TIME_OPERATE ELSE 0 END), 0) PM30 ,coalesce(sum(CASE WHEN DTS_DAY = 31 THEN TIME_OPERATE ELSE 0 END), 0) PM31 ,coalesce(sum(CASE WHEN DTS_DAY = 01 THEN WEIGHT_PLAN ELSE 0 END), 0) SEISANYOTEI1 ,coalesce(sum(CASE WHEN DTS_DAY = 02 THEN WEIGHT_PLAN ELSE 0 END), 0) SEISANYOTEI2 ,coalesce(sum(CASE WHEN DTS_DAY = 03 THEN WEIGHT_PLAN ELSE 0 END), 0) SEISANYOTEI3 ,coalesce(sum(CASE WHEN DTS_DAY = 04 THEN WEIGHT_PLAN ELSE 0 END), 0) SEISANYOTEI4 ,coalesce(sum(CASE WHEN DTS_DAY = 05 THEN WEIGHT_PLAN ELSE 0 END), 0) SEISANYOTEI5 ,coalesce(sum(CASE WHEN DTS_DAY = 06 THEN WEIGHT_PLAN ELSE 0 END), 0) SEISANYOTEI6 ,coalesce(sum(CASE WHEN DTS_DAY = 07 THEN WEIGHT_PLAN ELSE 0 END), 0) SEISANYOTEI7 ,coalesce(sum(CASE WHEN DTS_DAY = 08 THEN WEIGHT_PLAN ELSE 0 END), 0) SEISANYOTEI8 ,coalesce(sum(CASE WHEN DTS_DAY = 09 THEN WEIGHT_PLAN ELSE 0 END), 0) SEISANYOTEI9 ,coalesce(sum(CASE WHEN DTS_DAY = 10 THEN WEIGHT_PLAN ELSE 0 END), 0) SEISANYOTEI10 ,coalesce(sum(CASE WHEN DTS_DAY = 11 THEN WEIGHT_PLAN ELSE 0 END), 0) SEISANYOTEI11 ,coalesce(sum(CASE WHEN DTS_DAY = 12 THEN WEIGHT_PLAN ELSE 0 END), 0) SEISANYOTEI12 ,coalesce(sum(CASE WHEN DTS_DAY = 13 THEN WEIGHT_PLAN ELSE 0 END), 0) SEISANYOTEI13 ,coalesce(sum(CASE WHEN DTS_DAY = 14 THEN WEIGHT_PLAN ELSE 0 END), 0) SEISANYOTEI14 ,coalesce(sum(CASE WHEN DTS_DAY = 15 THEN WEIGHT_PLAN ELSE 0 END), 0) SEISANYOTEI15 ,coalesce(sum(CASE WHEN DTS_DAY = 16 THEN WEIGHT_PLAN ELSE 0 END), 0) SEISANYOTEI16 ,coalesce(sum(CASE WHEN DTS_DAY = 17 THEN WEIGHT_PLAN ELSE 0 END), 0) SEISANYOTEI17 ,coalesce(sum(CASE WHEN DTS_DAY = 18 THEN WEIGHT_PLAN ELSE 0 END), 0) SEISANYOTEI18 ,coalesce(sum(CASE WHEN DTS_DAY = 19 THEN WEIGHT_PLAN ELSE 0 END), 0) SEISANYOTEI19 ,coalesce(sum(CASE WHEN DTS_DAY = 20 THEN WEIGHT_PLAN ELSE 0 END), 0) SEISANYOTEI20 ,coalesce(sum(CASE WHEN DTS_DAY = 21 THEN WEIGHT_PLAN ELSE 0 END), 0) SEISANYOTEI21 ,coalesce(sum(CASE WHEN DTS_DAY = 22 THEN WEIGHT_PLAN ELSE 0 END), 0) SEISANYOTEI22 ,coalesce(sum(CASE WHEN DTS_DAY = 23 THEN WEIGHT_PLAN ELSE 0 END), 0) SEISANYOTEI23 ,coalesce(sum(CASE WHEN DTS_DAY = 24 THEN WEIGHT_PLAN ELSE 0 END), 0) SEISANYOTEI24 ,coalesce(sum(CASE WHEN DTS_DAY = 25 THEN WEIGHT_PLAN ELSE 0 END), 0) SEISANYOTEI25 ,coalesce(sum(CASE WHEN DTS_DAY = 26 THEN WEIGHT_PLAN ELSE 0 END), 0) SEISANYOTEI26 ,coalesce(sum(CASE WHEN DTS_DAY = 27 THEN WEIGHT_PLAN ELSE 0 END), 0) SEISANYOTEI27 ,coalesce(sum(CASE WHEN DTS_DAY = 28 THEN WEIGHT_PLAN ELSE 0 END), 0) SEISANYOTEI28 ,coalesce(sum(CASE WHEN DTS_DAY = 29 THEN WEIGHT_PLAN ELSE 0 END), 0) SEISANYOTEI29 ,coalesce(sum(CASE WHEN DTS_DAY = 30 THEN WEIGHT_PLAN ELSE 0 END), 0) SEISANYOTEI30 ,coalesce(sum(CASE WHEN DTS_DAY = 31 THEN WEIGHT_PLAN ELSE 0 END), 0) SEISANYOTEI31 ,coalesce(sum(CASE WHEN DTS_DAY = 01 THEN TIME_PM_WORK ELSE 0 END), 0) SOUGYOU1 ,coalesce(sum(CASE WHEN DTS_DAY = 01 THEN TIME_PM_WORK ELSE 0 END), 0) SOUGYOU2 ,coalesce(sum(CASE WHEN DTS_DAY = 03 THEN TIME_PM_WORK ELSE 0 END), 0) SOUGYOU3 ,coalesce(sum(CASE WHEN DTS_DAY = 04 THEN TIME_PM_WORK ELSE 0 END), 0) SOUGYOU4 ,coalesce(sum(CASE WHEN DTS_DAY = 05 THEN TIME_PM_WORK ELSE 0 END), 0) SOUGYOU5 ,coalesce(sum(CASE WHEN DTS_DAY = 06 THEN TIME_PM_WORK ELSE 0 END), 0) SOUGYOU6 ,coalesce(sum(CASE WHEN DTS_DAY = 07 THEN TIME_PM_WORK ELSE 0 END), 0) SOUGYOU7 ,coalesce(sum(CASE WHEN DTS_DAY = 08 THEN TIME_PM_WORK ELSE 0 END), 0) SOUGYOU8 ,coalesce(sum(CASE WHEN DTS_DAY = 09 THEN TIME_PM_WORK ELSE 0 END), 0) SOUGYOU9 ,coalesce(sum(CASE WHEN DTS_DAY = 10 THEN TIME_PM_WORK ELSE 0 END), 0) SOUGYOU10 ,coalesce(sum(CASE WHEN DTS_DAY = 11 THEN TIME_PM_WORK ELSE 0 END), 0) SOUGYOU11 ,coalesce(sum(CASE WHEN DTS_DAY = 12 THEN TIME_PM_WORK ELSE 0 END), 0) SOUGYOU12 ,coalesce(sum(CASE WHEN DTS_DAY = 13 THEN TIME_PM_WORK ELSE 0 END), 0) SOUGYOU13 ,coalesce(sum(CASE WHEN DTS_DAY = 14 THEN TIME_PM_WORK ELSE 0 END), 0) SOUGYOU14 ,coalesce(sum(CASE WHEN DTS_DAY = 15 THEN TIME_PM_WORK ELSE 0 END), 0) SOUGYOU15 ,coalesce(sum(CASE WHEN DTS_DAY = 16 THEN TIME_PM_WORK ELSE 0 END), 0) SOUGYOU16 ,coalesce(sum(CASE WHEN DTS_DAY = 17 THEN TIME_PM_WORK ELSE 0 END), 0) SOUGYOU17 ,coalesce(sum(CASE WHEN DTS_DAY = 18 THEN TIME_PM_WORK ELSE 0 END), 0) SOUGYOU18 ,coalesce(sum(CASE WHEN DTS_DAY = 19 THEN TIME_PM_WORK ELSE 0 END), 0) SOUGYOU19 ,coalesce(sum(CASE WHEN DTS_DAY = 20 THEN TIME_PM_WORK ELSE 0 END), 0) SOUGYOU20 ,coalesce(sum(CASE WHEN DTS_DAY = 21 THEN TIME_PM_WORK ELSE 0 END), 0) SOUGYOU21 ,coalesce(sum(CASE WHEN DTS_DAY = 22 THEN TIME_PM_WORK ELSE 0 END), 0) SOUGYOU22 ,coalesce(sum(CASE WHEN DTS_DAY = 23 THEN TIME_PM_WORK ELSE 0 END), 0) SOUGYOU23 ,coalesce(sum(CASE WHEN DTS_DAY = 24 THEN TIME_PM_WORK ELSE 0 END), 0) SOUGYOU24 ,coalesce(sum(CASE WHEN DTS_DAY = 25 THEN TIME_PM_WORK ELSE 0 END), 0) SOUGYOU25 ,coalesce(sum(CASE WHEN DTS_DAY = 26 THEN TIME_PM_WORK ELSE 0 END), 0) SOUGYOU26 ,coalesce(sum(CASE WHEN DTS_DAY = 27 THEN TIME_PM_WORK ELSE 0 END), 0) SOUGYOU27 ,coalesce(sum(CASE WHEN DTS_DAY = 28 THEN TIME_PM_WORK ELSE 0 END), 0) SOUGYOU28 ,coalesce(sum(CASE WHEN DTS_DAY = 29 THEN TIME_PM_WORK ELSE 0 END), 0) SOUGYOU29 ,coalesce(sum(CASE WHEN DTS_DAY = 30 THEN TIME_PM_WORK ELSE 0 END), 0) SOUGYOU30 ,coalesce(sum(CASE WHEN DTS_DAY = 31 THEN TIME_PM_WORK ELSE 0 END), 0) SOUGYOU31 FROM T_MCP WHERE DTS_YEAR = ('&1' ::text) ::numeric AND DTS_MONTH = ('&2' ::text) ::numeric AND (('&3' = '') OR (CD_GREAD = ('&3' ::text) ::numeric)) AND FLG_DELETE = 0 GROUP BY DTS_YEAR, DTS_MONTH , CD_GREAD --********************************************* --*‘€‹ΖŽžŠΤE‚o‚l‰^“]ŽžŠΤ --* --********************************************* [lDB_TIME_PM] select PRC_GET_TIMES('&1', '&2', '01', '&1', '&2', '&6') --BEGIN PRC_GET_TIMES('&1', '&2', '01','&1', '&2', '&6');end;