sql 实例3

SQLSelectYesterday ="SELECT '"+ytdTime+"' AS STATICDATE,T.Alarmid, '"+yyyy_Y+"'"
+" AS YEAR, '"+mm_Y+"' AS MONTH, '"+dd_Y+"' AS DAY,"
+" (CASE WHEN T.MONAME IS NULL AND t.alarmid='405' THEN SUBSTR(REGEXP_SUBSTR(T.ALERTKEY,'Site Name=[^,]*'),"
+" INSTR(REGEXP_SUBSTR(T.ALERTKEY, 'Site Name=[^,]*'), '=') + 1) WHEN T.MONAME IS NULL AND t.alarmid='22202'"
+" THEN SUBSTR(REGEXP_SUBSTR(T.ALERTKEY,'NodeB Name=[^,]*'),"
+" INSTR(REGEXP_SUBSTR(T.ALERTKEY, 'NodeB Name=[^,]*'), '=') + 1)"
+" ELSE T.MONAME END) AS BTSORNodeB, T.NODEALIAS AS BSCORRNC, (CASE WHEN t.alarmid='405'"
+" THEN SUBSTR(REGEXP_SUBSTR(T.ALERTKEY, 'Cell Name=[^,]*'),"
+" INSTR(REGEXP_SUBSTR(T.ALERTKEY, 'Cell Name=[^,]*'), '=') + 1) ELSE '' END) AS"
+" CELL, (SELECT TD.DEPOT_NAME FROM T_DEPOT TD WHERE TD.DEPOT_ID = TS.DEPOT_ID) AS"
+" REGION, (SELECT TSSB.SITE_STATE_NAME FROM T_SITE_STATE_BASE TSSB WHERE"
+" TSSB.SITE_STATE_ID = TS.SITE_STATE_ID) AS ONAIR, T.SERIAL, T.SERVERNAME,"
+" TO_CHAR(T.FIRSTOCCURRENCE, 'yyyy-mm-dd hh24:mi:ss') AS EVENT_START_TIME, (CASE"
+" WHEN TRUNC(T.CLEARTIME) >= TRUNC(SYSDATE) OR TRUNC(T.CLEARTIME) =TO_DATE('1970-01-01','yyyy-mm-dd')"
+" THEN '1970-01-01 8:00:00' ELSE to_char(T.CLEARTIME,'yyyy-mm-dd hh24:mi:ss')"
+" END) AS EVENT_CEASE_TIME, (CASE WHEN T.CLEARFLAG = 1 AND T.TICKETID IS NULL AND"
+" SIGN((T.CLEARTIME - T.FIRSTOCCURRENCE) * 24 * 60 - 3) = -1 THEN 'Less Than 3 mins'"
+" ELSE T.TICKETID END) AS TICKETID, (SELECT (CASE WHEN RSH.MONAME IS NULL AND RSH.ALARMID = '405' THEN"
+" SUBSTR(REGEXP_SUBSTR(RSH.ALERTKEY, 'Site Name=[^,]*'),"
+" INSTR(REGEXP_SUBSTR(RSH.ALERTKEY, 'Site Name=[^,]*'), '=') + 1)WHEN RSH.MONAME IS NULL"
+" AND RSH.ALARMID = '22202' THEN SUBSTR(REGEXP_SUBSTR(RSH.ALERTKEY, 'NodeB Name=[^,]*'),"
+" INSTR(REGEXP_SUBSTR(RSH.ALERTKEY, 'NodeB Name=[^,]*'), '=') + 1) ELSE RSH.MONAME END) FROM"
+" REPORTER_STATUS_H RSH WHERE RSH.TICKETID = T.TICKETID AND RSH.TICKETID IS NOT"
+" NULL AND RSH.TICKETSTATUS <> 14) AS SOURCESITE FROM REPORTER_STATUS_H T, T_SITE TS"
+" WHERE T.EMSTYPE = 1 AND ((T.ALARMID = '22202' AND T.NETYPE='BSC6900 UMTS') OR"
+" (T.ALARMID = '405' AND T.Netype='BSC6000')) AND T.SITENAME = TS.SITE_NAME(+) AND"
+" TO_CHAR(FIRSTOCCURRENCE, 'yyyy-mm-dd') < TO_CHAR(SYSDATE, 'yyyy-mm-dd') AND"
+" (T.CLEARFLAG = 0 OR TO_CHAR(T.CLEARTIME, 'yyyy-mm-dd') >="
+" TO_CHAR(SYSDATE - 1, 'yyyy-mm-dd')) ORDER BY T.FIRSTOCCURRENCE";