SELECT LEVEL
, ss.parent_sess_no
, ss.sess_no
, CONNECT_BY_ROOT
ss.sess_name root_sess_name
, LPAD(‘> ‘,3*(LEVEL-1),’-‘) || sess_name sess_name
, ss.sess_beg
, ss.sess_end
, stl.rows_processed
, ss.sess_dur
, ss.sess_status
FROM  odi_atlas_work.snp_session ss
, (SELECT sess_no
, SUM(ABS(nb_row)) rows_processed
FROM odi_atlas_work.snp_sess_task_log
WHERE TRUNC(task_beg) >= ’01-JUL-2011′
AND TRUNC(task_end) < ’02-JUL-2011’
GROUP BY sess_no ) stl
WHERE      ss.sess_no = stl.sess_no
AND UPPER(ss.agent_name) = ‘APOLLOSERVER’
START WITH ss.parent_sess_no IS NULL
AND ss.sess_beg >= ’01-JUL-2011′
AND ss.sess_end < ’02-JUL-2011′
CONNECT BY PRIOR ss.sess_no = ss.parent_sess_no