Hi,

the query below writes out the count of test step statuses for each root folder within a parent folder.
Can anyone change this so that the only difference is that it writes out the count of test case instance statuses (field name - tc_status) instead of the count of test step statuses (field name - st_status)?

SELECT *
FROM (
SELECT CONNECT_BY_ROOT cf_item_name AS root_folder, st_status FROM (
SELECT st_id, st_status, rn_run_id, ts_test_id, tc_testcycl_id, cy_cycle_id, cf_item_id, cf_father_id, cf_item_name
FROM (
SELECT st_id, st_status, rn_run_id, ts_test_id, tc_testcycl_id, cy_cycle_id, cy_folder_id
FROM step st, test ts, testcycl tc, cycle cy, (
SELECT rn_test_id, rn_cycle_id, MAX(rn_run_id) rn_run_id
FROM run
GROUP BY rn_test_id, rn_cycle_id
) rn
WHERE st.st_test_id = ts.ts_test_id
AND st.st_run_id = rn.rn_run_id
AND rn.rn_cycle_id = cy.cy_cycle_id
AND ts.ts_test_id = tc.tc_test_id
AND cy.cy_cycle_id = tc.tc_cycle_id
), (
SELECT cf_item_id, cf_father_id, cf_item_name
FROM cycl_fold
CONNECT BY PRIOR cf_item_id = cf_father_id
START WITH cf_item_id = 3113
)
WHERE cy_folder_id (+) = cf_item_id
)
CONNECT BY PRIOR cf_item_id = cf_father_id
START WITH cf_father_id = 3113
)
PIVOT ( COUNT ( st_status )
FOR ( st_status )
IN ( 'Failed' AS failed, 'Done' AS done, 'No Run' AS norun, 'Not Completed' AS notcompleted, 'Blocked' AS blocked, 'Passed' AS passed, 'Passed with W''around' AS passedwaround, 'N/A' AS na, 'Warning' AS warning, 'Deferred' AS deferred
)
)
ORDER BY 1


Regards,
Colm