Re: With SQL from TESTCYC to CYCLE (with full path) to TEST (with full path) (2129 Views)
Reply
Occasional Advisor
KitingJoe
Posts: 10
Registered: ‎11-11-2011
Message 1 of 17 (5,928 Views)
Accepted Solution

With SQL from TESTCYC to CYCLE (with full path) to TEST (with full path)

Hi all,

 

developed this via API but it's !!much!! too slow.

 

Need output like this:

Test Case Name | Testset Name | Testset full path  (root\folder1\folder2...) | Test Plan full path (subject\folder1\folder2...)

 

Tried to combine this

SELECT tsfolder.CF_ITEM_NAME, ts.CY_CYCLE,
  plantest.TS_NAME, tstest.TC_STATUS, plantest.TS_TEST_ID,
  tstest.TC_TESTCYCL_ID, ts.CY_CYCLE_ID
  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 plantest ON plantest.TS_TEST_ID = tstest.TC_TEST_ID
  JOIN ALL_LISTS planfolder ON planfolder.AL_ITEM_ID = plantest.TS_SUBJECT

 with that:

(SELECT * FROM
   (
   SELECT AL_DESCRIPTION, AL_ITEM_ID, AL_FATHER_ID, PATH FROM
       (
        SELECT AL_DESCRIPTION, AL_ITEM_ID, AL_FATHER_ID, SYS_CONNECT_BY_PATH(AL_DESCRIPTION, '\\') PATH, LEVEL
        FROM ALL_LISTS
        START WITH AL_ITEM_ID = 2
        CONNECT BY PRIOR AL_ITEM_ID = AL_FATHER_ID
        )
    START WITH AL_ITEM_ID = plantest.TS_SUBJECT
    CONNECT BY PRIOR AL_ITEM_ID = AL_FATHER_ID
    ) aa,
TEST tt
WHERE aa.AL_ITEM_ID = tt.TS_SUBJECT)

 But I'm a SQL honk.

 

Can anybody include the full path creation into the joins thru the relations? A complete SQL that will run in HP Excel Reports?

That would be very nice. Will spend a beer on Oktoberfest 2012.

 

Greetings from Munic

 

Ahoi, Joe

 

Please use plain text.
Honored Contributor
Trudy Claspill
Posts: 3,558
Registered: ‎09-09-2010
Message 2 of 17 (5,918 Views)

Re: With SQL from TESTCYC to CYCLE (with full path) to TEST (with full path)

QC supports something called "materialized paths".  My boss has used this to get the complete folder paths in the Test Plan and Test Lab.  I'm attaching a document where he gave a high level explanation and example.  The query can't be run in the Dashboard in an Excel report because it uses SQL syntax that isn't supported in those reports.  He runs the query in Excel.

[If this post solves or helps solve your issue, mark the thread as solved and give KUDOS to the author for their assistance.]

(Opinions expressed in my postings are mine alone, and do not reflect the opinions of my employer.No warranties express or implied for any solution/suggestion posted.)
Please use plain text.
Occasional Advisor
KitingJoe
Posts: 10
Registered: ‎11-11-2011
Message 3 of 17 (5,910 Views)

Re: With SQL from TESTCYC to CYCLE (with full path) to TEST (with full path)

Hi Trudy,

that all sounds very promising. I'll test tomorrow and give feedback.
Thanks a lot so far.

Joe
Please use plain text.
Occasional Advisor
KitingJoe
Posts: 10
Registered: ‎11-11-2011
Message 4 of 17 (5,897 Views)

Re: With SQL from TESTCYC to CYCLE (with full path) to TEST (with full path)

Hi Trudy and all,

 

unfortunately the SQL in your sample is not running out of the box. I've to convert to VBA string expression at least. So I shorten it like this:

SqlCmd.CommandText = "select TS_NAME, TC_STATUS, CY_CYCLE, RN_RUN_NAME, RN_STATUS, T1.AL_ABSOLUTE_PATH " & _
" from ALL_LISTS T1 left join ALL_LISTS T2 on T1.AL_ABSOLUTE_PATH like T2.AL_ABSOLUTE_PATH + '%' " & _
"  join TEST on T1.AL_ITEM_ID = TS_SUBJECT join TESTCYCL on TS_TEST_ID = TC_TEST_ID " & _
"  join CYCLE on TC_CYCLE_ID = CY_CYCLE_ID join RUN on  CY_CYCLE_ID = RN_CYCLE_ID " & _
"  order by T1.AL_ABSOLUTE_PATH;"

 And VBA says: Failed to run query. I'm helpless because with VBA no serious SQL debugging is possible.

 

