Maintenance is complete- We've moved from the saas.hpe.com domain to softwaregrp.com click to read more
As part of our journey to complete our separation work and our future integration with Micro Focus, we've now updated our domain for the community. This is an interim step, which will be followed by a series of future update / improvements: - Piloting Idea boards - Refreshing the entire UI. (more to come later) - and more
Quality Center / ALM Practitioners Forum
cancel

HPQC: why many test_cycle_id is extract, not only one expected where all run are

Highlighted
Aurelien_BRE
Contributor.

HPQC: why many test_cycle_id is extract, not only one expected where all run are

Hello,

 

In an excel report, join is done to have design steps in case of no running test and to have steps in case of running tests. filter is done about cycle, here OUT05.001 

Issue is:

many test_cycle_id are exported. so unexpected data is present. specially no run tests.

example

expected test_cycle_is is 3615. in attached document

We would like to know how we can make join to have correct test_cycle_id (not by fixing it) but by making correct join.

please find export:

-- extraction dans TEST LAB suivant le nom du repertoire (CYCL_FOLD.CF_ITEM_NAME) ou le nom -- de l'instance de test (cycle.CY_CYCLE)
-- a changer 2 fois avec le INNER JOIN

SELECT Dossier,Campagne,Test_id,Test_name,Criticity,Main_project,Business_domain,Designer,Approver,Status,Perimeter,Component,Version,Test_Status,Step_Order, Step_id,Step_name,Step_description,Step_expected,Step_actual,Step_status,Step_execution_date,Step_execution_time,Run_id,Run_Date, Run_Hour, valideur , test_cycle_id
from (

select * from(
SELECT
CYCL_FOLD.CF_ITEM_NAME AS Dossier ,
CYCLE.CY_CYCLE AS Campagne ,
TS_TEST_ID AS Test_id ,
CY_CYCLE_ID AS Cycle_id ,
TEST.TS_NAME AS Test_name ,
TEST.TS_USER_01 AS Criticity ,
TEST.TS_USER_06 AS Main_project ,
TEST.TS_USER_02 AS Business_domain ,
TEST.TS_RESPONSIBLE AS Designer ,
TEST.TS_USER_04 AS Approver ,
TEST.TS_STATUS AS Status ,
TEST.TS_USER_03 AS Perimeter ,
TEST.TS_USER_05 AS Component ,
TEST.TS_USER_07 AS Version ,
'No Run' AS Test_Status ,
TESTCYCL.TC_TEST_ORDER AS Step_Order ,
DESSTEPS.DS_ID AS Step_id ,
DESSTEPS.DS_STEP_NAME AS Step_name ,
DESSTEPS.DS_DESCRIPTION AS Step_description ,
DESSTEPS.DS_EXPECTED AS Step_expected ,
'' AS Step_actual ,
'No Run' AS Step_status ,
Null AS Step_execution_date ,
Null AS Step_execution_time ,
0 AS Run_id ,
Null AS Run_Date ,
Null AS Run_Hour ,
TESTCYCL.TC_TESTER_NAME AS valideur ,
TESTCYCL.TC_CYCLE_ID as test_cycle_id

 

FROM
CYCLE ,
CYCL_FOLD ,
TESTCYCL ,
TEST,
DESSTEPS


WHERE CYCL_FOLD.CF_ITEM_ID = CYCLE.CY_FOLDER_ID
-- nom de l'instance de test
and cycle.CY_CYCLE like 'OUT05.001 %'
-- nom du répertoire contenant les instances de test
--AND (CYCL_FOLD.CF_ITEM_NAME like 'V1.18 Downstream E2E Domain Downstream - Outbound%')
AND CY_CYCLE_ID = TC_CYCLE_ID
AND TESTCYCL.TC_TEST_ID = TEST.TS_TEST_ID
AND DESSTEPS.DS_TEST_ID=TEST.TS_TEST_ID

)t2

left join (SELECT RN_TEST_ID,RN_CYCLE_ID,max(RN_RUN_ID)as maxID FROM RUN GROUP BY RN_TEST_ID,RN_CYCLE_ID )t1 on (t1.RN_TEST_ID=t2.Test_id and t1.RN_CYCLE_ID=t2.Cycle_id)
where RN_TEST_ID is NULL or RN_CYCLE_ID is NULL

UNION ALL

select * from(
SELECT
CYCL_FOLD.CF_ITEM_NAME AS Dossier ,
CYCLE.CY_CYCLE AS Campagne ,
RUN.RN_TEST_ID AS Test_id ,
CYCLE.CY_CYCLE_ID AS Cycle_id ,
TEST.TS_NAME AS Test_name ,
TEST.TS_USER_01 AS Criticity ,
TEST.TS_USER_06 AS Main_project ,
TEST.TS_USER_02 AS Business_domain ,
TEST.TS_RESPONSIBLE AS Designer ,
TEST.TS_USER_04 AS Approver ,
TEST.TS_STATUS AS Status ,
TEST.TS_USER_03 AS Perimeter ,
TEST.TS_USER_05 AS Component ,
TEST.TS_USER_07 AS Version ,
RN_STATUS AS Test_Status ,
P.TC_TEST_ORDER AS Step_Order ,
STEP.ST_ID AS Step_id ,
STEP.ST_STEP_NAME AS Step_name ,
STEP.ST_DESCRIPTION AS Step_description ,
STEP.ST_EXPECTED AS Step_expected ,
STEP.ST_ACTUAL AS Step_actual ,
STEP.ST_STATUS AS Step_status ,
STEP.ST_EXECUTION_DATE AS Step_execution_date ,
STEP.ST_EXECUTION_TIME AS Step_execution_time ,
RN_RUN_ID AS Run_id ,
RN_EXECUTION_DATE AS Run_date ,
RN_EXECUTION_TIME AS Run_Hour ,
RN_TESTER_NAME AS valideur ,
P.TC_CYCLE_ID as test_cycle_id


FROM
RUN ,
CYCLE ,
CYCL_FOLD ,
TEST ,
STEP,
TESTCYCL P

WHERE
RUN.RN_CYCLE_ID = CYCLE.CY_CYCLE_ID

and TC_TESTCYCL_ID = RN_TESTCYCL_ID
AND TC_CYCLE_ID = CY_CYCLE_ID
AND TC_TEST_ID = TS_TEST_ID

and TS_TEST_ID = RN_TEST_ID
AND ST_RUN_ID = RN_RUN_ID
and RN_STATUS <> 'N/A'
AND CYCLE.CY_FOLDER_ID = CYCL_FOLD.CF_ITEM_ID
-- nom de l'instance de test
and cycle.CY_CYCLE like 'OUT05.001 %'
-- nom du répertoire contenant les instances de test
--AND ((CYCL_FOLD.CF_ITEM_NAME like 'V1.18 Downstream E2E Domain Downstream - Outbound%'))
AND RUN.RN_TEST_ID = TEST.TS_TEST_ID)t3

join (SELECT RN_TEST_ID,RN_CYCLE_ID,max(RN_RUN_ID)as maxID FROM RUN GROUP BY RN_TEST_ID,RN_CYCLE_ID )t4 on (t4.RN_TEST_ID=t3.Test_id and t4.RN_CYCLE_ID=t3.Cycle_id and t4.maxID=Run_id )
where RN_TEST_ID is not NULL and RN_CYCLE_ID is not NULL and maxID is not NULL
)tt
ORDER BY Dossier ASC, Campagne ASC , Step_Order ASC