| 12345678910111213141516171819202122232425262728293031323334 |
- <?xml version="1.0" encoding='UTF-8'?>
- <queryMap desc="炉次浇次编制结果分析">
- <query id="UID020030.select" desc="炉次浇次编制结果分析" fetchSize="10">
- <![CDATA[
- SELECT case when x.cast_no is null then DECODE(X.CAST_NO,NULL,'合计')
- when x.cast_no is not null then to_char(x.cast_no) end cast_no
- , COUNT(DISTINCT X.FAC_STL_GRP) AS GROUP_CNT
- , COUNT(DISTINCT X.ORD_NO_SEQ ) AS DST_ORD_CNT
- , SUM(X.ORD_CNT) AS SUM_ORD_CNT
- , SUM(X.ORD_WGT) AS SUM_ORD_WGT
- , SUM(DECODE(X.ORD_FL,'1',X.ORD_CNT)) AS ORD_FL1_CNT
- , SUM(DECODE(X.ORD_FL,'1',X.ORD_WGT)) AS ORD_FL1_WGT
- , SUM(DECODE(X.ORD_FL,'2',X.ORD_CNT)) AS ORD_FL2_CNT
- , SUM(DECODE(X.ORD_FL,'2',X.ORD_WGT)) AS ORD_FL2_WGT
- , ROUND(SUM(DECODE(X.ORD_FL,'2',X.ORD_WGT)) / SUM(X.ORD_WGT) * 100,2) AS ORD_FL2_PERCENT
- FROM (SELECT A.MAT_SPEC_SEQ AS CAST_NO
- , B.MAT_SPEC_SEQ AS CHARGE_NO
- , C.MAT_SPEC_SEQ AS SLAB_NO
- , C.PMAT_SPEC_SEQ, C.FAC_STL_GRP, C.ORD_FL, C.MAT_CNT, C.MAT_WGT
- , C.ORD_NO, C.ORD_SEQ, C.ORD_NO_SEQ, C.ORD_CNT, C.ORD_WGT
- FROM(SELECT * FROM TBD01_MATDN_RST WHERE MAT_TP = '01') A
- ,(SELECT * FROM TBD01_MATDN_RST WHERE MAT_TP = '02') B
- ,(SELECT Y.MAT_SPEC_SEQ, Y.PMAT_SPEC_SEQ, Y.FAC_STL_GRP, Y.ORD_FL, 1 AS MAT_CNT, Y.MAT_WGT
- , Y.ORD_NO, Y.ORD_SEQ, Y.ORD_NO||Y.ORD_SEQ AS ORD_NO_SEQ, X.ORD_CNT, X.ORD_WGT
- FROM TBE02_ORD_PRC X
- ,(SELECT * FROM TBD01_MATDN_RST WHERE MAT_TP = '03') Y
- WHERE Y.ORD_NO = X.ORD_NO(+)
- AND Y.ORD_SEQ = X.ORD_SEQ(+)) C
- WHERE A.MAT_SPEC_SEQ = B.PMAT_SPEC_SEQ(+)
- AND B.MAT_SPEC_SEQ = C.PMAT_SPEC_SEQ(+)) X
- GROUP BY rollup( X.CAST_NO)
- ]]>
- </query>
- </queryMap>
|
