Database report query problem – ALM11

  • Questions
  • Database report query problem - ALM11
Question ID: 104982
1
0

I could use some help with a database query problem I’m having. The query I’m setting up is intended to generate an Excel report on the current status of a test cycle. Some history on this is that we setup our testing structure such that we keep the same folder structure in Test Lab and just assign a new cycle when we get a new drop. This works well for us for a number of reasons I won’t elaborate on here.

So, I run the following report and I get the data I want … basically listing all the tests status (based on execution status, not test status from the actual assigned cycle). Works like a charm:

SELECT
TESTCYCL.TC_TEST_ID as ‘Test ID’,
RELEASE_CYCLES.RCYC_NAME as ‘Cycle Name’,
CYCLE.CY_CYCLE as ‘Category’,
TEST.TS_NAME as ‘Test Name’,
TEST.TS_EXEC_STATUS as ‘Status’, /*Test.Execution Status*/
TESTCYCL.TC_USER_02 as ‘Jira #’
FROM
TESTCYCL, RELEASE_CYCLES, TEST, CYCLE, CYCL_FOLD

where
RELEASE_CYCLES.RCYC_NAME = @CycleName@ and
TESTCYCL.TC_ASSIGN_RCYC = RELEASE_CYCLES.RCYC_ID and
TESTCYCL.TC_TEST_ID = TEST.TS_TEST_ID and
TESTCYCL.TC_CYCLE_ID = CYCLE.CY_CYCLE_ID and
CYCLE.CY_FOLDER_ID = CF_ITEM_ID

order by TESTCYCL.TC_TEST_ID

The resultant output lists each test and associated execution status.

Now, I want to add a user-defined field that we capture in each test run. This user defined field is a string that the test operator enters the tested against software version. My modified query looks like the following:

SELECT
TESTCYCL.TC_TEST_ID as ‘Test ID’,
RELEASE_CYCLES.RCYC_NAME as ‘Cycle Name’,
RUN.RN_USER_02 as ‘Tested Against’,
CYCLE.CY_CYCLE as ‘Category’,
TEST.TS_NAME as ‘Test Name’,
TEST.TS_EXEC_STATUS as ‘Status’, /*Test.Execution Status*/
TESTCYCL.TC_USER_02 as ‘Jira #’
FROM
TESTCYCL, RELEASE_CYCLES, RUN, TEST, CYCLE, CYCL_FOLD

where
RELEASE_CYCLES.RCYC_NAME = @CycleName@ and
TESTCYCL.TC_ASSIGN_RCYC = RELEASE_CYCLES.RCYC_ID and
TESTCYCL.TC_TEST_ID = TEST.TS_TEST_ID and
TESTCYCL.TC_CYCLE_ID = CYCLE.CY_CYCLE_ID and
CYCLE.CY_FOLDER_ID = CF_ITEM_ID

order by TESTCYCL.TC_TEST_ID

What I wanted to do is just add the user-defined string to the output. However, instead, I get this huge list of all the test cases with each test case repeated many times.

Anyone see anything obvious that I might be doing wrong???

Thanks,
Bob

Marked as spam
Posted by (Questions: 2, Answers: 5)
Asked on April 26, 2013 3:25 pm
25 views
Answers (1)
1
Private answer

First, reformatting your query to make it more readable onscreen.

SELECT
TESTCYCL.TC_TEST_ID as 'Test ID',
RELEASE_CYCLES.RCYC_NAME as 'Cycle Name',
RUN.RN_USER_02 as 'Tested Against',
CYCLE.CY_CYCLE as 'Category',
TEST.TS_NAME as 'Test Name',
TEST.TS_EXEC_STATUS as 'Status', /*Test.Execution Status*/
TESTCYCL.TC_USER_02 as 'Jira #'
FROM
TESTCYCL,
RELEASE_CYCLES,
RUN,
TEST,
CYCLE,
CYCL_FOLD
where
RELEASE_CYCLES.RCYC_NAME = @CycleName@ and
TESTCYCL.TC_ASSIGN_RCYC = RELEASE_CYCLES.RCYC_ID and
TESTCYCL.TC_TEST_ID = TEST.TS_TEST_ID and
TESTCYCL.TC_CYCLE_ID = CYCLE.CY_CYCLE_ID and
CYCLE.CY_FOLDER_ID = CF_ITEM_ID
order by
TESTCYCL.TC_TEST_ID

You added RUN to the list of tables you are retrieving from, but you aren't filtering the data you are retrieving from that specific table. So you will get a record for every Run that links to each Test Instance you were previously retrieving. If you have multiple Runs for a Test Instance, those test instances will be listed multiple times.

Marked as spam
Posted by (Questions: 1, Answers: 101)
Answered on April 26, 2013 7:07 pm