NIS_Longfellow
asked on
Trouble with Connect By statement
Here is the situation:
I need to count the number of steps written for all the Tests in a Testset. I can count all the steps in the tests that are Directly referenced by the Testset. The problem arises when the test calls another test.
I was given this from someone else:
define id_value=1;
select sum(ts_steps) "Total Steps"
from
(
select ts_test_id,ts_steps
from test where ts_test_id = &id_value or ts_test_id in
(
select ds_link_test from dessteps
start with ds_test_id = &id_value
connect by ds_test_id = prior ds_link_test
)
)
If I pass the root Ts_test_id as id_value it does give me the total number of steps in the test and all its child tests. However, everytime I have tried to roll this up to a testset I get a (large) multiple of the actual count.
Here is one of my stabs at it:
Select
testcycl.tc_cycle_id as cy_id,
testcycl.tc_test_id as test_id,
x.total as total
from
testcycl ,
(select sum(ts_steps) as Total
from test join testcycl on testcycl.tc_test_id = test.ts_test_id where ts_test_id = testcycl.tc_test_id or ts_test_id in
(
select ds_link_test from dessteps
start with ds_test_id = testcycl.tc_test_id
connect by ds_test_id = prior ds_link_test
))x
where testcycl.TC_CYCLE_ID = 10197
group by testcycl.TC_CYCLE_ID,testc
Here are the table defs (the important bits):
TESTCYCL - Table contains the test/testset relationship
tc_cycle_id - links to cy_cycle_id in the cycle table- think of cycle as testset
tc_test_id - the ts_test_id of the test in the cycle----the two of these together make the primary key
Test - table that contains the test records
ts_test_id - Primary Key
ts_name - test name
ts_steps - number of steps in the test
Dessteps - table that contains the individual steps of the test
ds_test_id - the test id that the step belongs to
ds_step_id - the unique Id of the step WITHIN the test ds_test_id and ds_step_id together make the primary key
ds_description - description of the step
ds_link_test - If the step is a link to another test this will contain the ts_test_id of that test, else null
Thanks in advance!
Shane
Try this
select testcycl.tc_cycle_id as cy_id, sum(ts_steps) as Total
from test ,testcycl
where (test.ts_test_id = testcycl.tc_test_id or
testcycl.tc_test_id in
(
select ds_link_test from dessteps
start with ds_test_id = testcycl.tc_test_id
connect by ds_test_id = prior ds_link_test
)
)and testcycl.TC_CYCLE_ID = 10197
group by testcycl.tc_cycle_id
select testcycl.tc_cycle_id as cy_id, sum(ts_steps) as Total
from test ,testcycl
where (test.ts_test_id = testcycl.tc_test_id or
testcycl.tc_test_id in
(
select ds_link_test from dessteps
start with ds_test_id = testcycl.tc_test_id
connect by ds_test_id = prior ds_link_test
)
)and testcycl.TC_CYCLE_ID = 10197
group by testcycl.tc_cycle_id
ASKER
Same result as the last call I posted. It seems like the testcycl join is filtering out the tests that aren't in the testset...even though it doesn't seem like that should be happening.
AAAARUGH!!!! Sorry about that...I've been staring at this all day. :)
AAAARUGH!!!! Sorry about that...I've been staring at this all day. :)
Can you post your table structures, some sample data and expected results if possible?
ASKER
Actually I should point out that the child test id's don't exist in the collection of test ids returned from testcycl. that is the crux of the problem
ASKER
Sure give me a minute
ASKER
Testcycl
tc_cycle_id tc_test_id
10197 1
10197 2
10197 3
10197 4
Test
ts_test_id ts_steps <- this field tells me how many steps are in the test
1 5
2 3
3 7
4 8
5 3
6 4
7 4
8 5
Dessteps
ds_test_id ds_step_id ds_descrption ds_link_id
1 Blah Blah
1 Blah Blah
1 Blah Blah
1 call to test 5
1 call to test 6
2 Blah Blah
2 Blah Blah
2 call to test 5
3 Blah Blah
3 call to test 8
3 Blah Blah
3 Blah Blah
3 Blah Blah
3 Blah Blah
3 call to test 7
4 Blah Blah
4 Blah Blah
4 Blah Blah
4 call to test 6
4 call to test 5
4 Blah Blah
4 Blah Blah
4 Blah Blah
5 Blah Blah
5 Blah Blah
6 Blah Blah
6 Blah Blah
6 call to test 7
7 Blah Blah
7 Blah Blah
7 Blah Blah
7 Blah Blah
8 Blah Blah
8 Blah Blah
8 Blah Blah
8 Blah Blah
8 Blah Blah
Is this what you need?
tc_cycle_id tc_test_id
10197 1
10197 2
10197 3
10197 4
Test
ts_test_id ts_steps <- this field tells me how many steps are in the test
1 5
2 3
3 7
4 8
5 3
6 4
7 4
8 5
Dessteps
ds_test_id ds_step_id ds_descrption ds_link_id
1 Blah Blah
1 Blah Blah
1 Blah Blah
1 call to test 5
1 call to test 6
2 Blah Blah
2 Blah Blah
2 call to test 5
3 Blah Blah
3 call to test 8
3 Blah Blah
3 Blah Blah
3 Blah Blah
3 Blah Blah
3 call to test 7
4 Blah Blah
4 Blah Blah
4 Blah Blah
4 call to test 6
4 call to test 5
4 Blah Blah
4 Blah Blah
4 Blah Blah
5 Blah Blah
5 Blah Blah
6 Blah Blah
6 Blah Blah
6 call to test 7
7 Blah Blah
7 Blah Blah
7 Blah Blah
7 Blah Blah
8 Blah Blah
8 Blah Blah
8 Blah Blah
8 Blah Blah
8 Blah Blah
Is this what you need?
Dunno if the below will even work. Try and see anyways
select testcycl.tc_cycle_id, Total_Steps from testcyl,
(select ts_test_id, sum(A) Total_Steps
(
select ts_test_id ,sum(ts_steps) A
from test A group by ts_test_id
union
select ts_test_id ,sum(ts_steps) A
from test where ts_test_id in
(
select ds_link_test from dessteps
start with ds_test_id = ts_test_id
connect by ds_test_id = prior ds_link_test
)
group by ts_test_id
)
) C where c.ts_test_id = testcycl.tc_test_id and testcyl.tc_test_id ='&VALUE' group by testcycl.tc_test_id;
select testcycl.tc_cycle_id, Total_Steps from testcyl,
(select ts_test_id, sum(A) Total_Steps
(
select ts_test_id ,sum(ts_steps) A
from test A group by ts_test_id
union
select ts_test_id ,sum(ts_steps) A
from test where ts_test_id in
(
select ds_link_test from dessteps
start with ds_test_id = ts_test_id
connect by ds_test_id = prior ds_link_test
)
group by ts_test_id
)
) C where c.ts_test_id = testcycl.tc_test_id and testcyl.tc_test_id ='&VALUE' group by testcycl.tc_test_id;
ASKER
Dang...that last table doesn't look right try this
Dessteps
ds_test_id ds_step_id ds_descrption ds_link_id
1 1 Blah Blah
1 2 Blah Blah
1 3 Blah Blah
1 4 call to test 5
1 5 call to test 6
2 1 Blah Blah
2 2 Blah Blah
2 3 call to test 5
3 1 Blah Blah
3 2 call to test 8
3 3 Blah Blah
3 4 Blah Blah
3 5 Blah Blah
3 6 Blah Blah
3 7 call to test 7
4 1 Blah Blah
4 2 Blah Blah
4 3 Blah Blah
4 4 call to test 6
4 5 call to test 5
4 6 Blah Blah
4 7 Blah Blah
4 8 Blah Blah
5 1 Blah Blah
5 2 Blah Blah
6 1 Blah Blah
6 2 Blah Blah
6 3 call to test 7
7 1 Blah Blah
7 2 Blah Blah
7 3 Blah Blah
7 4 Blah Blah
8 1 Blah Blah
8 2 Blah Blah
8 3 Blah Blah
8 4 Blah Blah
8 5 Blah Blah
What I would like to see returned is something like this:
cy_id Total_steps
10197 57
Thanks again!
Dessteps
ds_test_id ds_step_id ds_descrption ds_link_id
1 1 Blah Blah
1 2 Blah Blah
1 3 Blah Blah
1 4 call to test 5
1 5 call to test 6
2 1 Blah Blah
2 2 Blah Blah
2 3 call to test 5
3 1 Blah Blah
3 2 call to test 8
3 3 Blah Blah
3 4 Blah Blah
3 5 Blah Blah
3 6 Blah Blah
3 7 call to test 7
4 1 Blah Blah
4 2 Blah Blah
4 3 Blah Blah
4 4 call to test 6
4 5 call to test 5
4 6 Blah Blah
4 7 Blah Blah
4 8 Blah Blah
5 1 Blah Blah
5 2 Blah Blah
6 1 Blah Blah
6 2 Blah Blah
6 3 call to test 7
7 1 Blah Blah
7 2 Blah Blah
7 3 Blah Blah
7 4 Blah Blah
8 1 Blah Blah
8 2 Blah Blah
8 3 Blah Blah
8 4 Blah Blah
8 5 Blah Blah
What I would like to see returned is something like this:
cy_id Total_steps
10197 57
Thanks again!
Meanwhile try above.. Will see if I can create these table and come up with something else
Above post shd be
select testcycl.tc_cycle_id, Total_Steps from testcyl,
(select ts_test_id, sum(A) Total_Steps from
(
select ts_test_id ,sum(ts_steps) A
from test A group by ts_test_id
union
select ts_test_id ,sum(ts_steps) A
from test where ts_test_id in
(
select ds_link_test from dessteps
start with ds_test_id = ts_test_id
connect by ds_test_id = prior ds_link_test
)
group by ts_test_id
)
) C where c.ts_test_id = testcycl.tc_test_id and testcyl.tc_cycle_id ='&VALUE' group by testcycl.tc_test_id;
select testcycl.tc_cycle_id, Total_Steps from testcyl,
(select ts_test_id, sum(A) Total_Steps from
(
select ts_test_id ,sum(ts_steps) A
from test A group by ts_test_id
union
select ts_test_id ,sum(ts_steps) A
from test where ts_test_id in
(
select ds_link_test from dessteps
start with ds_test_id = ts_test_id
connect by ds_test_id = prior ds_link_test
)
group by ts_test_id
)
) C where c.ts_test_id = testcycl.tc_test_id and testcyl.tc_cycle_id ='&VALUE' group by testcycl.tc_test_id;
ASKER
I edited to look like this to get it to run:
select testcycl.tc_cycle_id, sum(Total_Steps) from testcycl,
(select ts_test_id, sum(A) as Total_Steps from
(
select ts_test_id ,sum(ts_steps) A
from test A group by ts_test_id
union
select ts_test_id ,sum(ts_steps) A
from test where ts_test_id in
(
select ds_link_test from dessteps
start with ds_test_id = ts_test_id
connect by ds_test_id = prior ds_link_test
)
group by ts_test_id
) group by ts_test_id
) C where c.ts_test_id = testcycl.tc_test_id and testcycl.tc_cycle_id =10197
group by testcycl.tc_cycle_id
Unfortunately it still just returned the step count of the root level tests
select testcycl.tc_cycle_id, sum(Total_Steps) from testcycl,
(select ts_test_id, sum(A) as Total_Steps from
(
select ts_test_id ,sum(ts_steps) A
from test A group by ts_test_id
union
select ts_test_id ,sum(ts_steps) A
from test where ts_test_id in
(
select ds_link_test from dessteps
start with ds_test_id = ts_test_id
connect by ds_test_id = prior ds_link_test
)
group by ts_test_id
) group by ts_test_id
) C where c.ts_test_id = testcycl.tc_test_id and testcycl.tc_cycle_id =10197
group by testcycl.tc_cycle_id
Unfortunately it still just returned the step count of the root level tests
select * from(select DS_TEST_ID, DS_LINK_ID from dessteps connect by DS_TEST_ID= prior DS_LINK_ID start with DS_TEST_ID in(select tc_test_id from Testcycl where tc_cycle_id=10197)) ;
Taking ts_steps from test table
select sum(ts_steps) from
(select DS_TEST_ID, DS_LINK_ID from (
select distinct DS_TEST_ID,DS_LINK_ID from dessteps where DS_LINK_ID is null union all select distinct DS_TEST_ID,DS_LINK_ID from dessteps where DS_LINK_ID
is not null)
connect by DS_TEST_ID= prior DS_LINK_ID start with DS_TEST_ID in(select tc_test_id from Testcycl where tc_cycle_id=10197)) a, test b
WHERE a.DS_TEST_ID=b.TS_TEST_ID and DS_LINK_ID is null;
select sum(ts_steps) from
(select DS_TEST_ID, DS_LINK_ID from (
select distinct DS_TEST_ID,DS_LINK_ID from dessteps where DS_LINK_ID is null union all select distinct DS_TEST_ID,DS_LINK_ID from dessteps where DS_LINK_ID
is not null)
connect by DS_TEST_ID= prior DS_LINK_ID start with DS_TEST_ID in(select tc_test_id from Testcycl where tc_cycle_id=10197)) a, test b
WHERE a.DS_TEST_ID=b.TS_TEST_ID and DS_LINK_ID is null;
select sum(ts_steps) from
(select DS_TEST_ID, DS_LINK_ID from
(
select distinct DS_TEST_ID,DS_LINK_ID from dessteps where DS_LINK_ID is null
union all
select distinct DS_TEST_ID,DS_LINK_ID from dessteps where DS_LINK_ID is not null
)
connect by DS_TEST_ID= prior DS_LINK_ID
start with DS_TEST_ID in(select tc_test_id from Testcycl where tc_cycle_id=10197)) a,
test b
WHERE a.DS_TEST_ID=b.TS_TEST_ID and DS_LINK_ID is null;
(select DS_TEST_ID, DS_LINK_ID from
(
select distinct DS_TEST_ID,DS_LINK_ID from dessteps where DS_LINK_ID is null
union all
select distinct DS_TEST_ID,DS_LINK_ID from dessteps where DS_LINK_ID is not null
)
connect by DS_TEST_ID= prior DS_LINK_ID
start with DS_TEST_ID in(select tc_test_id from Testcycl where tc_cycle_id=10197)) a,
test b
WHERE a.DS_TEST_ID=b.TS_TEST_ID and DS_LINK_ID is null;
ASKER
Cool, That returns the count, but it doesn't return the tc_cycle_id. Although I have been filtering on a single Testset (tc_cycle_id = 10197) I will need to get the counts from more than one testset at a time. I need the cycle_id to match things up.
select tc_cycle_id, sum(ts_steps) from
(select tc_cycle_id, DS_TEST_ID, DS_LINK_ID from
(
select distinct tc_cycle_id, DS_TEST_ID,DS_LINK_ID from dessteps,Testcycl where DS_LINK_ID is null and DS_TEST_ID=TC_TEST_ID
union all
select distinct tc_cycle_id, DS_TEST_ID,DS_LINK_ID from dessteps,Testcycl where DS_LINK_ID is not null and DS_TEST_ID=TC_TEST_ID
)
connect by DS_TEST_ID= prior DS_LINK_ID
start with DS_TEST_ID in(select tc_test_id from Testcycl where tc_cycle_id=10197)) a,
test b
WHERE a.DS_TEST_ID=b.TS_TEST_ID and DS_LINK_ID is null
GROUP BY tc_cycle_id;
(select tc_cycle_id, DS_TEST_ID, DS_LINK_ID from
(
select distinct tc_cycle_id, DS_TEST_ID,DS_LINK_ID from dessteps,Testcycl where DS_LINK_ID is null and DS_TEST_ID=TC_TEST_ID
union all
select distinct tc_cycle_id, DS_TEST_ID,DS_LINK_ID from dessteps,Testcycl where DS_LINK_ID is not null and DS_TEST_ID=TC_TEST_ID
)
connect by DS_TEST_ID= prior DS_LINK_ID
start with DS_TEST_ID in(select tc_test_id from Testcycl where tc_cycle_id=10197)) a,
test b
WHERE a.DS_TEST_ID=b.TS_TEST_ID and DS_LINK_ID is null
GROUP BY tc_cycle_id;
Outer join is required for Testcycl table
select tc_cycle_id, sum(ts_steps) from
(select tc_cycle_id, DS_TEST_ID, DS_LINK_ID from
(
select distinct tc_cycle_id, DS_TEST_ID,DS_LINK_ID from dessteps,Testcycl where DS_LINK_ID is null and DS_TEST_ID=TC_TEST_ID(+)
union all
select distinct tc_cycle_id, DS_TEST_ID,DS_LINK_ID from dessteps,Testcycl where DS_LINK_ID is not null and DS_TEST_ID=TC_TEST_ID(+)
)
connect by DS_TEST_ID= prior DS_LINK_ID
start with DS_TEST_ID in(select tc_test_id from Testcycl where tc_cycle_id=10197)) a,
test b
WHERE a.DS_TEST_ID=b.TS_TEST_ID and DS_LINK_ID is null
GROUP BY tc_cycle_id;
select tc_cycle_id, sum(ts_steps) from
(select tc_cycle_id, DS_TEST_ID, DS_LINK_ID from
(
select distinct tc_cycle_id, DS_TEST_ID,DS_LINK_ID from dessteps,Testcycl where DS_LINK_ID is null and DS_TEST_ID=TC_TEST_ID(+)
union all
select distinct tc_cycle_id, DS_TEST_ID,DS_LINK_ID from dessteps,Testcycl where DS_LINK_ID is not null and DS_TEST_ID=TC_TEST_ID(+)
)
connect by DS_TEST_ID= prior DS_LINK_ID
start with DS_TEST_ID in(select tc_test_id from Testcycl where tc_cycle_id=10197)) a,
test b
WHERE a.DS_TEST_ID=b.TS_TEST_ID and DS_LINK_ID is null
GROUP BY tc_cycle_id;
ASKER
What if the call returned the Cycle_Id and step count from Every testset? If it did that I could refilter later to just the testsets I wanted.
ASKER
Whoa...just saw your later response...let me try that
ASKER
Ok, I gave it a try, unfortunatly it didn't work. 2 things were wrong
1) the count of steps was once again just the count of step from the tests at the root
2) It returned other testsets that used the test that were linked.
The strangest part of that it that it did not count the steps from the linked tests, but it did use those tests to link to other testsets
1) the count of steps was once again just the count of step from the tests at the root
2) It returned other testsets that used the test that were linked.
The strangest part of that it that it did not count the steps from the linked tests, but it did use those tests to link to other testsets
SQL>
SQL> set serveroutput on
SQL> declare
2 n1 number;
3 BEGIN
4 for c1 in(select distinct tc_cycle_id from Testcycl) LOOP
5 for c2 IN(
6 select sum(ts_steps) sum_steps from
7 (select DS_TEST_ID, DS_LINK_ID from
8 (
9 select distinct DS_TEST_ID,DS_LINK_ID from dessteps where DS_LINK_ID is null
10 union all
11 select distinct DS_TEST_ID,DS_LINK_ID from dessteps where DS_LINK_ID is not null
12 )
13 connect by DS_TEST_ID= prior DS_LINK_ID
14 start with DS_TEST_ID in(select tc_test_id from Testcycl where tc_cycle_id=c1.tc_cycle_id
15 test b
16 WHERE a.DS_TEST_ID=b.TS_TEST_ID and DS_LINK_ID is null) LOOP
17 dbms_output.put_line(c1.tc
18 END LOOP;
19 END LOOP;
20 end;
21 /
10197 57
PL/SQL procedure successfully completed.
SQL> set serveroutput on
SQL> declare
2 n1 number;
3 BEGIN
4 for c1 in(select distinct tc_cycle_id from Testcycl) LOOP
5 for c2 IN(
6 select sum(ts_steps) sum_steps from
7 (select DS_TEST_ID, DS_LINK_ID from
8 (
9 select distinct DS_TEST_ID,DS_LINK_ID from dessteps where DS_LINK_ID is null
10 union all
11 select distinct DS_TEST_ID,DS_LINK_ID from dessteps where DS_LINK_ID is not null
12 )
13 connect by DS_TEST_ID= prior DS_LINK_ID
14 start with DS_TEST_ID in(select tc_test_id from Testcycl where tc_cycle_id=c1.tc_cycle_id
15 test b
16 WHERE a.DS_TEST_ID=b.TS_TEST_ID and DS_LINK_ID is null) LOOP
17 dbms_output.put_line(c1.tc
18 END LOOP;
19 END LOOP;
20 end;
21 /
10197 57
PL/SQL procedure successfully completed.
ASKER
Close...very close. The dbms output was absolutely correct. (a Big Yay! for that) I am running in Toad to test the code before running it in the reporting environment (Crystal reports). I had to remove the Set Serveroutput on statement to get it to run in toad. I also add a filter into the C1 portion, just to get it to run faster.
Anyway, The only problem I have now is that I can't use the dbms_output for reporting...I need data.
Question: Is that even possible?
Thanks so much for your help on this.
Shane
Anyway, The only problem I have now is that I can't use the dbms_output for reporting...I need data.
Question: Is that even possible?
Thanks so much for your help on this.
Shane
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
This looks like it will Work! Let me do some testing today.
Thanks!
Shane
Thanks!
Shane
ASKER
That is going to work! Thanks for all your help!
ASKER
select testcycl.tc_cycle_id as cy_id, sum(ts_steps) as Total
from test join testcycl on testcycl.tc_test_id = test.ts_test_id
where (test.ts_test_id = testcycl.tc_test_id or ts_test_id in
(
select ds_link_test from dessteps
start with ds_test_id = testcycl.tc_test_id
connect by ds_test_id = prior ds_link_test
)
)and testcycl.TC_CYCLE_ID = 10197
group by testcycl.tc_cycle_id
But this doesn't count the child tests only the tests in the root of the testset.
I just don't see how to get past this...how can I pass a test_id into that sub query without filtering out the children with the join/where clause criteria?