Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Too Many Rows?

Author  Topic 

planetoneautomation
Posting Yak Master

105 Posts

Posted - 2012-11-13 : 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
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2012-11-13 : 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
Go to Top of Page

planetoneautomation
Posting Yak Master

105 Posts

Posted - 2012-11-13 : 10:09:48
Yep, that did it:


INNER JOIN CYCL_FOLD ON CYCLE.CY_FOLDER_ID = CYCL_FOLD.CF_ITEM_ID


Thanks.

Go to Top of Page

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2012-11-13 : 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
Go to Top of Page

planetoneautomation
Posting Yak Master

105 Posts

Posted - 2012-11-13 : 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
Go to Top of Page

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2012-11-13 : 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
Go to Top of Page

planetoneautomation
Posting Yak Master

105 Posts

Posted - 2012-11-13 : 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.
Go to Top of Page
   

- Advertisement -