asked on 11/8/2010

SQL ORACLE: How to divide a static number

Experts,

I have a query that divides into another query. I am now wanting to have the query divide into a static number "6".

I am using Oracle 10g

Here is my SQL, need to replace the divisor to "6"

INSERT INTO NOC_KEY_INDICATOR_VALUES (FIELD_ID, FIELD_VALUE, REPORT_ID, REPORT_DATE, SHOW_DATE, KEY_INDICATOR)
        VALUES(
			'S32_W',
		to_char(((SELECT COUNT(*) 
        FROM ARADMIN.NOC_SITE_ALERTS_HISTORY 
          WHERE UDF_CONVERT_UNIX_DATETIME(CREATE_DATE, 'US/Eastern') >= (TRUNC(SYSDATE) - INTERVAL '7' DAY) AND UDF_CONVERT_UNIX_DATETIME(CREATE_DATE, 'US/Eastern') < (TRUNC(SYSDATE) - INTERVAL '0' DAY) 
            AND WS_MODIFIED_BY IS NULL 
            AND UDF_CONVERT_UNIX_DATETIME(CREATE_DATE, 'US/Eastern') <= UDF_CONVERT_UNIX_DATETIME(TICKET_CREATE_DATE, 'US/Eastern') + INTERVAL '15' MINUTE) 
        / (SELECT case when COUNT(*) = 0 then null else count(*) end FROM ARADMIN.NOC_SITE_ALERTS_HISTORY 
            WHERE UDF_CONVERT_UNIX_DATETIME(TICKET_CREATE_DATE, 'US/Eastern') >= (TRUNC(SYSDATE) - INTERVAL '7' DAY) 
            AND UDF_CONVERT_UNIX_DATETIME(TICKET_CREATE_DATE, 'US/Eastern') < (TRUNC(SYSDATE) - INTERVAL '0' DAY) AND WS_MODIFIED_BY IS NULL)),'9999999.99')
          ,'SO-WKLY-00',TRUNC(SYSDATE),TO_CHAR(SYSDATE-1, 'DAY')
          , '% Site Alerts Executed Within 15 Minutes of Ticket Creation');

Last Comment

11/8/2010

11/8/2010

This?

INSERT INTO NOC_KEY_INDICATOR_VALUES (FIELD_ID, FIELD_VALUE, REPORT_ID, REPORT_DATE, SHOW_DATE, KEY_INDICATOR)
VALUES(
'S32_W',
to_char(((SELECT COUNT(*)
FROM ARADMIN.NOC_SITE_ALERTS_HISTORY
WHERE UDF_CONVERT_UNIX_DATETIME(CREATE_DATE, 'US/Eastern') >= (TRUNC(SYSDATE) - INTERVAL '7' DAY) AND UDF_CONVERT_UNIX_DATETIME(CREATE_DATE, 'US/Eastern') < (TRUNC(SYSDATE) - INTERVAL '0' DAY)
AND WS_MODIFIED_BY IS NULL
AND UDF_CONVERT_UNIX_DATETIME(CREATE_DATE, 'US/Eastern') <= UDF_CONVERT_UNIX_DATETIME(TICKET_CREATE_DATE, 'US/Eastern') + INTERVAL '15' MINUTE)
/ 6),'9999999.99')
,'SO-WKLY-00',TRUNC(SYSDATE),TO_CHAR(SYSDATE-1, 'DAY')
, '% Site Alerts Executed Within 15 Minutes of Ticket Creation');

HainKurt

11/8/2010

replace

(SELECT CASE
WHEN COUNT ( * ) = 0 THEN NULL
ELSE COUNT ( * )
END
FROM ARADMIN.NOC_SITE_ALERTS_HISTORY
WHERE UDF_CONVERT_UNIX_DATETIME (
TICKET_CREATE_DATE,
'US/Eastern'
) >= (TRUNC (SYSDATE) - INTERVAL '7' DAY)
AND UDF_CONVERT_UNIX_DATETIME (
TICKET_CREATE_DATE,
'US/Eastern'
) < (TRUNC (SYSDATE) - INTERVAL '0' DAY)
AND WS_MODIFIED_BY IS NULL)

with 6

HainKurt

11/8/2010

final qry is:

INSERT INTO NOC_KEY_INDICATOR_VALUES (
                                         FIELD_ID,
                                         FIELD_VALUE,
                                         REPORT_ID,
                                         REPORT_DATE,
                                         SHOW_DATE,
                                         KEY_INDICATOR
           )
  VALUES   (
               'S32_W',
               TO_CHAR (
                  ( (SELECT   COUNT ( * )
                       FROM   ARADMIN.NOC_SITE_ALERTS_HISTORY
                      WHERE   UDF_CONVERT_UNIX_DATETIME (CREATE_DATE,
                                                         'US/Eastern') >=
                                 (TRUNC (SYSDATE) - INTERVAL '7' DAY)
                              AND UDF_CONVERT_UNIX_DATETIME (CREATE_DATE,
                                                             'US/Eastern') <
                                    (TRUNC (SYSDATE) - INTERVAL '0' DAY)
                              AND WS_MODIFIED_BY IS NULL
                              AND UDF_CONVERT_UNIX_DATETIME (CREATE_DATE,
                                                             'US/Eastern') <=
                                    UDF_CONVERT_UNIX_DATETIME (
                                       TICKET_CREATE_DATE,
                                       'US/Eastern'
                                    )
                                    + INTERVAL '15' MINUTE)
                   / 6),
                  '9999999.99'
               ),
               'SO-WKLY-00',
               TRUNC (SYSDATE),
               TO_CHAR (SYSDATE - 1, 'DAY'),
               '% Site Alerts Executed Within 15 Minutes of Ticket Creation'
           );