Solved

Trouble with Connect By statement

Posted on 2006-07-13
26
544 Views
Last Modified: 2012-05-05


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,testcycl.tc_test_id,x.total


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


0
Comment
Question by:NIS_Longfellow
  • 14
  • 7
  • 5
26 Comments
 

Author Comment

by:NIS_Longfellow
ID: 17102956
I am a little closer with this:

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?

0
 
LVL 14

Expert Comment

by:sathyagiri
ID: 17104791
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
0
 

Author Comment

by:NIS_Longfellow
ID: 17104907
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. :)

0
 
LVL 14

Expert Comment

by:sathyagiri
ID: 17104922
Can you post your table structures, some sample data and expected results if possible?
0
 

Author Comment

by:NIS_Longfellow
ID: 17104940
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
0
 

Author Comment

by:NIS_Longfellow
ID: 17104948
Sure give me a minute
0
 

Author Comment

by:NIS_Longfellow
ID: 17105013
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?
0
 
LVL 14

Expert Comment

by:sathyagiri
ID: 17105038
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;
0
 

Author Comment

by:NIS_Longfellow
ID: 17105039
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!
0
 
LVL 14

Expert Comment

by:sathyagiri
ID: 17105047
Meanwhile try above.. Will see if I can create these table and come up with something else
0
 
LVL 14

Expert Comment

by:sathyagiri
ID: 17105069
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;
0
 

Author Comment

by:NIS_Longfellow
ID: 17105171
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
0
 
LVL 16

Expert Comment

by:MohanKNair
ID: 17106114
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)) ;

0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 16

Expert Comment

by:MohanKNair
ID: 17106560
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;

0
 
LVL 16

Expert Comment

by:MohanKNair
ID: 17106565
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;

0
 

Author Comment

by:NIS_Longfellow
ID: 17107456
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.
0
 
LVL 16

Expert Comment

by:MohanKNair
ID: 17107506
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;

0
 
LVL 16

Expert Comment

by:MohanKNair
ID: 17107528
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;

0
 

Author Comment

by:NIS_Longfellow
ID: 17107634
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.
0
 

Author Comment

by:NIS_Longfellow
ID: 17108048
Whoa...just saw your later response...let me try that
0
 

Author Comment

by:NIS_Longfellow
ID: 17108225
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
0
 
LVL 16

Expert Comment

by:MohanKNair
ID: 17120548
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)) a,
 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_cycle_id||' '||c2.sum_steps);
 18  END LOOP;
 19  END LOOP;
 20  end;
 21  /
10197 57

PL/SQL procedure successfully completed.
0
 

Author Comment

by:NIS_Longfellow
ID: 17122850
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
0
 
LVL 16

Accepted Solution

by:
MohanKNair earned 500 total points
ID: 17127555
One solution is to create a pipelined function. Pipelined functions are useful if there is a need for a customized data source.
Table functions are functions that produce a collection of rows (either a nested table or a varray) that can be queried like a physical database table - http://www.psoug.org/reference/pipelined.html

1) create Colelction object types in SQL

create or replace TYPE STEPS_TYPE as OBJECT(tc_cycle_id number, tot_steps number);
/

create or replace TYPE STEPS_TAB as TABLE of STEPS_TYPE;
/

2) Create pipelined function

create or replace function get_total_steps
RETURN STEPS_TAB PIPELINED
IS
outrec STEPS_TYPE := STEPS_TYPE(null,null);
n1 number;
BEGIN
for c1 in(select distinct tc_cycle_id from Testcycl) LOOP
for c2 IN(
select sum(ts_steps) sum_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=c1.tc_cycle_id)) a,
test b
WHERE a.DS_TEST_ID=b.TS_TEST_ID and DS_LINK_ID is null) LOOP
-- dbms_output.put_line(c1.tc_cycle_id||' '||c2.sum_steps);
outrec.tc_cycle_id := c1.tc_cycle_id;
outrec.tot_steps   := c2.sum_steps;
PIPE ROW(outrec);
END LOOP;
END LOOP;
RETURN;
end;
/

3) Select from pipelined function

SQL> select * from TABLE(get_total_steps);

Also see the following links

Oracle Pipelined Table Functions - http://www.akadia.com/services/ora_pipe_functions.html
Streamlining Oracle 9i ETL With Pipelined Table Functions - http://orafaq.com/node/38
Pipelined Table Functions - http://www.oracle-base.com/articles/9i/PipelinedTableFunctions9i.php
Using PIPELINED in PL/SQL - http://www.adp-gmbh.ch/ora/plsql/pipeline.html

0
 

Author Comment

by:NIS_Longfellow
ID: 17132208
This looks like it will Work!  Let me do some testing today.

Thanks!

Shane
0
 

Author Comment

by:NIS_Longfellow
ID: 17138101
That  is going to work!   Thanks for all your help!
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Article by: Swadhin
From the Oracle SQL Reference (http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/queries006.htm) we are told that a join is a query that combines rows from two or more tables, views, or materialized views. This article provides a glimps…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now