sql 实例2

SQLTmpData="SELECT VV.ALM_TYPE_NAME ALERTGROUP,VV.EXECUTEDATE,VV.DYNAMICWEEK,VV.WEEKNUM,(VV.TOTALALARM+VV.NEWALARM) AS TOTALALARM,VV.NEWALARM,VV.ALARMCLEAR,DECODE((VV.TOTALALARM+VV.NEWALARM),0,'0',VV.ALARMCLEAR /(VV.TOTALALARM+VV.NEWALARM))*100 AS ALARMPROPORTION"
       +" FROM"
       +" (SELECT R.ALM_TYPE_NAME,TO_CHAR(SYSDATE, 'yyyy-mm-dd') AS EXECUTEDATE,"
       +" to_char(SYSDATE,'day') AS DYNAMICWEEK,"
       +" to_number(DECODE(TO_CHAR(SYSDATE, 'd') - 1,0,7,TO_CHAR(SYSDATE, 'd') - 1)) AS WEEKNUM,"    
     +" (SELECT COUNT(*) FROM REPORTER_STATUS R1 WHERE R1.CLEARFLAG = 0 AND R1.DELETEFLAG = 0 AND R1.MANAGER IN ("+trim(emsNameGroup)+") AND R1.ALERTGROUP = "     
     +" R.ALM_TYPE_NAME AND (TO_CHAR(R1.FIRSTOCCURRENCE, 'yyyy-mm-dd hh24:mi:ss') BETWEEN"
       +" TO_CHAR(SYSDATE - 2, 'yyyy-mm-dd') || ' 08:00:00' AND  TO_CHAR(SYSDATE-1, 'yyyy-mm-dd') || ' 08:00:00')) AS TOTALALARM,"      
     +" (SELECT COUNT(*) FROM "+crtTable+" R2"       
     +" WHERE (TO_CHAR(R2.FIRSTOCCURRENCE, 'yyyy-mm-dd hh24:mi:ss') BETWEEN"        
     +" TO_CHAR(SYSDATE - 1, 'yyyy-mm-dd') || ' 08:00:00' AND"
       +" TO_CHAR(SYSDATE, 'yyyy-mm-dd') || ' 08:00:00')"
     +" AND R2.ALERTGROUP = R.ALM_TYPE_NAME AND R2.MANAGER IN ("+trim(emsNameGroup)+")) AS NEWALARM,"      
     +" (SELECT COUNT(*) FROM "+crtTable+" R3"
     +" WHERE TO_CHAR(R3.FIRSTOCCURRENCE, 'yyyy-mm-dd hh24:mi:ss') BETWEEN"
     +" TO_CHAR(SYSDATE - 1, 'yyyy-mm-dd') || ' 08:00:00' AND"
     +" TO_CHAR(SYSDATE, 'yyyy-mm-dd') || ' 08:00:00'"
     +" AND R3.CLEARFLAG = 1 AND R3.ALERTGROUP = "
     +" R.ALM_TYPE_NAME AND R3.MANAGER IN ("+trim(emsNameGroup)+")) AS ALARMCLEAR"
     +" FROM T_ALM_TYPE_BASE R"
     +" GROUP BY R.ALM_TYPE_NAME) VV";
       }