| || |
Full path from Test lab in Excel Report MSSQL
I'm trying to create a report where I extract the full path from the Test Lab.
I've been able to extract the full path from the Test Plan by using this SQL;
ISNULL(AL6.AL_DESCRIPTION+Char(92),'') + ISNULL(AL5.AL_DESCRIPTION+Char(92),'') + ISNULL(AL4.AL_DESCRIPTION+Char(92),'') + ISNULL(AL3.AL_DESCRIPTION+Char(92),'') + ISNULL(AL2.AL_DESCRIPTION+Char(92),'') + AL.AL_DESCRIPTION AS 'Subject', TS_NAME AS 'Test Name'
LEFT JOIN ALL_LISTS AL ON AL_ITEM_ID = TS_SUBJECT
LEFT JOIN ALL_LISTS AL2 ON AL2.AL_ITEM_ID = AL.AL_FATHER_ID
LEFT JOIN ALL_LISTS AL3 ON AL3.AL_ITEM_ID = AL2.AL_FATHER_ID
LEFT JOIN ALL_LISTS AL4 ON AL4.AL_ITEM_ID = AL3.AL_FATHER_ID
LEFT JOIN ALL_LISTS AL5 ON AL5.AL_ITEM_ID = AL4.AL_FATHER_ID
LEFT JOIN ALL_LISTS AL6 ON AL6.AL_ITEM_ID = AL5.AL_FATHER_ID
But I cannot seem to get it right for the Test Lab - anyone??
Here is a very similar SQL that works with the Test Lab:
SELECT ISNULL(CF6.CF_ITEM_NAME+Char(92),'') + ISNULL(CF5.CF_ITEM_NAME+Char(92),'') + ISNULL(CF4.CF_ITEM_NAME+Char(92),'') + ISNULL(CF3.CF_ITEM_NAME+Char(92),'') + ISNULL(CF2.CF_ITEM_NAME+Char(92),'') + CF.CF_ITEM_NAME AS 'Test Set Folder', CY_CYCLE AS 'Test Set Name'
LEFT JOIN CYCL_FOLD CF ON CF.CF_ITEM_ID = CY_FOLDER_ID
LEFT JOIN CYCL_FOLD CF2 ON CF2.CF_ITEM_ID = CF.CF_FATHER_ID
LEFT JOIN CYCL_FOLD CF3 ON CF3.CF_ITEM_ID = CF2.CF_FATHER_ID
LEFT JOIN CYCL_FOLD CF4 ON CF4.CF_ITEM_ID = CF3.CF_FATHER_ID
LEFT JOIN CYCL_FOLD CF5 ON CF5.CF_ITEM_ID = CF4.CF_FATHER_ID
LEFT JOIN CYCL_FOLD CF6 ON CF6.CF_ITEM_ID = CF5.CF_FATHER_ID
The query below produces the same results using hierarchical SQL which is faster. Also it doesn't limit folder structure to six levels. Notice that since this query does not start with the SELECT keyword it can't be used to generate excel reports with the default QC settings.
with cycle_folders(cf_item_id, cf_item_name) as (
select cf_item_id, cast(cf_item_name as varchar(1000))
select cf.cf_item_id, cast(c.cf_item_name + '\' + cf.cf_item_name as varchar(1000))
from cycl_fold cf
join cycle_folders c on cf.cf_father_id=c.cf_item_id
select cf_item_name as 'Test Set Path', cy_cycle as 'Test Set Name'
from cycle c left join cycle_folders cf on c.cy_folder_id=cf.cf_item_id
Tags for this Thread