This discussion is archived
1 Reply Latest reply: Sep 25, 2013 7:03 PM by Vadim Tropashko RSS

4 EA2 Group By advice is wrong if there is a column alias.

Jim Smith Expert
Currently Being Moderated

Given the following query


select snap_id,dbid, event,event_id, p1text, p1 file_no,sum(time_waited) time ,sum(p3) blocks
from dba_hist_active_sess_history
where event is not null
and p1text='file#'
group by snap_id,dbid, event,event_id, p1text, p1
order by snap_id, event

The select list is underlined in yellow and the suggested correction is to change the group by clause to


event, snap_id, p1text, event_id, dbid


As well as being in a bizarre order, it is missing p1.

If I remove the file_no column alias, it seems to work.


  • Correct Answers - 10 points
  • Helpful Answers - 5 points