Solved

SQL ORACLE: How to divide a static number

Posted on 2010-11-08
8
397 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 56

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 56

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
Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

 

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

Independent Software Vendors: 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

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.
Suggested Courses

623 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