Solved

SQL ORACLE: How to divide a static number

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

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
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 

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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Query 34 80
grouping logic 6 46
how to install/upgrade the Blitz responder kit 8 22
Update data using formula 22 19
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Via a live example, show how to take different types of Oracle backups using RMAN.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

911 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now