Could it be, that I'm the first one tried to solve that problem? Is it possible to amend my first SQL above so that it delivers full pathes?

 

Thank you all!

Joe

Please use plain text.
Honored Contributor
Vladimir Skrbek
Posts: 2,059
Registered: ‎03-12-2008
Message 5 of 17 (5,889 Views)

Re: With SQL from TESTCYC to CYCLE (with full path) to TEST (with full path)

Just note to your first note. You said that develope this by OTA API is too slow but I do not thing that it is slow because you can get the full path directly from object with entity like Test or TestSet. See this thread for syntax:

http://h30499.www3.hp.com/t5/ITRC-Quality-Center-Forum/QC-Test-Plan-Path-column/m-p/4796065/highligh...

 

Here is the code:

---------------------------------------------------

Re: QC Test Plan Path column06-08-2011 12:39 AM

I am resending the setting of full path for test plan and requirement and test lab from old forums threads that I have collected.
Every time it is good to save it to new custom field for Test Plan (for other entity it is simmilar:
- create a user field and remember the name for exemple "TS_USER_01"
- in script editor on event "TestPlan_Test_New" and "TestPlan_Test_MoveTo" put this code:
Test_Fields.Field("TS_USER_01").IsReadOnly = false
if Test_Fields("TS_USER_01").Value <> Test_Fields("TS_SUBJECT").Value.Path then
Test_Fields("TS_USER_01").Value = Test_Fields("TS_SUBJECT").Value.Path
End If
Test_Fields.Field("TS_USER_01").IsReadOnly = true

for TestPlan:
- in workflow script:
Test_Fields.Field("TS_SUBJECT").Value.Path
- in Visual Basic script:
objTestCase.Field("TS_SUBJECT").Path

for Test Lab:
- in workflow script:
TestSet_Fields.Field("CY_FOLDER_ID").Value.Path
- in Visual Basic script:
objTestSet.Field("CY_FOLDER_ID").Path

for Requirement:
- in workflow script in format AAAAAB:
Req_Fields.Field("RQ_REQ_PATH").Value
- in workflow script in format of all father requirements and for visual basic:
set myreq = TDConnection.ReqFactory.Item(Req_Fields.Field("RQ_

REQ_ID").Value)
msgbox myreq.Path
--------------------------------------
If you really need the sql query then I think you can use the syntaxt that you already wrote just compat it so you will get the columns that you need.
And just for to be sure you use Oracle as backend? Because for MS SQL there is another structure.
I use this queries for getting of full path for oracle:
-------------
select * from
  (select AL_ITEM_ID, sys_connect_by_path(AL_DESCRIPTION, '\\') PTH
   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 PTH
or
select * from
(select AL_ITEM_ID, sys_connect_by_path(AL_DESCRIPTION, '\\') PTH
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
where PTH like '\\Subject\\Oracle%' order by PTH
for getting all subfolders
-----------
For mssql it is another think for example like this thread:
Please use plain text.
Occasional Advisor
KitingJoe
Posts: 10
Registered: ‎11-11-2011
Message 6 of 17 (5,884 Views)

Re: With SQL from TESTCYC to CYCLE (with full path) to TEST (with full path)

Hi Vladimir,

 

thank you for all the information. Just stored to my archieve.

Using OTA one performance issue could be the fact that I read thru TestLab and get data from related TEST for every TESTINSTANCE.

 

My problem now is how to combine this SQL requirements in one SQL script executable in Excel:

Loop thru TestLab (given in my first SQL)

Get data of TestSet  (incl. TestLab full path: missing) 

Get data of Test Instance (STAUS, RUN, etc.)

Get data of relatet TEST (TestPlan full path: missing)

 

Wonder if sombody can fiddle the puzzel together.

 

Thanks!

Joe

Please use plain text.
Honored Contributor
Vladimir Skrbek
Posts: 2,059
Registered: ‎03-12-2008
Message 7 of 17 (5,871 Views)

Re: With SQL from TESTCYC to CYCLE (with full path) to TEST (with full path)

[ Edited ]

What about this one, and also this works in excel report generator directly in QC:

SELECT
  (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-- and in_cf.CF_ITEM_NAME = 'Subject'
     ) 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
  ) "TestSet Folder",
  ts.CY_CYCLE "TestSet name",
  pt.TS_TEST_ID "TestPlan Test ID",
  (select TABLEPATH.PTH from
      (select in_al.AL_ITEM_ID, sys_connect_by_path( in_al.AL_DESCRIPTION, '\') PTH
       from ALL_LISTS in_al connect by prior in_al.AL_ITEM_ID = in_al.AL_FATHER_ID
       start with in_al.AL_FATHER_ID = 0 and in_al.AL_DESCRIPTION = 'Subject') TABLEPATH
    left join Test in_pt on (in_pt.TS_SUBJECT = TABLEPATH.AL_ITEM_ID)
    where in_pt.TS_TEST_ID = pt.TS_TEST_ID
  ) "TestPlan Path",
  pt.TS_NAME "Test Name",
  tstest.TC_STATUS "Test Instance Status"
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

 

And for the OTA API, I do not think that it will take too much.

You can get through TestInstance by filter and get to each test instance the right data. i think it will be also quick.

Please use plain text.
Occasional Advisor
KitingJoe
Posts: 10
Registered: ‎11-11-2011
Message 8 of 17 (5,864 Views)

Re: With SQL from TESTCYC to CYCLE (with full path) to TEST (with full path)

Hi Vladimir,

 

first of all I want to express my thank for considerable work you have done!

 

I pasted the code in the Excel Report Query field and got an error that you find attached.

In row 3 the parser stumbles about the >, '\')< before the closing bracket and leave >, ")< and this seems to be the reason for the message: illegal parameter in SYS_CONNECT_BY_PATH function.

 

I'll try to use same code in Excel.

 

Thanks again!

Ahoi, Joe

 

 

Please use plain text.
Occasional Advisor
KitingJoe
Posts: 10
Registered: ‎11-11-2011
Message 9 of 17 (5,860 Views)

Re: With SQL from TESTCYC to CYCLE (with full path) to TEST (with full path)

[ Edited ]

Hi Vladimir and all,

 

I fixed the problem!! Replacing the backslash by double backslash results in executable code:

SELECT
  (select TABLEPATH.PTH from
    (select in_cf.CF_ITEM_ID, sys_connect_by_path (in_cf.CF_ITEM_NAME, '\\') PTH -- OR '\'
     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
  ) "TestSet Folder",
  ts.CY_CYCLE "TestSet name",
  pt.TS_TEST_ID "TestPlan Test ID",
  (select TABLEPATH.PTH from
      (select in_al.AL_ITEM_ID, sys_connect_by_path( in_al.AL_DESCRIPTION, '\\') PTH -- OR '\'
       from ALL_LISTS in_al connect by prior in_al.AL_ITEM_ID = in_al.AL_FATHER_ID
       start with in_al.AL_FATHER_ID = 0 and in_al.AL_DESCRIPTION = 'Subject') TABLEPATH
    left join Test in_pt on (in_pt.TS_SUBJECT = TABLEPATH.AL_ITEM_ID)
    where in_pt.TS_TEST_ID = pt.TS_TEST_ID
  ) "TestPlan Path",
  pt.TS_NAME "Test Name",
  tstest.TC_STATUS "Test Instance Status"
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

 

Thanks a lot.

 

Ahoi, Joe

Please use plain text.
Honored Contributor
Vladimir Skrbek
Posts: 2,059
Registered: ‎03-12-2008
Message 10 of 17 (5,853 Views)

Re: With SQL from TESTCYC to CYCLE (with full path) to TEST (with full path)

It is strange. I can put '\' without problem.
Try to put '\\' or '/'.
Or put again '\'
Please use plain text.
Occasional Advisor
KitingJoe
Posts: 10
Registered: ‎11-11-2011
Message 11 of 17 (5,850 Views)

Re: With SQL from TESTCYC to CYCLE (with full path) to TEST (with full path)

Hi Vladimir,

 

'\\' is working and results in a single backslash, great!!

 

Again many thanks. You'll get the promised beer ;)

 

Ahoi, Joe

Please use plain text.
Occasional Advisor
KitingJoe
Posts: 10
Registered: ‎11-11-2011
Message 12 of 17 (5,833 Views)

Re: With SQL from TESTCYC to CYCLE (with full path) to TEST (with full path)

Additional request:

 

the tree structures builds up organizational entities. To reduce the amount of result records I need to filter in_cf.CF_ITEM_NAME depending on current level at a time.

 

Should be not so difficult isn't it? I've no idea :(

 

It's cold outside.

 

Ahoi, Joe

Please use plain text.
Honored Contributor
Vladimir Skrbek
Posts: 2,059
Registered: ‎03-12-2008
Message 13 of 17 (5,822 Views)

Re: With SQL from TESTCYC to CYCLE (with full path) to TEST (with full path)

I do not understand what you want to filter.

and actually I do not know how to filter it.

Please use plain text.
Occasional Advisor
QCQTP8
Posts: 11
Registered: ‎08-16-2011
Message 14 of 17 (5,820 Views)

Re: With SQL from TESTCYC to CYCLE (with full path) to TEST (with full path)

Is it possible to add query to retrieve the Linked Defect as well?

Any suggestion?

Please use plain text.
Honored Contributor
Vladimir Skrbek
Posts: 2,059
Registered: ‎03-12-2008
Message 15 of 17 (5,818 Views)

Re: With SQL from TESTCYC to CYCLE (with full path) to TEST (with full path)

Yes of course it is possible, but sorry I am just now very busy and not have time to lear the oracle sql. Find oracel administrator in your company they will give you the answer in 5 minutes.
Please use plain text.
Occasional Advisor
QCQTP8
Posts: 11
Registered: ‎08-16-2011
Message 16 of 17 (5,774 Views)

Re: With SQL from TESTCYC to CYCLE (with full path) to TEST (with full path)

Thanks Valdimir.

But i am trying following query:

SELECT
  (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
  ) "TestSet Folder",
  ts.CY_CYCLE "TestSet name",
  (select TABLEPATH.PTH from
      (select in_al.AL_ITEM_ID, sys_connect_by_path( in_al.AL_DESCRIPTION, '\') PTH
       from ALL_LISTS in_al connect by prior in_al.AL_ITEM_ID = in_al.AL_FATHER_ID
       start with in_al.AL_FATHER_ID = 0 and in_al.AL_DESCRIPTION = 'Subject') TABLEPATH
    left join Test in_pt on (in_pt.TS_SUBJECT = TABLEPATH.AL_ITEM_ID)
    where in_pt.TS_TEST_ID = pt.TS_TEST_ID
  ) "TestPlan Path",
pt.TS_NAME "Test Name",
tstest.TC_STATUS "Test Instance Status",bg.BG_BUG_ID,bg.BG_STATUS
FROM TESTCYCL tstest
LEFT JOIN CYCLE ts ON ts.CY_CYCLE_ID = tstest.TC_CYCLE_ID
LEFT JOIN CYCL_FOLD tsfolder ON tsfolder.CF_ITEM_ID = ts.CY_FOLDER_ID
LEFT JOIN TEST pt ON pt.TS_TEST_ID = tstest.TC_TEST_ID
LEFT JOIN LINK ln ON  tstest.TC_TESTCYCL_ID = ln.LN_ENTITY_ID
RIGHT JOIN BUG bg ON bg.BG_BUG_ID = ln.LN_BUG_ID

 

All Defects are getting listed but it is not returning correctly as per the test set.

I tried using the BUG table as well in the selct criteria.but nothing helped me.

I also tried other way round by

 

SELECT
  bg.BG_BUG_ID as Defect_ID,
  bg.BG_SUMMARY as Summary,
  bg.BG_STATUS as Status,
  al.al_description as TestPlanName
FROM BUG bg
Left Join all_lists al on bg.bg_subject = al.al_item_id

 

Here i have the trouble of returning Test set folder,Test set name.

 

Your help will be highly appreciated.

 

Best Regards,

-Amit

Please use plain text.
Occasional Visitor
Puneet007
Posts: 3
Registered: ‎10-09-2013
Message 17 of 17 (2,129 Views)

Re: With SQL from TESTCYC to CYCLE (with full path) to TEST (with full path)

Based on the solution does anyone know how to do the same for the requirement module.

Please use plain text.
The opinions expressed above are the personal opinions of the authors, not of HP. By using this site, you accept the Terms of Use and Rules of Participation