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