ORACLE SQL: Error single group function

Experts,

I am using Oracle 10G and I am getting this messagewhen I am run this function.
 Execute:
      [RESULT] = "NOCREPORTS"."TEST_NOC_KEY_INDICATOR_REPORT"

---------------------------------------------------------------------------------------------------

I usually works but I just added values for a column named ENDING_PLAN and the query that is
(SELECT COUNT(*) FROM ARADMIN.PLATFORM_EVENT WHERE UDF_CONVERT_UNIX_DATETIME(DATE_CREATED, 'US/Eastern') >= (TRUNC(SYSDATE) - INTERVAL '16' HOUR) AND UDF_CONVERT_UNIX_DATETIME(DATE_CREATED, 'US/Eastern') < (TRUNC(SYSDATE) - INTERVAL '8' HOUR) AND NOC_MANAGED = 0)

I am not sure how I should add this into the function.  Please see below  Thanks



INSERT INTO noc_key_indicator_values (field_id, field_value, report_id, report_date, show_date, KEY_INDICATOR, ENDING_PLAN)
   SELECT   'S8_1', COUNT (npe1.ticket_id), 'SO-DYLY-01', TRUNC (SYSDATE), TO_CHAR (SYSDATE - 1, 'DAY'), '  # BHN Platform Events Triaged & Dispatched <=15 Minutes of Engagement', (SELECT COUNT(*) FROM ARADMIN.PLATFORM_EVENT WHERE UDF_CONVERT_UNIX_DATETIME(DATE_CREATED, 'US/Eastern') >= (TRUNC(SYSDATE) - INTERVAL '24' HOUR) AND UDF_CONVERT_UNIX_DATETIME(DATE_CREATED, 'US/Eastern') < (TRUNC(SYSDATE) - INTERVAL '16' HOUR) AND NOC_MANAGED = 0)
     FROM   (SELECT   *
               FROM   nocreports.tts_tkt_assignment_history
              WHERE   tah_request_id IN
                            (  SELECT   MIN (tah_request_id)
                                 FROM   nocreports.tts_tkt_assignment_history
                                WHERE       tah_assign_start_date >= (TRUNC (SYSDATE) - INTERVAL '24' HOUR)
                                        AND tah_assign_start_date < (TRUNC (SYSDATE) - INTERVAL '16' HOUR)
                                        AND tah_current_combined_group = 'National-Surveillance'
                                        AND tah_form_name = 'Platform Event'
                             GROUP BY   tah_ticket_id)) ia1,
            (SELECT   ticket_id
               FROM   aradmin.platform_event
              WHERE       udf_convert_unix_datetime (date_created, 'US/Eastern') >= (TRUNC (SYSDATE) - INTERVAL '24' HOUR)
                      AND udf_convert_unix_datetime (date_created, 'US/Eastern') < (TRUNC (SYSDATE) - INTERVAL '16' HOUR)
                      AND noc_managed = 0) npe1
    WHERE   npe1.ticket_id = ia1.tah_ticket_id AND tah_assignment_seconds <= 900;

Open in new window

Maliki HassaniAsked:
Who is Participating?
 
sdstuberConnect With a Mentor Commented:
try this...


INSERT INTO noc_key_indicator_values(field_id,
                                     field_value,
                                     report_id,
                                     report_date,
                                     show_date,
                                     key_indicator,
                                     ending_plan
                                    )
    SELECT 'S8_1',
           cnt,
           'SO-DYLY-01',
           TRUNC(SYSDATE),
           TO_CHAR(SYSDATE - 1, 'DAY'),
           '  # BHN Platform Events Triaged & Dispatched <=15 Minutes of Engagement',
           (SELECT COUNT(*)
              FROM aradmin.platform_event
             WHERE udf_convert_unix_datetime(date_created, 'US/Eastern') >=
                       (TRUNC(SYSDATE) - INTERVAL '24' HOUR)
                   AND udf_convert_unix_datetime(date_created, 'US/Eastern') <
                           (TRUNC(SYSDATE) - INTERVAL '16' HOUR)
                   AND noc_managed = 0)
      FROM (SELECT COUNT(npe1.ticket_id) cnt
              FROM (SELECT *
                      FROM nocreports.tts_tkt_assignment_history
                     WHERE tah_request_id IN
                               (SELECT   MIN(tah_request_id)
                                    FROM nocreports.tts_tkt_assignment_history
                                   WHERE tah_assign_start_date >=
                                             (TRUNC(SYSDATE) - INTERVAL '24' HOUR)
                                         AND tah_assign_start_date <
                                                 (TRUNC(SYSDATE)
                                                  - INTERVAL '16' HOUR)
                                         AND tah_current_combined_group =
                                                 'National-Surveillance'
                                         AND tah_form_name = 'Platform Event'
                                GROUP BY tah_ticket_id)) ia1,
                   (SELECT ticket_id
                      FROM aradmin.platform_event
                     WHERE udf_convert_unix_datetime(date_created,
                                                     'US/Eastern'
                                                    ) >=
                               (TRUNC(SYSDATE) - INTERVAL '24' HOUR)
                           AND udf_convert_unix_datetime(date_created,
                                                         'US/Eastern'
                                                        ) <
                                   (TRUNC(SYSDATE) - INTERVAL '16' HOUR)
                           AND noc_managed = 0) npe1
             WHERE npe1.ticket_id = ia1.tah_ticket_id
                   AND tah_assignment_seconds <= 900);
0
 
sdstuberCommented:
you have used an aggregate (COUNT)  but you haven't specified a group by clause

every non-aggregate /non-constant column must be in a group by
0
 
Maliki HassaniAuthor Commented:
Sdstuber:  Do you know of a way to rearrange this function so it can work?
0
 
sdstuberCommented:
I moved the aggregate out of the same scope as the subquery
0
 
Maliki HassaniAuthor Commented:
Great Stuff..  You are so smart!!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.