How to create the Folder Tree Path in DQL for Test Plan and Test Lab (ALM 12.50)

How to create the Folder Tree Path in DQL for Test Plan and Test Lab (ALM 12.50)
Question ID: 106760
0
0

When Legacy Excel Reports were enabled in ALM 11.00, I was able to create Test Plan and Test Lab reports with a column for the hierarchical folder tree path, so I could then sort the results by folder path. I Googled around and found that DQL is mostly ASNI SQL 9.2 (http://alm-help.saas.hpe.com/en/12.50/online_help/Content/Admin/cust_business_views_dql.htm) and found an article on an alternate way to write it (https://technology.amis.nl/2009/09/01/oracle-rdbms-11gr2-goodbye-connect-by-or-the-end-of-hierarchic…), but I’m at a loss for how to re-write it from SQL.

So how do I recursively create the folder path using DQL in Business Views? Below are the SQL-based queries I used to be able to run in SQL.

=======================================================
Test Cases By Folder (Test Plan)
=======================================================
SELECT
Path as "Folder Path",
TS_NAME as "Test Name",
TS_EXEC_STATUS as "Execution Status"
FROM
(SELECT
AL_ITEM_ID,
SYS_CONNECT_BY_PATH(AL_DESCRIPTION, ‘\\’) Path
FROM
ALL_LISTS
CONNECT BY PRIOR AL_ITEM_ID = AL_FATHER_ID
START WITH AL_FATHER_ID = 0 AND AL_DESCRIPTION = ‘Subject’)
LEFT JOIN TEST ON TS_SUBJECT = AL_ITEM_ID
ORDER BY Path, TS_NAME

=======================================================
Execution Report (Test Lab)
=======================================================
SELECT
TC_CYCLE_ID as "Test Set ID",
TC_TESTCYCL_ID as "Test Instance ID",
TC_TEST_ORDER as "Test Instance Order",
SUBSTR((SELECT TABLEPATH.PTH FROM
(SELECT in_cf.CF_ITEM_ID, sys_connect_by_path(in_cf.CF_ITEM_NAME,’\\’) PTH
FROM CYCL_FOLD in_cf CONNECT BY PRIOR in_cf.CF_ITEM_ID = in_cf.CF_FATHER_ID
START WITH in_cf.CF_FATHER_ID = 0) TABLEPATH
LEFT JOIN CYCLE in_cy on (in_cy.CY_FOLDER_ID = TABLEPATH.CF_ITEM_ID)
WHERE in_cy.CY_CYCLE_ID = ts.CY_CYCLE_ID), 0,
INSTR((SELECT TABLEPATH.PTH FROM
(SELECT in_cf.CF_ITEM_ID, sys_connect_by_path(in_cf.CF_ITEM_NAME,’\\’) PTH
FROM CYCL_FOLD in_cf CONNECT BY PRIOR in_cf.CF_ITEM_ID = in_cf.CF_FATHER_ID
START WITH in_cf.CF_FATHER_ID = 0) TABLEPATH
LEFT JOIN CYCLE in_cy on (in_cy.CY_FOLDER_ID = TABLEPATH.CF_ITEM_ID)
WHERE in_cy.CY_CYCLE_ID = ts.CY_CYCLE_ID), ‘\\’, -1)) as "Folder Path",
CF_ITEM_NAME as "Test Scenario",
CY_CYCLE as "Test Set",
TS_NAME as "Test Case",
TC_STATUS as "Status",
TC_TESTER_NAME as "Responsible Tester",
TC_PLAN_SCHEDULING_DATE as "Planned Exec Date",
TC_ACTUAL_TESTER as "Actual Tester",
TC_EXEC_DATE as "Actual Exec Date",
FROM TESTCYCL tstest
JOIN CYCLE ts ON ts.CY_CYCLE_ID = tstest.TC_CYCLE_ID
JOIN CYCL_FOLD tsfolder ON tsfolder.CF_ITEM_ID = ts.CY_FOLDER_ID
JOIN TEST pt ON pt.TS_TEST_ID = tstest.TC_TEST_ID
WHERE CF_ASSIGN_RCYC = 1021
ORDER BY "Folder Path", CY_CYCLE, TC_TEST_ORDER

Marked as spam
Posted by (Questions: 1, Answers: 0)
Asked on April 16, 2016 3:44 pm
63 views
EyeOnTesting

Welcome back to "EyeOnTesting" brought to you by Orasi Software, Inc.

X
Scroll to Top