Aruna_QC Trusted Contributor.
Trusted Contributor.
3292 views

SQL Query for retriving test plan folders/subfolders /testcases

Hi I need a query on below requirment 

In test plan i have number of test folders/sub folders /test cases in subject , i can trerive all the data but here requirment is 

1) Need all the folders/subfolders/test cases data  except two solders data 

2) i need to retrive only four  sub folders data from the subject tree.

appreciate if you hepl on the above requirments and i have beginner knowledge on sql.

Thank you

 

 

Tags (1)
0 Likes
8 Replies
Damodar-Reddy Acclaimed Contributor.
Acclaimed Contributor.

Re: SQL Query for retriving test plan folders/subfolders /testcases

Why dont you use the filter's option & then export your cases / data.

you can filter by giving exactly the path of the folders you needed.

or else you can export everything and in the Excel, you can delete the data which you find irrelevant...!!

(If this helped, accept as solution )
(Posts and opinions made here are my own and do not reflect the opinions of my employer)
0 Likes
Aruna_QC Trusted Contributor.
Trusted Contributor.

Re: SQL Query for retriving test plan folders/subfolders /testcases

Hi Damus,

Thanks for your replay...i already created that report and added filter condition  but here they need direct query for retriving data from different tables. they dont want to filter every time.

Thank you...!

0 Likes
Damodar-Reddy Acclaimed Contributor.
Acclaimed Contributor.

Re: SQL Query for retriving test plan folders/subfolders /testcases

May be you can use this query & try to few filter options...!!

SELECT
 TEST.TS_TEST_ID as "Test ID", /*Test.Test ID*/
 TEST.TS_NAME as "Test Name", /*Test.Test Name*/
 TEST.TS_DESCRIPTION as "Test Desc.", /*Test.Description*/
 DESSTEPS.DS_STEP_NAME as "Step Name", /*Design Step.Step Name*/
 DESSTEPS.DS_DESCRIPTION as "Step Desc.", /*Design Step.Description*/
 DESSTEPS.DS_EXPECTED as "Expected Result", /*Design Step.Expected Result*/
 TEST.TS_STATUS as "Test Status", /*Test.Status*/
 TEST.TS_TYPE "Test Type", /*Test.Type*/
 TEST.TS_RESPONSIBLE as "Test Designer", /*Test.Designer*/
 TEST.TS_USER_03 as "Test Priority", /*Test.Priority*/
 TEST.TS_USER_04 as "Test Reviewer" /*Test.Reviewer*/

FROM
 Test,
 DESSTEPS

WHERE
 DS_TEST_ID = TS_TEST_ID
(If this helped, accept as solution )
(Posts and opinions made here are my own and do not reflect the opinions of my employer)
Damodar-Reddy Acclaimed Contributor.
Acclaimed Contributor.

Re: SQL Query for retriving test plan folders/subfolders /testcases

I have an utility, & I am unable attach the same over here, may be you can send me some private message, i can share you over there..!!

(If this helped, accept as solution )
(Posts and opinions made here are my own and do not reflect the opinions of my employer)
0 Likes
Aruna_QC Trusted Contributor.
Trusted Contributor.

Re: SQL Query for retriving test plan folders/subfolders /testcases

Hi Damus,

Thanks for your query but i know this before and you can send email to my id if possible .

 

0 Likes
Damodar-Reddy Acclaimed Contributor.
Acclaimed Contributor.

Re: SQL Query for retriving test plan folders/subfolders /testcases

What is your email id?

(If this helped, accept as solution )
(Posts and opinions made here are my own and do not reflect the opinions of my employer)
0 Likes
Highlighted
Knowledge Partner
Knowledge Partner

Re: SQL Query for retriving test plan folders/subfolders /testcases

Hi,

If it is oracle db on the backend, use something like below.

select PATH,TS_TEST_ID,TS_NAME,ds_step_name,ds_description,ds_expected
from dessteps
right outer join
(
select AL_ITEM_ID,PATH,TS_TEST_ID,TS_NAME 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 outer join TEST
on TS_SUBJECT = AL_ITEM_ID
where path like      @TEST_FOLDER_PATH@
)
on ds_test_id =ts_test_id
order by PATH asc

NOTE: TEST_FOLDER_PATH parameter value ex:   \Subject\SIP - Lilly CTMS Integration\OQ%

explore if you can do a regular expression on path for other folder paths as well.

Regards,

Srihari

Valued Contributor.. hardrock Valued Contributor..
Valued Contributor..

Re: SQL Query for retriving test plan folders/subfolders /testcases

Hi, Can you please send me utilplease

0 Likes
The opinions expressed above are the personal opinions of the authors, not of Micro Focus. By using this site, you accept the Terms of Use and Rules of Participation. Certain versions of content ("Material") accessible here may contain branding from Hewlett-Packard Company (now HP Inc.) and Hewlett Packard Enterprise Company. As of September 1, 2017, the Material is now offered by Micro Focus, a separately owned and operated company. Any reference to the HP and Hewlett Packard Enterprise/HPE marks is historical in nature, and the HP and Hewlett Packard Enterprise/HPE marks are the property of their respective owners.