DZone Snippets is a public source code repository. Easily build up your personal collection of code snippets, categorize them with tags / keywords, and share them with the world

Snippets Manager

Snippets has posted 5883 posts at DZone. View Full User Profile

å·¥å•ç”Ÿæˆibatis Sqlå®šä¹‰

05.01.2012

| 1080 views |

        // å·¥å•ç”Ÿæˆibatis sqlå®šä¹‰

<?xml version="1.0" encoding="GBK" ?>
<!DOCTYPE sqlMap PUBLIC "-//iBATIS.com//DTD SQL Map 2.0//EN" "http://www.ibatis.com/dtd/sql-map-2.dtd">
<sqlMap namespace="VDK_DW_EN_GS_SERV_LEVEL">
    <typeAlias alias="VDK_DW_EN_GS_SERV_LEVEL"  type="java.util.HashMap" />
     <sql id="CREATE_PROCESS_ORD_SERV_DAYS">
        <isNotEmpty property="greaterOrdServDays" prepend="and">
 			ORD_SERV_DAYS > #greaterOrdServDays#
        </isNotEmpty>
     </sql>
    <sql id="NO_CREATE_PROCESS">
    	<![CDATA[
	    	and not exists (select id 
	    		from vodka.bpm_procinst p where
	    		p.is_deleted='n'
	    		and p.doc_type='Task_GsLevelOneCustomer' 
	    		and p.cust_id = VDK_DW_EN_GS_SERV_LEVEL.global_id
	    		and (p.procend_time is null or (p.procend_time > sysdate-#crateRepeatDays# and p.procend_time <= sysdate))
	    	)
    	]]>
    </sql>
    <sql id="NO_CREATE_PROCESS_FOR_2A">
    	<![CDATA[
	    	and not exists (select id 
	    		from vodka.bpm_procinst p where
	    		p.is_deleted='n'
	    		and p.doc_type in ('Task_Gs2ACustomerTask','Task_Gs2ACustomer')
	    		and p.cust_id = VDK_DW_EN_GS_SERV_LEVEL.global_id
	    		and p.procend_time is null
	    	)
    	]]>
    </sql>
    <sql id="LAST_PERIDO_IS_OVER">
    	<![CDATA[
    		and nvl((select max(vdap.UPDATE_TIME) from VDK_DOUBLE_A_POOL vdap 
    			where vdap.is_deleted='n'
	    		and vdap.DATA_TYPE='DOUBLE_A'
	    		and vdap.global_id = VDK_DW_EN_GS_SERV_LEVEL.global_id
	    		and vdap.status = 'close'),sysdate-#crateRepeatDays#) <= sysdate-#crateRepeatDays#
    	]]>
    </sql>
    <select id="SELECT_CREATE_PROCESS" resultMap="VDK_DW_EN_GS_SERV_LEVEL_RM" parameterClass="map">
    	<include refid="ALICRM_COMMON.ALI_PAGING_START"/>
		<include refid="COMMON_SELECT"/>
        <include refid="COMMON_CONDITION"/>
        <include refid="CREATE_PROCESS_ORD_SERV_DAYS"/>
        <include refid="NO_CREATE_PROCESS"/>
        <include refid="ALICRM_COMMON.ALI_ORDER"/>
        <include refid="ALICRM_COMMON.ALI_PAGING_END"/>
    </select>
    <select id="SELECT_CREATE_PROCESS_FOR_2A" resultMap="VDK_DW_EN_GS_SERV_LEVEL_RM" parameterClass="map">
    	<include refid="ALICRM_COMMON.ALI_PAGING_START"/>
		<include refid="COMMON_SELECT"/>
        <include refid="COMMON_CONDITION"/>
        <include refid="CREATE_PROCESS_ORD_SERV_DAYS"/>
        <include refid="NO_CREATE_PROCESS_FOR_2A"/>
        <include refid="LAST_PERIDO_IS_OVER"/>
        <include refid="ALICRM_COMMON.ALI_PAGING_END"/>
    </select>
    <parameterMap id="BUILD_DATA_PM" class="VDK_DW_EN_GS_SERV_LEVEL" >
		<parameter property="stateDate" jdbcType="VARCHAR" javaType="String" mode="IN"/>
	</parameterMap>
    <procedure id="UPDATE_BUILD_DATA" parameterMap="BUILD_DATA_PM">
		{call PRO_BUILD_GS_SERV_LEVEL(?)}
	</procedure>
	
	<select id="SELECT_COUNT_PAGECOUNT" resultClass="int" parameterClass="map">
		<![CDATA[
			SELECT count(*) FROM VDK_DW_EN_GS_SERV_LEVEL WHERE is_deleted='n' 
		]]>
		<include refid="COMMON_CONDITION"/>
        <include refid="CREATE_PROCESS_ORD_SERV_DAYS"/>
        <include refid="NO_CREATE_PROCESS_FOR_2A"/>
        <include refid="LAST_PERIDO_IS_OVER"/>
	</select> 
	
	<select id="SELECT_COUNT_CURRENT" resultClass="int" parameterClass="map">
		<![CDATA[
			select count(*) from dhw.dhw_sync_stat where task_id=2680424 and TABLE_NAME='en_gs_serv_level_fdt0' and TRUNC(GMT_DHW_ARRIVED)=TRUNC(sysdate)
		]]>
	</select> 
</sqlMap>

Tags: