HP Quality Center (QC)

In case I'm ever asked to export from QC again.

/*
Built for Jira 26921
Michael Nitschke 30/03/2010

Examples:
define testPlanRoot = 'SA Master'
define testLabRoot = 'System Testing\SYS1 SA'

define testPlanRoot = 'MTP Testing'
define testLabRoot = 'MTP Testing\MTP 13'

Hint: you can optionally add a trailing back-slash on the testLabRoot variable if you
need to clearly define it as seperate from and similarly named folder,
e.g. 'SYSTEM' and 'SYSTEM1'.
*/

/* notes:
Test Lab runs do not neccessarily line up in the same folders in Test Lab as the folders that are in Test Plan ...if that makes sense.
Joined by TEST_ID but the Test Lab folder Name can be changed, so the lab results for a given test plan may be in a test lab folder that has a different name to the plan.
hard to explain.
*/

define testPlanRoot = 'SA Master'
define testLabRoot = 'System Testing\System Test Phase 1 - SA\'


select * from (

SELECT
  substr(path, 2, instr(path,'\',1,2) - 2) as lev1
, substr(path, instr(path,'\',1,2) + 1, instr(path,'\',1,3) - instr(path,'\',1,2) - 1) as lev2
, substr(path, instr(path,'\',1,3) + 1, instr(path,'\',1,4) - instr(path,'\',1,3) - 1) as lev3
, substr(path, instr(path,'\',1,4) + 1, instr(path,'\',1,5) - instr(path,'\',1,4) - 1) as lev4
, substr(path, instr(path,'\',1,5) + 1, instr(path,'\',1,6) - instr(path,'\',1,5) - 1) as lev5
, substr(path, instr(path,'\',1,6) + 1, instr(path,'\',1,7) - instr(path,'\',1,6) - 1) as lev6
, nvl(users.full_name, 'anon.') || ' (' || test.ts_responsible || ')' as sweet_fa
, test.ts_name as test_script_name
, test.ts_status
, run.rn_status

from (
 select
 al_father_id, al_item_id, sys_connect_by_path(al_description, '\') || '\' as path
 FROM (SELECT * from all_lists where al_father_id <> 2329) -- 2329 is a 'bad', invisible folder. Yes really.
 connect by prior al_item_id = al_father_id
) path
, test
, users
,
 (select run.rn_test_id, run.rn_status
 from run
 where exists
  (select 1
  from cycle
  where cycle.cy_cycle_id = run.rn_cycle_id
  and cycle.cy_folder_id in
   (select cf_item_id from
    (select cf_item_id, sys_connect_by_path(cf_item_name, '\') as path
    from cycl_fold a
    connect by prior cf_item_id = cf_father_id)
   where path like '\' || &testLabRoot || '%'))
 and run.rn_execution_date =
  (select max(r.rn_execution_date)
  from run r
  where r.rn_test_id = run.rn_test_id)
 and run.rn_execution_time =
  (select max(rn_execution_time)
  from run r
  where r.rn_test_id = run.rn_test_id
  and r.rn_execution_date = run.rn_execution_date)) run


where path.path like '\' || &testPlanRoot || '%'

and test.ts_subject (+) = path.al_item_id
and users.user_name (+) = test.ts_responsible

and run.rn_test_id (+) = test.ts_test_id

)
where lev1 = &testPlanRoot -- Removes data where same folder name exists on another branch.
and not lev2 is null -- Don't need base folder only in data (blanks).
and not lev3 is null
order by 1, 2, 3, 4, 5, 6, 8
;