1 2 3 4 5
| SELECT NY_DATA_TYPES.DISPLAY_STRING, NY_GROUPS.GROUP_NAME, NY_HOSTS.HOST_NAME, NY_STATISTICS_AGG_DATA.INSTANCE, DECODE(NY_STATISTICS_AGG_DATA.DATE_TO,NULL,TO_DATE(NULL,'MMDDYYYY'),TO_DATE(TO_CHAR(TRUNC(NY_STATISTICS_AGG_DATA.DATE_TO,'DD'),'DD')||'190001','DDYYYYMM')), DECODE(NY_STATISTICS_AGG_DATA.DATE_TO,NULL,TO_DATE(NULL,'MMDDYYYY'),TO_DATE(TO_CHAR(TRUNC(NY_STATISTICS_AGG_DATA.DATE_TO,'MM'),'MM')||'1900','MMYYYY')), DECODE(NY_STATISTICS_AGG_DATA.DATE_TO,NULL,TO_DATE(NULL,'MMDDYYYY'),TO_DATE(TO_CHAR(TRUNC(NY_STATISTICS_AGG_DATA.DATE_TO,'YYYY'),'YYYY')||'01','YYYYMM')), ( SUM(NY_STATISTICS_AGG_DATA.VALUE_SUM) )/( SUM(NY_STATISTICS_AGG_DATA.ROW_COUNT) )
FROM SM_STATISTICS.NY_DATA_TYPES NY_DATA_TYPES, OMAP_CONFIG.NY_GROUPS NY_GROUPS, OMAP_CONFIG.NY_HOSTS NY_HOSTS, SM_STATISTICS.NY_OPERATIONS NY_OPERATIONS, SM_STATISTICS.NY_STATISTICS_AGG_DATA NY_STATISTICS_AGG_DATA
WHERE ( ( NY_DATA_TYPES.TYPE_ID = NY_STATISTICS_AGG_DATA.DATA_TYPE_ID ) AND ( NY_GROUPS.GROUP_ID = NY_STATISTICS_AGG_DATA.GROUP_ID ) AND ( NY_HOSTS.HOST_ID = NY_STATISTICS_AGG_DATA.HOST_ID ) AND ( NY_OPERATIONS.CLASS_ID = NY_DATA_TYPES.CLASS_ID AND NY_OPERATIONS.OPERATION_ID = NY_DATA_TYPES.OPERATION_ID ) ) AND ( NY_STATISTICS_AGG_DATA.DATE_TO BETWEEN '01-JAN-2013' AND TRUNC('05-JAN-2013')+1-1/24/3600 ) AND ( NY_OPERATIONS.DESCRIPTIONS = :"Operation" ) AND ( NY_DATA_TYPES.OPERATION_ID BETWEEN 100 AND 103 )
GROUP BY NY_DATA_TYPES.DISPLAY_STRING, NY_GROUPS.GROUP_NAME, NY_HOSTS.HOST_NAME, NY_STATISTICS_AGG_DATA.INSTANCE, DECODE(NY_STATISTICS_AGG_DATA.DATE_TO,NULL,TO_DATE(NULL,'MMDDYYYY'),TO_DATE(TO_CHAR(TRUNC(NY_STATISTICS_AGG_DATA.DATE_TO,'DD'),'DD')||'190001','DDYYYYMM')), DECODE(NY_STATISTICS_AGG_DATA.DATE_TO,NULL,TO_DATE(NULL,'MMDDYYYY'),TO_DATE(TO_CHAR(TRUNC(NY_STATISTICS_AGG_DATA.DATE_TO,'MM'),'MM')||'1900','MMYYYY')), DECODE(NY_STATISTICS_AGG_DATA.DATE_TO,NULL,TO_DATE(NULL,'MMDDYYYY'),TO_DATE(TO_CHAR(TRUNC(NY_STATISTICS_AGG_DATA.DATE_TO,'YYYY'),'YYYY')||'01','YYYYMM'))
; |
Partager