1

I am trying to get a total count of test cases in each application folder in the Test Plan module in HP ALM 11.

This is what I have so far:

SELECT
Count (TS_TEST_ID) As "App1",
Count (TS_TEST_ID) As "App2",
Count (TS_TEST_ID) As "App3",
Count (TS_TEST_ID) As "App4",
Count (TS_TEST_ID) As "App5",
Count (TS_TEST_ID) As "App6",
Count (TS_TEST_ID) As "App7",
Count (TS_TEST_ID) As "App8"

FROM DESSTEPS, TEST, ALL_LISTS
WHERE DS_TEST_ID = TS_TEST_ID
AND  AL_ITEM_ID = TS_SUBJECT
AND TS_SUBJECT = ALL_LISTS.AL_ITEM_ID
AND AL_DESCRIPTION = 'App1 Folder Name in the Database'

The query outputs a column for each application name but I can only get the total count of test cases for App1. Let's say that number is 100. It shoots out 100 across the board for each application. Is there a way to list all the application folders so each column has the total count pertaining to that application? I need to somehow list all the folder names and I can't figure it out. Is this possible? Thank you for the help.

Pockets2Deep
  • 21
  • 1
  • 3
  • I have spent a lot of hours researching this and I have never found this exact question. I will try and find the duplicate but if you have a link to that, I would love to see it. I have spent way too much time on this one thing, I finally asked the question. – Pockets2Deep Jan 09 '16 at 22:27

1 Answers1

1

So, you want the number of tests per app, and each AL_DESCRIPTION represent an app, and you want exactly 8 specific apps, right ? I think what you want is the following :

SELECT AL_DESCRIPTION, Count (TS_TEST_ID)
FROM DESSTEPS, TEST, ALL_LISTS
WHERE DS_TEST_ID = TS_TEST_ID
  AND  AL_ITEM_ID = TS_SUBJECT
  AND TS_SUBJECT = ALL_LISTS.AL_ITEM_ID
  AND AL_DESCRIPTION IN
    ('App1 Folder Name in the Database', 'App2 Folder Name in the Database', [...],'App8 Folder Name in the Database')
GROUP BY AL_DESCRIPTION

The values will appear vertically, though, but I don't think this is a problem, is it ?

Seipas
  • 123
  • 8
  • 1
    So this works well for one app, but I can't get it to work for multiple application folders. I added a new line after the 2nd AND: AND AL_DESCRIPTION = 'App1' I can't seem to figure out how to get all eight Apps on there. I can't simply do this? AND AL_DESCRIPTION = 'App1', 'App2', etc etc edit: sorry I am new and don't know how to put in the code blocks when I comment on a comment. – Pockets2Deep Jan 10 '16 at 18:30
  • I should have mentioned that I don't want every application folder name. I need the count of eight folders out of probably 20. And these eight folders are subfolders. The path looks like this: Subject/Main App/App1 Subject/Main App/App2 – Pockets2Deep Jan 10 '16 at 18:42
  • I edited my answer to have only 8 apps. Does it works now ? – Seipas Jan 10 '16 at 19:41
  • Thanks for the response. I can't get it to work, though. Do I need the AL_DESCRIPTION = piece before each app folder name? I tried that but doesn't seem to work. I also tried your way and can't get it to work. I removed the AL_DESCRIPTION = piece in that last AND stmt and I am getting the first app folder and the 7th app folder. Nothing else though. Any thoughts? – Pockets2Deep Jan 11 '16 at 16:48
  • No, I forgot to remove one. It's either : ` AND AL_DESCRIPTION IN ('App1 Folder Name in the Database', 'App2 Folder Name in the Database', [...],'App8 Folder Name in the Database')` or : ` AND (AL_DESCRIPTION = 'App1 Folder Name in the Database' OR AL_DESCRIPTION = 'App2 Folder Name in the Database' OR [...] OR AL_DESCRIPTION = 'App8 Folder Name in the Database')` – Seipas Jan 11 '16 at 17:37
  • Both of those seem to work but it's still only spitting out data for two application folders. The first and the eighth in the list. Every application folder has test cases so I should see a number for all of them. If I remove seven of the apps and just have App1 and App2, it gives me data for App1 only but the code works. I am going to keep working on it but any other ideas would be great. Thanks again for the time you have spent helping me. – Pockets2Deep Jan 11 '16 at 19:45
  • Are you sure you spelled corectly your App2 folder name ? Are you sure there are tuples in your database in which AL_DESCRIPTION = your App2 folder name (with the right spelling) ? – Seipas Jan 11 '16 at 22:49
  • I spelled all of them correct but I am not sure what you mean by "are there tuples in your database in which AL_DESCRIPTION = your App2 folder name. App1 and App8 are working fine and all the folders are set up the exact same way. I am assuming everything is setup correctly for this report. – Pockets2Deep Jan 12 '16 at 14:52
  • I meant "are the informations regarding App2 to App7 actually in the database" ? It seems weird to me that this query work for App 1 and App 8 but not the others. – Seipas Jan 12 '16 at 15:56