Solved

SQL ORACLE: How to divide a static number

Posted on 2010-11-08
8
395 Views
Last Modified: 2012-06-27
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');

Open in new window

0
Comment
Question by:Maliki Hassani
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
  • +1
8 Comments
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 34086824
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');
0
 
LVL 51

Expert Comment

by:Huseyin KAHRAMAN
ID: 34087075
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
0
 
LVL 51

Expert Comment

by:Huseyin KAHRAMAN
ID: 34087092
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'
           );

Open in new window

0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:Maliki Hassani
ID: 34087563
Great!  Now what if I want it to come out exactly like .82%  Meaning 2 decimal places and with a percent sign?
0
 

Author Comment

by:Maliki Hassani
ID: 34087609
it is currently showing as 82
0
 
LVL 32

Expert Comment

by:awking00
ID: 34087793
Which query did you use for that result?
0
 
LVL 58

Accepted Solution

by:
cyberkiwi earned 250 total points
ID: 34087805
.82% means 0.0082 of the total, is that right?
82% = 0.82 ...
Is there some confusion?

The to_char and '9999999.99' should return it to 2 decimal places, so just add || '%', i.e.

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');

Open in new window

0
 
LVL 32

Assisted Solution

by:awking00
awking00 earned 250 total points
ID: 34087808
to_char(yourexpression/100)||'%'
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.

733 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question