1 2 3 4 5 6 7 8 9 10 11 12 13
| SELECT CASE WHEN ( ( i117923 BETWEEN 1 AND 7 OR i117923 BETWEEN 500 AND 502 ) OR i117923 BETWEEN 2300 AND 2305 ) THEN 'Valid SM' WHEN ( i117923 BETWEEN 8 AND 14 OR i117923 BETWEEN 503 AND 505 ) THEN 'Ancillary' WHEN ( ( i117923 BETWEEN 25 AND 29 OR i117923 BETWEEN 516 AND 518 ) OR i117923 BETWEEN 2311 AND 2314 ) THEN 'Invalid SM' WHEN ( i117923 BETWEEN 118 AND 120 OR i117923 BETWEEN 138 AND 138 ) THEN 'FDA' WHEN ( i117923 IN (115,116,117,142,143) ) THEN 'Internal Reject' END as E_26,DECODE(i117966,0,'Events',1,'Status') as E_21,i117912 as E117912,i117961 as E117961,i117997 as E117997,i118008 as E118008,i118009 as E118009,i118104 as E118104,i118160 as E118160,i118176 as E118176,SUM(i118053) as E118053_SUM
FROM ( SELECT CLASS_ID AS i117900, DESCRIPTIONS AS i117949, DISPLAY_STRING AS i117961, ERROR_FLAG AS i117966, ERROR_GROUP_ID AS i117971, OPERATION_ID AS i118019, ORIGINATOR AS i118025, TYPE_ID AS i118045 FROM SM_STATISTICS.NY_DATA_TYPES ) o117849,
( SELECT DESCRIPTION AS i117948, GROUP_ID AS i117987, GROUP_NAME AS i117997, GROUP_TYPE_ID AS i117998 FROM OMAP_CONFIG.NY_GROUPS ) o117852,
( SELECT HOST_ID AS i117999, HOST_NAME AS i118008, IP_ADDRESS AS i118017, OS_TYPE_ID AS i118030 FROM OMAP_CONFIG.NY_HOSTS ) o117853,
( SELECT CLASS_ID AS i117902, CLASS_NAME AS i117912, GROUP_ID AS i117988, IS_DEFAULT_CLASS AS i118018, PARENT_CLASS_ID AS i118031, DECODE(PARENT_CLASS_ID,14,PARENT_CLASS_ID,CLASS_ID) AS i118057 FROM OMAP_CONFIG.NY_ISMSC_CLASSES ) o117854,
( SELECT SFE_INSTANCE AS i118037, VALUE_AVG AS i118050, VALUE_SUM AS i118053, ERROR_ID AS i117979, decode(DATE_TO,null,to_date(null, 'MMDDYYYY'),to_date(to_char(trunc(DATE_TO,'YYYY'),'YYYY') || '01','YYYYMM')) AS i118176, decode(DATE_TO,null,to_date(null, 'MMDDYYYY'),to_date(to_char(trunc(DATE_TO,'DD'),'DD') || '190001','DDYYYYMM')) AS i118104, decode(DATE_TO,null,to_date(null, 'MMDDYYYY'),to_date(to_char(trunc(DATE_TO,'HH'),'HH24') || '19000101','HH24YYYYMMDD')) AS i118120, decode(DATE_TO,null,to_date(null, 'MMDDYYYY'),to_date(to_char(trunc(DATE_TO,'MI'),'HH24MI') || '19000101','HH24MIYYYYMMDD')) AS i118136, decode(DATE_TO,null,to_date(null, 'MMDDYYYY'),to_date(to_char(trunc(DATE_TO,'MI'),'MI') || '1900010100','MIYYYYMMDDHH24')) AS i118152, decode(DATE_TO,null,to_date(null, 'MMDDYYYY'),to_date(to_char(trunc(DATE_TO,'MM'),'MM') || '1900','MMYYYY')) AS i118160, DATE_TO AS i117935, CLASS_ID AS i117904, CONNECTION_TYPE AS i117915, DATA_TYPE_ID AS i117923, GROUP_ID AS i117989, HOST_ID AS i118000, INSTANCE AS i118009, ROW_COUNT AS i118034, EXTRA1 AS i125936, EXTRA2 AS i125937, EXTRA3 AS i125938, EXTRA4 AS i125939, EXTRA5 AS i125940, EXTRA6 AS i125941, EXTRA7 AS i125942, EXTRA8 AS i125943 FROM SM_STATISTICS.NY_STATISTICS_AGG_DATA ) o117856
WHERE ( (i118045 = i117923)
and (i117987 = i117989)
and (i117999 = i118000)
and (i117902 = i117904 AND i117988 = i117989))
AND ( ( ( o117856.i117923 BETWEEN 1 AND 7 OR o117856.i117923 BETWEEN 8 AND 14 OR o117856.i117923 BETWEEN 25 AND 29 OR o117856.i117923 IN (115,116,117,142,143) ) AND o117856.i117904 = 2 OR ( o117856.i117923 BETWEEN 2300 AND 2305 OR o117856.i117923 BETWEEN 2311 AND 2314 OR o117856.i117923 BETWEEN 2373 AND 2375 ) AND o117856.i117904 = 7 OR ( o117856.i117923 BETWEEN 118 AND 120 OR o117856.i117923 = 138 ) AND o117856.i117904 = 2 OR ( o117856.i117923 BETWEEN 500 AND 502 OR o117856.i117923 BETWEEN 503 AND 505 OR o117856.i117923 BETWEEN 516 AND 518 ) AND o117856.i117904 = 38 ) )
AND (o117856.i117935 >= :"From" AND o117856.i117935 <= TRUNC(:"To")+1-1/24/3600)
GROUP BY CASE WHEN ( ( i117923 BETWEEN 1 AND 7 OR i117923 BETWEEN 500 AND 502 ) OR i117923 BETWEEN 2300 AND 2305 ) THEN 'Valid SM' WHEN ( i117923 BETWEEN 8 AND 14 OR i117923 BETWEEN 503 AND 505 ) THEN 'Ancillary' WHEN ( ( i117923 BETWEEN 25 AND 29 OR i117923 BETWEEN 516 AND 518 ) OR i117923 BETWEEN 2311 AND 2314 ) THEN 'Invalid SM' WHEN ( i117923 BETWEEN 118 AND 120 OR i117923 BETWEEN 138 AND 138 ) THEN 'FDA' WHEN ( i117923 IN (115,116,117,142,143) ) THEN 'Internal Reject' END, DECODE(i117966,0,'Events',1,'Status'), i117912, i117961, i117997, i118008, i118009, i118104, i118160, i118176; |
Partager