CODEWITHSUNDEEP
sqloracle-database
nbpeth
nbpeth

Reputation: 3148

ORA-0097 Group By Error

I've got this select statement inside of a procedure:

SELECT activity_note, maint,hist_maint, position 
FROM   (SELECT activity_note, activity_count, maint, hist_maint, position
        FROM   (SELECT (CASE WHEN rfi IN (SELECT column_value 
                                          FROM   table(v_sum_pms_rfi_list))
                             THEN set_gid || '-PM-' || activity_note else set_gid || '-' || activity_note end) AS activity_note,
                       SUM(activity_count) AS ctivity_count
                FROM   dpt_production_activities
                WHERE  set_gid IN (1428, 1666)
                  AND  rfi IN (SELECT column_value FROM table(v_sum_pms_rfi_list))
                   OR  rfi in (SELECT column_value FROM table(v_sum_rfi_list))
                  AND  activity_code = 'COUNT'
                GROUP  BY activity_note, activity_count)

I'm getting the invalid group by error, ORA-0097. My understanding is that I need to have all selected columns in the group by, with the option of aggregated columns - so I'm feeding two variables (activity_note and activity_count) with the select statement - the former with a condition and the latter by an aggregated column

how do I put these into my group by? have I stumbled upon something that isn't viable or is there an elegant solution I just can't see

I should mention that the previous version of this worked fine, where the inner select statement didn't contain a case statement and the GROUP BY was set_gid || '-' || activity_note

Upvotes: 0

Views: 357

Answers (1)

Vikas Hardia
Vikas Hardia

Reputation: 2695

try this

   SELECT activity_note, maint,hist_maint, position 
      FROM 
          (
           SELECT activity_note, SUM(activity_count) AS ctivity_count
                 FROM (
                       SELECT (CASE WHEN rfi IN 
                                   (
                                    SELECT column_value 
                                           FROM table(v_sum_pms_rfi_list)
                                    ) THEN    
                                     set_gid || '-PM-' || activity_note else set_gid || '-' || activity_note end) AS activity_note, activity_count
                       FROM dpt_production_activities)
                 WHERE set_gid in(1428, 1666)
                      AND rfi IN 
                              (SELECT column_value 
                                    FROM table(v_sum_pms_rfi_list)) 
                      OR rfi IN (SELECT column_value 
                                    FROM table(v_sum_rfi_list))
                      AND activity_code = 'COUNT'
                 GROUP BY activity_note))

Upvotes: 1

Related Questions