Retrieve Test cases with Null Design steps using SQL query
I need to retrieve all test cases from Test Plan where the test cases don't have Design Steps/Expected Result. Basically, only the test case name exists in Test plan, but no steps in it! Can you also tell me on how to retrieve this for a set of folders in Test plan?
The DB is Oracle, I need to list down all the test case names(which doesn't have any steps/expected results in it) and its folder location . Am a novice to this query builder and learning things. Can you help, am not able to figure out the exact way of doing it
If you expect to be building queries against the ALM system, you should spend some time reviewing the Database Reference document that comes with the product. You need to get a really good understanding of how the data is organized and how it is linked. You also need to find some resources on SQL syntax and study those.
The simplest thing to do is get a list of tests with no design steps. Getting the full path of the folder they are in is harder, so I'm going to talk about the simple solution first.
I'm also assuming you are using a version of ALM that includes the Dashboard module.
You can use the Dashboard module for this. In Analysis View, select New Excel Report.
Click the Query Builder button. This gives you an interface for constructing a SQL query.
Test Cases are stored in the Test table, so you need to start there.
SELECT * FROM TEST
That will select all records and all columns from the TEST table.
SELECT * FROM TEST WHERE TS_STEPS=0
That will select all records where TS_STEPS=0. If you look at the Database Reference, at the information for the TEST table, you will see that there is a column TS_STEPS which holds the count of design steps in the test case.
SELECT TS_NAME, TS_SUBJECT FROM TEST WHERE TS_STEPS=0
That will select the same records, but retrieve just the two specified fields.
TS_NAME is the name of the test.
TS_PATH is a bit of a misnomer. It doesn't actually provide the human-readable path to the test. It provides an ID that points to another table where path information is stored.
There's a starting point. Give it a try and come back with more questions.
(Opinions and information contained in this post are wholly my own and do not reflect the opinions of my employer.)