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";
}