Solved

SQL ORACLE: How to divide a static number

Posted on 2010-11-08
8
393 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
  • 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:HainKurt
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:HainKurt
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
Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

 

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
clob to char in oracle 3 39
sql help 8 55
Oracle dataguard 5 31
SQL Server 2012 r2 - Query results have 2 seperate results instead of 1 2 22
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…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

773 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