Maliki Hassani
asked on
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"
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');
replace
(SELECT CASE
WHEN COUNT ( * ) = 0 THEN NULL
ELSE COUNT ( * )
END
FROM ARADMIN.NOC_SITE_ALERTS_HI STORY
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
(SELECT CASE
WHEN COUNT ( * ) = 0 THEN NULL
ELSE COUNT ( * )
END
FROM ARADMIN.NOC_SITE_ALERTS_HI
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
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'
);
ASKER
Great! Now what if I want it to come out exactly like .82% Meaning 2 decimal places and with a percent sign?
ASKER
it is currently showing as 82
Which query did you use for that result?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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_HI
WHERE UDF_CONVERT_UNIX_DATETIME(
AND WS_MODIFIED_BY IS NULL
AND UDF_CONVERT_UNIX_DATETIME(
/ 6),'9999999.99')
,'SO-WKLY-00',TRUNC(SYSDAT
, '% Site Alerts Executed Within 15 Minutes of Ticket Creation');