在线配置工具的某段sql

<select id="queryOnlyProductBySearchCondition" parameterClass="java.util.Map"
		resultClass="ProductInfo">
      Select C.* from 
      (Select B.* from (SELECT A.*,rownum as num from 
       (select dpp.chsName 		 as chsName,	
               dpp.engname       as engName,
               dpp.producttypeid as productType,
               dpp.productcode   as productCode,
               dpp.productver    as productVersion,
               dpp.modulecode    as moduleCode,
               im.imagepath      as images,
               dpp.chsSimpleDesc as chsSimpleDesc,
               dpp.engSimpleDesc as engSimpleDesc,
               dpp.producteom    as noticeEOMDate,
               dpp.planeom       as planEOMDate,
               dc.nodeid         as pcNodeCode,
               dc.path			 as path,
               dp.productid      as productid,
               dc.sortNo         as sortNo,
               ln.sortNo         as lineSortNo,
               dpp.salesState    as salesState
          from V_PRODUCTTREE dc
          INNER JOIN D_PRODUCTLINE ln ON (dc.PRODUCTLINEID = ln.PRODUCTLINEID AND ln.AVAILABLE = 1)
         inner join DP_PRODUCTATTR dpp
            on dc.productcode = dpp.productcode
           and dpp.available = 1
           <isEqual property="isControlledProduct" compareValue="true" prepend=" and ">
             dpp.SalesState = 0 
           </isEqual>
         inner join DV_PRODUCT dp 
            on dc.productcode = dp.productcode
            <isEqual property="serachType" compareValue="0">
	            <isEqual property="fixCFGType" compareValue="5" prepend=" and ">
			 		dp.fixcfgtype = 5
	   			</isEqual>
	   			<isEqual property="fixCFGType" compareValue="0" prepend=" and ">
			 		dp.fixcfgtype<![CDATA[ <>]]>5 
	   			</isEqual>
   			</isEqual>
   			<isEqual property="serachType" compareValue="1" prepend=" and ">
   			        dp.fixcfgtype = 4
   			</isEqual>
         inner join d_producttreesaleattr ds 
            on dc.nodeid = ds.nodeid
         left join D_PRODUCTIMAGE im
            on (dc.productcode = im.productcode and im.available = 1)
            <isEqual property="serachType" compareValue="1">
                 inner join d_sbom dm on dp.moduleid = dm.productid
                 inner join d_spart dt on dm.partnumber = dt.partnumber
            </isEqual>
         where dc.nodetype = #productNodeType#
           and dc.available = 1
           and ds.issctsale = 1        	
           and (ds.salescope =0 or ds.salescope=#saleScope#)
           and (dpp.producteom is null or
               trunc(to_date(dpp.producteom,'yyyy-mm-dd  hh24:mi:ss')) >=trunc(sysdate))
           and dp.productid in (Select max(dv.productid)
                          From DV_PRODUCT dv
                         Where dv.productcode = dp.productcode
                           and (dv.scope = 0 or dv.scope = #saleScope#))
           and dp.productcode not in (select pf.productcode
                          from d_productfilter pf, d_country c
                         where pf.filtertype = 1
                               and pf.filterid = c.countryid
                               and pf.available = 1
                               <![CDATA[
                               and ( (pf.isapply = 0 and c.countrycode = #countryCode#) 
                                      or (pf.isapply = 1 and c.countrycode <> #countryCode#
                               ]]>
                               and pf.productcode not in
       (select pf1.productcode
          from d_productfilter pf1, d_country c1
         where pf1.filtertype = 1
           and pf1.filterid = c1.countryid
           and pf1.available = 1 
           and pf1.isapply = 1
           and c1.countrycode = #countryCode#)))
                               )
         <dynamic>
		
			<isNotEmpty property="clanID" prepend="and">
				instr(dc.path,'-'||#clanID#||'-')>0
   			</isNotEmpty>
   			<isEqual property="serachType" compareValue="0">
	   			<isNotEmpty property="name" prepend="and">
	   				((UPPER(dpp.chsName) like '%'||UPPER(TRIM(#name#))||'%' escape '^')
	   					OR
	   				 (UPPER(dpp.chsSimpleDesc) like '%'||UPPER(TRIM(#name#))||'%' escape '^')
	   				)
	   			</isNotEmpty>
	   			<isNotEmpty property="engName" prepend="and">
	   				((UPPER(dpp.engname) like '%'||UPPER(TRIM(#engName#))||'%' escape '^')
	   					OR
	   				 (UPPER(dpp.engSimpleDesc) like '%'||UPPER(TRIM(#engName#))||'%' escape '^')
	   				)
	   			</isNotEmpty>
   			</isEqual>
   			<isEqual property="serachType" compareValue="1">
                  <isNotEmpty property="name" prepend="and">
                      UPPER(dt.partnumber) = UPPER(TRIM(#name#))
                  </isNotEmpty>
                  <isNotEmpty property="engName" prepend="and">
                      UPPER(dt.partnumber) = UPPER(TRIM(#engName#))
                  </isNotEmpty>
            </isEqual>
		</dynamic>
		 ORDER BY ln.sortNo,dc.sortNo) A ) B
		<dynamic>	
			<isNotEmpty property="pageIndex" prepend="">	
				where num > (#pageIndex#-1)*#pageCount# and num <= #pageIndex#*#pageCount#
			</isNotEmpty>   
		</dynamic>  
      )C  ORDER BY C.lineSortNo,C.sortNo