Extract Data from Quality Center (QC) 9.2 - SQL

Quality Center is a testing suite used at a few installations. It comes with a collection of reports, including its own report generator.

Nonetheless it is nice to be able to get the RAW data and do with it what you will.

-- Extract ALL Test Plan Data:
SELECT
Replace(A.PATH, ' \\Subject\System Tests\SA System Test\', '') AS PATH
, B.TS_NAME
, C.DS_STEP_NAME
, REPLACE(REPLACE(REPLACE(REGEXP_REPLACE(REPLACE(C.DS_DESCRIPTION, '
', Chr(13)), '<(.|\n)+?>', ''), '<', '<'), '>', '>'), '"', Chr(34)) AS DS_DESCRIPTION
, REPLACE(REPLACE(REPLACE(REGEXP_REPLACE(REPLACE(C.DS_EXPECTED, '
', Chr(13)), '<(.|\n)+?>', ''), '<', '<'), '>', '>'), '"', Chr(34)) AS DS_EXPECTED
FROM
(SELECT AL_ITEM_ID, LPAD(' ', 2 * LEVEL-1) || SYS_CONNECT_BY_PATH(AL_DESCRIPTION, '\') AS PATH
FROM ALL_LISTS
CONNECT BY PRIOR AL_ITEM_ID = AL_FATHER_ID) A
, TEST B
, DESSTEPS C
WHERE A.PATH LIKE '%\\Subject\System Tests\SA System Test\GRADUATIONS%'
AND B.TS_SUBJECT = A.AL_ITEM_ID
AND C.DS_TEST_ID = B.TS_TEST_ID
ORDER BY A.PATH, B.TS_NAME, C.DS_STEP_ORDER
;

Note I've done some stripping of HTML tags using a combination of Replace() and a regular expression.

We dumped a larger set of the above results into a spreadsheet, connected to it through Access and wrote out all our UAT scripts as a report.

You can also join to the Test Lab to get the Test Results out. Note that it is joined by TEST_ID so the Lab folders may not line up with the Plan folders, you can change folder names etc.

/*
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.
*/

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

, test.ts_user_06 as Business_Owner
, test.ts_user_07 as Technical_Owner
, test.ts_user_04 as HR_Func_Area
, test.ts_user_05 as Tester
, test.ts_user_09 as Date_Time
, test.ts_user_08 as Location
, test.ts_user_02 as CBS_Test_Effort
, test.ts_user_01 as Summary
, test.ts_user_03 as CR_ID

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
;