XinYu_RZMES/.svn/pristine/65/65e19779d803810a87c05286d6df9d1df1cfc40b.svn-base

<?xml version="1.0" encoding='UTF-8'?>
<queryMap desc="STEEL MAKING QUERY ">
	<query id="UIH050200_01.select" desc="钢卷库库区信息查询">
        <![CDATA[
/**********************************************************************************
		   SELECT  a.YARD_CLF,a.ROW_COUNT||'__('||nvl(b.ROW_COUNT,0)||')' ROW_COUNT,
               RHF_SCH
         FROM                                                 
         (
          SELECT DISTINCT YARD_CLF
             	,COUNT(COIL_NO) ROW_COUNT
         	FROM TBH05_COIL_YARD  
         	WHERE SUBSTR(YARD_CLF,1,2) = ? 
         	GROUP BY YARD_CLF
         ) a,
         (
          SELECT DISTINCT YARD_CLF
	           ,COUNT(a.COIL_NO) ROW_COUNT
	           ,'Y' RHF_SCH
	        FROM TBH05_COIL_YARD a,TBH02_COIL_COMM b   
	        WHERE b.TOT_DEC_GRD = '1'
             AND  b.OLD_SAMPL_NO = a.coil_no
			 AND  b.COIL_STAT = '2' 
	         AND  SUBSTR(YARD_CLF,1,2) = ? 
	        GROUP BY YARD_CLF
	     ) b    
       WHERE a.YARD_CLF = b.YARD_CLF(+)
		     order by a.YARD_CLF
***************************************************************/
	SELECT  a.YARD_CLF,a.ROW_COUNT||'__('||nvl(b.ROW_COUNT,0)||')' ROW_COUNT
               
         FROM                                                 
         (
          SELECT DISTINCT YARD_CLF
             	,COUNT(YARD_CLF) ROW_COUNT
         	FROM TBH05_COIL_YARD  
         	WHERE SUBSTR(YARD_CLF,1,2) = ? 
         	GROUP BY YARD_CLF
         ) a,
         (
          SELECT DISTINCT YARD_CLF
             	,COUNT(COIL_NO) ROW_COUNT
         	FROM TBH05_COIL_YARD  
         	WHERE SUBSTR(YARD_CLF,1,2) = ? 
         	GROUP BY YARD_CLF
	     ) b    
       WHERE a.YARD_CLF = b.YARD_CLF(+)
		     order by a.YARD_CLF
        ]]>
    </query>
    <query id="UIH050200_02.select" desc="钢卷库统计数据来源">
        <![CDATA[
      SELECT
         ALL_T_CNT,ALL_GRD_T_CNT,YARD_CLF_CNT,YARD_CLF_GRD_CNT,
         SPM_YN_T_CNT,YARD_CLF_SPM_CNT
       FROM
       (
          SELECT  COUNT(COIL_NO) ALL_T_CNT
           FROM TBH05_COIL_YARD  
          WHERE YARD_CLF LIKE 'C1%' 
          OR YARD_CLF LIKE 'C2%' 
          OR YARD_CLF LIKE 'C3%'
          OR YARD_CLF LIKE 'C4%'
  
       )  T1,
       (
          SELECT  COUNT(a.COIL_NO) ALL_GRD_T_CNT
           FROM TBH05_COIL_YARD a,TBH02_COIL_COMM b 
           WHERE b.TOT_DEC_GRD = '1'
          AND  a.COIL_NO = b.OLD_SAMPL_NO 
          AND  b.COIL_STAT = '2'   
          AND  (YARD_CLF LIKE 'C1%' 
               OR YARD_CLF LIKE 'C2%' 
               OR YARD_CLF LIKE 'C3%'
               OR YARD_CLF LIKE 'C4%'
              )
  
       )  T2,
       (
           SELECT  COUNT(COIL_NO) YARD_CLF_CNT
           FROM TBH05_COIL_YARD  
          WHERE YARD_CLF LIKE ?||'%'
  
       )  T3,
       (
          SELECT  COUNT(a.COIL_NO) YARD_CLF_GRD_CNT
           FROM TBH05_COIL_YARD a,TBH02_COIL_COMM b 
           WHERE b.TOT_DEC_GRD = '1'
          AND  a.COIL_NO = b.OLD_SAMPL_NO   
        AND  b.COIL_STAT = '2'
          AND  YARD_CLF LIKE ?||'%'
               
       ) T4, 
       (
           SELECT  COUNT(a.COIL_NO) SPM_YN_T_CNT
           FROM TBH05_COIL_YARD a,TBH02_COIL_COMM b 
           WHERE b.SPM_YN = '1'
          AND  a.COIL_NO = b.OLD_SAMPL_NO    
          AND  b.COIL_STAT = '2'
          AND  (YARD_CLF LIKE 'C1%' 
               OR YARD_CLF LIKE 'C2%' 
               OR YARD_CLF LIKE 'C3%'
               OR YARD_CLF LIKE 'C4%'
               )
       ) T5 ,
       (
          SELECT  COUNT(a.COIL_NO) YARD_CLF_SPM_CNT
           FROM TBH05_COIL_YARD a,TBH02_COIL_COMM b 
           WHERE b.SPM_YN = '1'
          AND  a.COIL_NO = b.OLD_SAMPL_NO    
        AND  b.COIL_STAT = '2'
          AND  YARD_CLF LIKE ?||'%'
     
       )  T6 
        ]]>
    </query>
</queryMap>