ALM Query for test cases without attachments and finding absolute file path location?
Iím interested in knowing whether itís possible to write an SQL query to do the following:
Iíve been tasked with checking 500+ test cases to ensure each one has photos attached to each test case at the step level.
Is this possible? My research says yes, however I canít seem to get the query to work. Iíve been playing with the ALM Query Builder and I think the following query will do it:
WHERE [ST_ATTACHMENT] = 'Y'
I canít get it to work though because I donít know what to put in the SELECT field to replace the asterisk.
My main question is, how do I find the path to the test I want? or do I even need that? All the answers I found on google say I need to write a recursive SQL query which I have not even the slightest clue how to do and any directions on how to do it assumed the user has more familiarity with SQL than I have (0 familiarity currently, in case you were wondering)
My folder structure is folder > folder 2 > folder 3, with multiple folders at the folder 2 level but Iím only interested in one specific folder at level 2 and all its child folders to search through for photos and test steps.
Ideally the query will return back any test cases in any status that have 0 photos attached, so I can manually check those cases and see if photos are required for the test cases or not.
Any guidance or assistance with this would be so appreciated!
All right, I think I found the location for the file folder, it looks like the folder path I want to use is AAA AAC AAA, and all of its child folders in the Test Lab area, so I've changed my initial query from the above to
WHERE [ST_ATTACHMENT] = 'Y'
However I keep getting an error now because of an invalid character. Anyone have any suggestions?
Okay, so I used this ALM query to find the file path I needed:
CF_ITEM_NAME as "Test Lab Folder Name",
CF_ITEM_PATH as "Path"
order by CF_ITEM_PATH
Which I didn't realize there was another folder with the same name, so the actual absolute location I am dealing with is 'AAA AAF', as I see all the subfolders I'm interested in are part of the AAA AAF parent folder.
I'm now still trying to get the query below to work:
WHERE [ST_ATTACHMENT] = 'N'
How do I utilize the absolute file location I have in order to have the query to show me all the test steps, whether pass or fail, that do not have attachments, as well as at the test case level? Thank you!
Can we take a step back for a moment, to clarify what you're after and from where? Youíre all over the place with whether youíre after steps from a test case or from a test run.
Test Cases are stored in the Test Plan module; to get the correct parent folder for test cases you'll need to use:
Test Instances (and test runs) are stored in Test Lab; youíve already run a version of this to get the Test Lab folder:
FROM ALL_LISTS /*Test Plan Folder*/
where AL_DESCRIPTION like 'Folder2'
If you have your attachments stored in Test Cases in Test Plan, and you want to run a query to see if all Test Sets in Test Lab have test cases with attachments at each step, try the following:
WHERE CF_ITEM_NAME Like 'Folder2'
The ĎTest Casesí that you see in Test Lab donít actually exist there, you need your SQL to join from your Test Sets in Test Lab through to the relevant tests in Test Plan.
Given your resulting folder name from Test Lab of AAAAAF, try something like this as a basic query to return all test steps in your test cases that are linked to test sets:
They are all left joins so will return all empty folders as well.
CYCL_FOLD.CF_ITEM_PATH AS "Folder ID",
CYCL_FOLD.CF_ITEM_NAME AS "Test Lab Folder Name",
CYCLE.CY_CYCLE_ID AS "Test Set ID",
CYCLE.CY_CYCLE AS "Test Set",
TEST.TS_NAME AS "Test Instance Name",
TESTCYCL.TC_TEST_ORDER AS "Test Instance Order",
DESSTEPS.DS_ID AS "Step ID",
DESSTEPS.DS_STEP_NAME AS "Step Name",
DESSTEPS.DS_STEP_ORDER AS "Step Order",
DESSTEPS.DS_DESCRIPTION AS "Step Description",
DESSTEPS.DS_EXPECTED AS "Step Expected Result",
DESSTEPS.DS_ATTACHMENT AS "Attachment (Y/N)"
CYCL_FOLD LEFT OUTER JOIN CYCLE ON CYCL_FOLD.CF_ITEM_ID = CYCLE.CY_FOLDER_ID
LEFT OUTER JOIN TESTCYCL ON TESTCYCL.TC_CYCLE_ID = CYCLE.CY_CYCLE_ID
LEFT JOIN TEST ON TEST.TS_TEST_ID = TESTCYCL.TC_TEST_ID
LEFT JOIN DESSTEPS ON DESSTEPS.DS_TEST_ID = TEST.TS_TEST_ID
CYCL_FOLD.CF_ITEM_PATH LIKE 'AAAAAF%' /*Folder ID. Everything under this folder level in Test Lab is selected */
However, your last comment says ďwhether pass or failĒ, which suggests that you want to check the test steps in a test run? In that case, try the following:
CYCLE.CY_CYCLE_ID as "Test Set ID",
CYCLE.CY_CYCLE as "Test Set" ,
TESTCYCL.TC_STATUS as "Execution Status",
TESTCYCL.TC_EXEC_DATE as "Date Executed",
TESTCYCL.TC_TEST_ORDER as "Test Case Order",
RUN.RN_RUN_ID AS "RUN ID",
STEP.ST_STEP_NAME AS "Step Name",
STEP.ST_STEP_ORDER AS "Step Order",
STEP.ST_STATUS AS "Step Status",
STEP.ST_ATTACHMENT AS "Attachment (Y/N)"
inner join TESTCYCL on (CYCLE.CY_CYCLE_ID = TESTCYCL.TC_CYCLE_ID)
INNER JOIN RUN ON (CYCLE.CY_CYCLE_ID = RUN.RN_CYCLE_ID)
INNER JOIN STEP ON (RUN.RN_RUN_ID = STEP.ST_RUN_ID)
Inner Join CYCL_FOLD On (CYCL_FOLD.CF_ITEM_PATH LIKE 'AAAAAF%' AND CYCLE.CY_FOLDER_ID=CYCL_FOLD.CF_ITEM_ID)
ORDER BY 5, 8
Hey, thanks for the reply! Sorry for the absence of clarification, I was pretty frazzled by this by the time I decided to seek outside help, lol. I thought of nuking this thread and reposting it, but talking it out did help solve some of the issue. I'm about to look over the code you provided and see if and how it works for me, and I'll let you know.
To clarify, what I actually am seeking is a report which will check for attachments at both the test run step level and the test case level, and tell me which tests have 0 attachments. I realized about halfway through the day yesterday that in my original query, it will return every step in a test run that doesn't have an attachment, but that might not work because in some test cases not every step will need a photo evidence attachment. Also, some of the photos were attached at the test case level, but honestly, that's such a small number that if it causes complications, I can do without that check.
I think now my primary confusion is how to get the query to check all test steps in a case and return only cases where no test steps in the case have an attachment, but not return any cases where even 1 test step has an attachment.
I'm going to review the posted code and see if answers any of those questions, I just wanted to say thanks for taking the time to reply. Some of your other forum posts I've read over the past few days have already helped on this issue!
No problem, I know what that can be like when you're so close to a result yet it seems so far away!
The SQL above won't give you exactly what you want, it returns all steps rather than just the ones with no attachment. I'm not a coder by any means (and there will no doubt be an SQL expert along sometime to point out the inefficiencies in the code!) but it should be a start to get a basic Excel report out and see if it's what you want.
You should also note that none of the code takes multiple runs into account; if you need to check for the latest run then you'll need to add something like
to the Where clause.
where run.rn_run_id = (select max(rn_run_id)from run
where rn_testcycl_id = tc.TC_TESTCYCL_ID and rn_draft='N')
Does your solution have to be a SQL query, or are you open to writing code to utilize the product's APIs?
(Opinions and information contained in this post are wholly my own and do not reflect the opinions of my employer.)