View Test’s with Attachments – Excel Report

My objective was to find a way to see which Test’s have Attachments (a quick high level check to see that evidence has been attached).
I found some code on this forum (Thank You) that identified Test’s that contained Attachments but did not include all places an Attachment could be added. I have tried to add other fields to the code (below) to ensure I capture all attachments that may be associated to a Test and believe that there are four possible locations;
Test Set, Test, Run or Step

My questions are;
1) Are these the only locations a user can attached evidence when running a Test?
2) Is this the best structure for my code? – I am not an expert at SQL therefore wondered if this could be completed more efficiently – it does run – but takes a minute or two.
3) Could this be completed a better way?

I have restricted this to specific Release Cycles as that is easier for me to report on.

 

SELECT
RC.RCYC_PARENT_ID AS "RELEASE",
RE.REL_NAME AS "RELEASE_NAME",
CF.CF_ASSIGN_RCYC AS "REL_CYC",
RC.RCYC_NAME AS "REL_CYC_NAME",
CYCLE.CY_CYCLE AS "TEST SET",
T.TS_NAME AS "TEST NAME",
A.AU_ENTITY_TYPE AS "RUN_STEP_TESTCYCLE_CYCLE",
CYCLE.CY_CYCLE_ID AS "CYCLE",
TESTCYCL.TC_TESTCYCL_ID AS "TEST CYCLE",
R.RN_RUN_ID AS "RUN",
S.ST_ID AS "STEP",
A.AU_USER AS "USER_ID",
A.AU_TIME AS "DATE/TIME",
CF.CF_ITEM_NAME AS "TEST PHASE",
A.AU_DESCRIPTION AS "ATTACHMENT INFO"

FROM
AUDIT_LOG A,
TEST T,
RUN R,
CYCL_FOLD CF,
CYCLE,
TESTCYCL,
STEP S,
RELEASE_CYCLES RC,
RELEASES RE

WHERE (
(A.AU_ENTITY_TYPE = 'STEP'     AND A.AU_ENTITY_ID = S.ST_ID) OR
(A.AU_ENTITY_TYPE = 'RUN'      AND A.AU_ENTITY_ID = R.RN_RUN_ID) OR
(A.AU_ENTITY_TYPE = 'TESTCYCL' AND A.AU_ENTITY_ID = TESTCYCL.TC_TESTCYCL_ID) OR
(A.AU_ENTITY_TYPE = 'CYCLE'    AND A.AU_ENTITY_ID = CYCLE.CY_CYCLE_ID))

AND R.RN_RUN_ID = S.ST_RUN_ID
AND R.RN_TEST_ID = T.TS_TEST_ID
AND R.RN_TESTCYCL_ID = TESTCYCL.TC_TESTCYCL_ID
AND R.RN_CYCLE_ID = CYCLE.CY_CYCLE_ID
AND CYCLE.CY_FOLDER_ID = CF.CF_ITEM_ID
AND CF.CF_ASSIGN_RCYC = RC.RCYC_ID
AND RC.RCYC_PARENT_ID = RE.REL_ID
AND (RC.RCYC_PARENT_ID = '201' OR RC.RCYC_PARENT_ID = '202' OR RC.RCYC_PARENT_ID = '203')

order by  A.AU_TIME

 

 

Thanks

 

Tags: