| Author |
 Topic  |
|
|
planetoneautomation
Yak Posting Veteran
89 Posts |
 Posted - 11/13/2012 : 09:42:13
|
The following query returns 261 rows when I expect 3 rows. In this SQL, the "Test Set" contains three "Tests". The query returns 87 rows per test (261/3 = 87).
I tried using DISTINCT as I saw it used in some examples but it gives me a syntax error. I think DISTINCT is not what I need anyway.
Here's the SQL ... any guidance? ...
SELECT
CYCLE.CY_CYCLE_ID AS "Test Set ID",
CYCLE.CY_CYCLE AS "Test Set Name",
CYCLE.CY_USER_07 AS "Test Set Folder",
TEST.TS_USER_04 AS "Test Functional Area",
TEST.TS_TEST_ID AS "Test ID",
TEST.TS_NAME AS "Test Name",
REQ.RQ_REQ_ID AS "Req ID",
REQ.RQ_REQ_NAME AS "Req Name",
REQ.RQ_USER_25 AS "Req Folder Path"
FROM REQ, REQ_COVER, TEST, TESTCYCL, CYCLE, CYCL_FOLD
WHERE
cycle.cy_cycle_id = 1261 and
REQ.RQ_REQ_ID = REQ_COVER.RC_REQ_ID
AND TEST.TS_TEST_ID = REQ_COVER.RC_ENTITY_ID
AND TESTCYCL.TC_TEST_ID = TEST.TS_TEST_ID
AND TESTCYCL.TC_CYCLE_ID = CYCLE.CY_CYCLE_ID
AND REQ.RQ_REQ_ID IS NOT NULL
ORDER BY CYCLE.CY_CYCLE_ID
|
|
|
jimf
Flowing Fount of Yak Knowledge
USA
2868 Posts |
Posted - 11/13/2012 : 09:49:19
|
You're mising a join. Fill that in and see if that clears things up
SELECT
CYCLE.CY_CYCLE_ID AS "Test Set ID",
CYCLE.CY_CYCLE AS "Test Set Name",
CYCLE.CY_USER_07 AS "Test Set Folder",
TEST.TS_USER_04 AS "Test Functional Area",
TEST.TS_TEST_ID AS "Test ID",
TEST.TS_NAME AS "Test Name",
REQ.RQ_REQ_ID AS "Req ID",
REQ.RQ_REQ_NAME AS "Req Name",
REQ.RQ_USER_25 AS "Req Folder Path"
FROM REQ
INNER JOIN REQ_COVER ON REQ.RQ_REQ_ID = REQ_COVER.RC_REQ_ID
INNER JOIN TEST ON TEST.TS_TEST_ID = REQ_COVER.RC_ENTITY_ID
INNER JOIN TESTCYCL ON TESTCYCL.TC_TEST_ID = TEST.TS_TEST_ID
INNER JOIN CYCLE ON TESTCYCL.TC_CYCLE_ID = CYCLE.CY_CYCLE_ID
INNER JOIN CYCL_FOLD ON
WHERE
cycle.cy_cycle_id = 1261
AND REQ.RQ_REQ_ID IS NOT NULL
ORDER BY CYCLE.CY_CYCLE_ID
Jim
Everyday I learn something that somebody else already knew |
 |
|
|
planetoneautomation
Yak Posting Veteran
89 Posts |
Posted - 11/13/2012 : 10:09:48
|
Yep, that did it:
INNER JOIN CYCL_FOLD ON CYCLE.CY_FOLDER_ID = CYCL_FOLD.CF_ITEM_ID
Thanks.
|
|
|
|
jimf
Flowing Fount of Yak Knowledge
USA
2868 Posts |
Posted - 11/13/2012 : 10:43:17
|
You're welcome. Using the ANSI join standard makes things like this easier to spot.
Jim
Everyday I learn something that somebody else already knew |
|
|
|
planetoneautomation
Yak Posting Veteran
89 Posts |
Posted - 11/13/2012 : 14:23:33
|
In the SQL below, a single row is returned for each "Test Set" which is correct. I'm using COUNT to count the number of "Tests" in the "Test Set", in this case three "Tests" and that's what the query WAS returning ... before I added the 2nd and 3rd JOIN statements which were needed to count "Steps". A "Test" has one or more "Steps". I'm now trying to count the number of "Steps" for a "Test".
So, for example, I know that a particular "Test Set" has three tests. One of those three tests has 3 steps, one has 2 steps and one has 1 step for a total of (3+2+1=) 6 steps.
The SQL below returns the correct number of steps (6) but now, instead of 3 tests its reporting 6 steps. The number of steps is always correct but the number of tests now always equals the number of steps.
What's wrong? I've tried every combination of INNER,OUTER,LEFT,RIGHT JOIN there is and not matter what, the count of tests always matches the count of steps.
-- Test Set Report
SELECT
-- Test Set Fields
CYCLE.CY_CYCLE AS "Test Set",
--QC Memo fields must be converted to nvarchar
convert(nvarchar(MAX),CYCLE.CY_COMMENT) AS "Description",
CYCLE.CY_USER_01 AS "Functional Area",
CYCLE.CY_OPEN_DATE AS "Open Date",
CYCLE.CY_CLOSE_DATE AS "Close Date",
CYCLE.CY_REQUEST_ID AS "ITG Request ID",
CYCLE.CY_STATUS AS "Status",
CYCLE.CY_PINNED_BASELINE AS "Baseline",
CYCLE.CY_CYCLE_ID AS "Cycle ID",
CYCLE.CY_ASSIGN_RCYC AS "Target Cycle",
CYCLE.CY_VTS AS "Modified",
-- Test Instance Fields & Step Fields
COUNT(TESTCYCL.TC_TEST_ID) AS "Number of Test Instances",
COUNT(STEP.ST_ID) AS "Number of Steps"
FROM TESTCYCL
INNER JOIN CYCLE ON CYCLE.CY_CYCLE_ID = TESTCYCL.TC_CYCLE_ID
JOIN RUN ON RN_TESTCYCL_ID = TC_TESTCYCL_ID
JOIN STEP ON ST_RUN_ID = RN_RUN_ID
GROUP BY CYCLE.CY_CYCLE, CAST (CYCLE.CY_COMMENT AS NVARCHAR(MAX)), --QC Memo fields must be converted to nvarchar
CYCLE.CY_USER_01, CYCLE.CY_OPEN_DATE, CYCLE.CY_CLOSE_DATE, CYCLE.CY_REQUEST_ID,
CYCLE.CY_STATUS, CYCLE.CY_PINNED_BASELINE, CYCLE.CY_CYCLE_ID, CYCLE.CY_ASSIGN_RCYC, CYCLE.CY_VTS
ORDER BY CYCLE.CY_CYCLE_ID
|
|
|
|
jimf
Flowing Fount of Yak Knowledge
USA
2868 Posts |
Posted - 11/13/2012 : 14:47:07
|
Try
COUNT(DISTINCT TESTCYCL.TC_TEST_ID) AS "Number of Test Instances",
Jim
Everyday I learn something that somebody else already knew |
|
|
|
planetoneautomation
Yak Posting Veteran
89 Posts |
Posted - 11/13/2012 : 14:52:11
|
| DISTINCT worked. Why does the answer always seem obvious once I know it? At some point, this stuff has to sink in. I could write procedural code (C##, whatever) all day long to get what I need using simple SELECT statements and spinning through record sets but anything more complicated than a straight SELECT and maybe a JOIN or two sends me reeling. I guess its a matter of doing it enough times. Thanks again. |
|
|
| |
Topic |
|
|
|
Too Many Rows?
