Excellent

sys_connect_by_path is available in 8.1.7 as an undocumented feature, which can be enabled by

alter session set "_new_connect_by_enabled" = true;

or setting this parameter in init.ora

Hi Tom

Consider the table structures as below

Table A

ID PARENT_ID TYPE STATUS
1 1 S PROAPP
2 1 R PROAPP
3 2 R PROAPP
4 3 C APP
5 5 S PROAPP
6 5 R PROAPP
7 6 C APP

Table B

ID VALUE
1 Value one
5 Value two

Now, I have to write a query that will select records from Table A with STATUS = 'APP'( in the above example ids 4 and 7 ) but trawl through to get ids 1 and 5 ( TYPE = 'S' and parents of ids 4 and 7 ) and then join with Table B to retrieve VALUE column. The volume is high in both the tables ( Table A : 200000 records, Table B: 5000000 records ).

I had raised a similar sort of query before and received an excellent feedback.

Thanks in advance

Sorry Tom - Ids 1 and 5 are ultimate parents of ids 4 and 7 in the above example. Ultimate parents always have a type value of S. Only ultimate parent Ids have values in Table B.

Many Thanks

No problem - Please find enclosed the scripts

SQL> create table a
  2  (id number,
  3   parent_id number,
  4   type varchar2(1),
  5   status varchar2(10));

Table created.

SQL> insert into a values(1,1,'S','PROAPP');

1 row created.

SQL> insert into a values(2,1,'R','PROAPP');

1 row created.

SQL> insert into a values(3,2,'R','PROAPP');

1 row created.

SQL> insert into a values(4,3,'C','APP');

1 row created.

SQL> insert into a values(5,5,'S','PROAPP');

1 row created.

SQL> insert into a values(6,5,'R','PROAPP');

1 row created.

SQL> insert into a values(7,6,'C','APP');

1 row created.

SQL> commit;

Commit complete.

SQL> create table b
  2  (id number,
  3   value varchar2(2000));

Table created.

SQL> insert into b values(1,'Value One');

1 row created.

SQL> insert into b values(5,'Value Two');

1 row created.

SQL> commit;

Commit complete.

Thanks 

ops$tkyte@ORA9IR2> select a.*, b.*
  2    from (
  3  select a.* ,
  4         to_number( substr(
  5             (select max(to_char(level,'fm00000')||id)
  6                from a a2
  7                   start with id = a.parent_id
  8                 connect by prior parent_id = id and prior parent_id <> prior id), 6 ) ) ultimate_pid
  9    from a
 10   where status = 'APP'
 11         ) a, b
 12   where b.id = a.ultimate_pid
 13  /
 
        ID  PARENT_ID T STATUS     ULTIMATE_PID         ID VALUE
---------- ---------- - ---------- ------------ ---------- ---------------
         4          3 C APP                   1          1 Value One
         7          6 C APP                   5          5 Value Two
 

Thanks Tom - This is exactly what I wanted.

hi tom,

i've to select all "chains" (sets of related entries under a certain node) that don't include a given node.

create table tab(
id number,
parentid number,
constraint pk primary key( id ),
constraint fk foreign key( parentid ) references tab( id )
)
/

insert into tab( id, parentid ) values( 1, null ) ;
insert into tab( id, parentid ) values( 2, 1 ) ;
insert into tab( id, parentid ) values( 3, 2 ) ;
insert into tab( id, parentid ) values( 4, null ) ;
insert into tab( id, parentid ) values( 5, 4 ) ;
commit ;

select level, id, parentid
from tab
start with parentid is null
connect by prior id = parentid
order by id ;

is there a way to skip - lets say - the "chain" 1->2->3 (all nodes under id = 1) due to the fact that this subset includes the node with id = 3?

ops$tkyte@ORA9IR2> variable the_bad_guy number;
ops$tkyte@ORA9IR2> exec :the_bad_guy := 3
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select substr( rpad('*', 2*level, '*' ) || id, 1, 20 ) idstr
  2    from tab
  3   start with
  4     ( parentid is null
  5       and
  6       not exists ( select null
  7                      from tab t2
  8                     where id = :the_bad_guy
  9                     start with parentid = tab.id
 10                   connect by prior id = parentid )
 11     )
 12  connect by prior id = parentid
 13  /
 
IDSTR
--------------------
**4
****5
 

flip@flop> variable the_bad_guy number;
flip@flop> exec :the_bad_guy := 1

PL/SQL procedure successfully completed.

flip@flop> select substr( rpad('*', 2*level, '*' ) || id, 1, 20 ) idstr
2 from tab
3 start with parentid is null
4 connect by prior id = parentid
5 order by id ;

IDSTR
------------------------------------------------------
**1
****2
******3
**4
****5

flip@flop> select substr( rpad('*', 2*level, '*' ) || id, 1, 20 ) idstr
2 from tab
3 start with
4 ( parentid is null
5 and
6 not exists ( select null
7 from tab t2
8 where id = :the_bad_guy
9 start with parentid = tab.id
10 connect by prior id = parentid )
11 )
12 connect by prior id = parentid
13 /

IDSTR
----------------------------------------------------
**1
****2
******3
**4
****5

Oh ... and "chains" is a bit confusing in that question ... "subtree" would be a bit more appropriate.

Thanks.

Hi Tom,
since we talked about connect by I have question. I have the following table
key1 sdt edt val1
--- ---------- -------- -----
1 1/1/2000 2/1/2000 1
1 2/1/2000 3/1/2000 1
1 3/1/2000 4/1/2000 2
1 4/1/2000 5/1/2000 2
1 5/1/2000 6/1/2000 2
1 6/1/2000 7/1/2000 3
1 7/1/2000 8/1/2000 1

basically for a given key between given dates it had a value, kinda historical table. The problem is that as you can see first two rows can be compressed into one. First two rows should become one (1, 1/1/2000, 3/1/2003, 1) as it had same values between first two rows. Same with the next three row (1,3/1/2000,6/1/2000,2). I did this to get to the following result.
SELECT key1, MIN(sdt), MAX(edt), val1
FROM (SELECT LEVEL, SYS_CONNECT_BY_PATH( TO_CHAR(sdt,'yyyymmdd') , '/') path, key1, sdt, edt, val1
FROM myt t1
START WITH sdt = (SELECT MIN(sdt) FROM myt t2 WHERE t2.key1 = t1.key1 AND t2.val1 = t1.val1)
CONNECT BY sdt = PRIOR edt
AND key1 = PRIOR key1
AND val1 = PRIOR val1
)
GROUP BY key1, SUBSTR(path,1,8), val1;

I was wondering if there is a better way to do this. what kinda indexes would help this query.

key1 sdt edt val1
--- ---------- -------- -----
1 1/1/2000 3/1/2000 1
1 3/1/2000 6/1/2000 2
1 6/1/2000 7/1/2000 3
1 7/1/2000 8/1/2000 1

Thanks in advance.

If you think the above question is a new question thats alright not to answer at this point. But if you want to answer the question, I found a problem with the above query already. So I am still looking for a query that would work. Also if there is another column lets say, val2 and if it does have different values for the rows we are compressing, I want the resulting row should have value from the first row.
Thanks again

Here is the answer with new KEY1 values. This is of course using one of Tom's techniques.

SQL> create table t (key1 number(2), start_date date, end_date date, val1 number(2));

Table created.

SQL> alter session set nls_date_format = 'dd/mm/yyyy';

Session altered.

SQL> 
SQL> insert into t values (1, to_date('1/1/2000'), to_date('2/1/2000'), 1);

1 row created.

SQL> insert into t values (1, to_date('2/1/2000'), to_date('3/1/2000'), 1);

1 row created.

SQL> insert into t values (1, to_date('3/1/2000'), to_date('4/1/2000'), 2);

1 row created.

SQL> insert into t values (1, to_date('4/1/2000'), to_date('5/1/2000'), 2);

1 row created.

SQL> insert into t values (1, to_date('5/1/2000'), to_date('6/1/2000'), 2);

1 row created.

SQL> insert into t values (1, to_date('6/1/2000'), to_date('7/1/2000'), 3);

1 row created.

SQL> insert into t values (1, to_date('7/1/2000'), to_date('8/1/2000'), 1);

1 row created.

SQL> 
SQL> insert into t values (2, to_date('11/1/2000'), to_date('12/1/2000'), 1);

1 row created.

SQL> insert into t values (2, to_date('12/1/2000'), to_date('13/1/2000'), 1);

1 row created.

SQL> insert into t values (2, to_date('13/1/2000'), to_date('14/1/2000'), 1);

1 row created.

SQL> insert into t values (2, to_date('14/1/2000'), to_date('15/1/2000'), 2);

1 row created.

SQL> insert into t values (2, to_date('15/1/2000'), to_date('16/1/2000'), 2);

1 row created.

SQL> insert into t values (2, to_date('16/1/2000'), to_date('17/1/2000'), 3);

1 row created.

SQL> insert into t values (2, to_date('17/1/2000'), to_date('18/1/2000'), 3);

1 row created.

SQL> insert into t values (2, to_date('18/1/2000'), to_date('19/1/2000'), 3);

1 row created.

SQL> insert into t values (2, to_date('19/1/2000'), to_date('20/1/2000'), 1);

1 row created.

SQL> 
SQL> alter session set nls_date_format = 'dd-Mon-yyyy';

Session altered.

SQL> select * from t order by key1, start_date;

      KEY1 START_DATE  END_DATE          VAL1                                   
---------- ----------- ----------- ----------                                   
         1 01-Jan-2000 02-Jan-2000          1                                   
         1 02-Jan-2000 03-Jan-2000          1                                   
         1 03-Jan-2000 04-Jan-2000          2                                   
         1 04-Jan-2000 05-Jan-2000          2                                   
         1 05-Jan-2000 06-Jan-2000          2                                   
         1 06-Jan-2000 07-Jan-2000          3                                   
         1 07-Jan-2000 08-Jan-2000          1                                   
         2 11-Jan-2000 12-Jan-2000          1                                   
         2 12-Jan-2000 13-Jan-2000          1                                   
         2 13-Jan-2000 14-Jan-2000          1                                   
         2 14-Jan-2000 15-Jan-2000          2                                   
         2 15-Jan-2000 16-Jan-2000          2                                   
         2 16-Jan-2000 17-Jan-2000          3                                   
         2 17-Jan-2000 18-Jan-2000          3                                   
         2 18-Jan-2000 19-Jan-2000          3                                   
         2 19-Jan-2000 20-Jan-2000          1                                   

16 rows selected.

SQL> 
SQL> select key1, min(start_date) start_date, max(end_date) end_date, val1
  2       from (
  3       select key1, start_date, end_date, val1,
  4            max(rn) over (partition by key1 order by start_date) grp
  5       from (
  6    select key1, start_date, end_date, val1,
  7            case when lag(val1) over (partition by key1 order by start_date) <> val1 or
  8              row_number() over (partition by key1 order by start_date) = 1
  9             then row_number() over (partition by key1 order by start_date)
 10             end rn
 11       from t
 12            )
 13            )
 14      group by key1, grp, val1
 15      order by key1, start_date;

      KEY1 START_DATE  END_DATE          VAL1                                   
---------- ----------- ----------- ----------                                   
         1 01-Jan-2000 03-Jan-2000          1                                   
         1 03-Jan-2000 06-Jan-2000          2                                   
         1 06-Jan-2000 07-Jan-2000          3                                   
         1 07-Jan-2000 08-Jan-2000          1                                   
         2 11-Jan-2000 14-Jan-2000          1                                   
         2 14-Jan-2000 16-Jan-2000          2                                   
         2 16-Jan-2000 19-Jan-2000          3                                   
         2 19-Jan-2000 20-Jan-2000          1                                   

8 rows selected.

 

Hello Tom! You was so helpful before, so here I am again :)
We have an application that registers veterynary clinics and doctors all around the country. Every clinic or doctor has an area in which it is supposed to give services. So we used the third-party dictionary which contains all territories in our country organized into hierarchical structure. We have three levels of hierarchy: region/district/town or village (a little more complicated, but it is of no matter for this question).
Table has next fields:
create table TERR
(
TER_ID NUMBER NOT NULL, - territory id
NAME_ID NUMBER NOT NULL, - id of territory name
TER_TER_ID NUMBER, - link to the territory 1 level higher
TER_LEVEL NUMBER NOT NULL, - level of territory
TER_TYPE NUMBER NOT NULL, - type of the territory
TER_CODE VARCHAR2(15) - territory unique code
)
and
create table TNAMES
(
NAME_ID NUMBER NOT NULL, - name id
NAME VARCHAR2(240), - territory name
)
Near 30 first-level territories, 700 second-level and 40000 of third level.
Our table, that contains clinics links to these tables by TER_ID. The task is to create search function for finding clinics and doctors around the country using different filters. Using your great idea about using sys_context to create dynamic searches with varying number of filters, I still need to add one more filter - by territory.
So, I need to find all clinics in given territory and all underlying. In one of our previous applications, where we have our own hierarchical dictionary, we created table TERR_LINKS with fields PARENT and CHILD and filled it in the way, where every territory has at least one record, where it was parent and child at the same time.
So our filter was as simpe as:
...AND clinics.ter_ter_id IN (SELECT child
FROM TERR_LINKS
WHERE parent = p_ter_id)
But now there are some problems, realising this approach, because we can't change the dictionary (add tables in its schema) ourselves (but can add this table in our schema) and because data in the dictionary potentially could be updated through third-party application, so we'll need keep track of this changes and update our table too.
Second approach I'm thinking of now is something like this:
...AND clinics.ter_ter_id IN
(SELECT t.ter_id
FROM terr t
START WITH t.ter_ter_id = p_ter_id
CONNECT BY PRIOR t.ter_id = t.ter_ter_id
UNION ALL
SELECT p_ter_id FROM dual)
I test this thing on one of our reports and it showed 303 sec against 290 in the variant with additional 'links' table. Not so big difference, but... Few times before, I have some strange results using hierarchical queries - the plan went crazy and queries were running for hours and hours, so I'm a bit confused if it happens again. Also, for now I have tested the perfomance only on few queries and can't say how much the difference will be on more complicated reports and search-functions.
Can you give few words on what approach, you'll better use in this situation, maybe something completely new, that I haven't think of.
In any case, want to say, that I admire your great work. This site is a little miracle for Oracle users around the world.
Thanks

Is it possible to get multiple records of a particular employee within a
hierarchy in specific cases?

I have the following table structure.


create table EMPINFO
( c_STATUS VARCHAR2(2),
c_GROUP VARCHAR2(10),
c_SYSID VARCHAR2(10),
c_SYSTEM VARCHAR2(10),
c_NAME VARCHAR2(40),
c_ID VARCHAR2(10),
c_MGRID VARCHAR2(10),
c_MGRNAME VARCHAR2(40)
);

Typical Data is
insert into EMPINFO VALUES ('A','HR','','','JASON BICKER','0000001','','');
insert into EMPINFO VALUES ('A','HR','','','NANCY
WALTER','0400001','0000001','JASON BICKER');
insert into EMPINFO VALUES ('','MF','NAN3005','WIN','NANCY
WALTER','0400001','','');
insert into EMPINFO VALUES ('','MF','NAN3005','UNIX','NANCY
WALTER','0400001','','');
insert into EMPINFO VALUES ('A','HR','','','ADAM
BRYAN','0400002','0000001','JASON BICKER');
insert into EMPINFO VALUES ('','MF','BRY0034','WIN','ADAM
BRYAN','0400002','','');
insert into EMPINFO VALUES ('A','HR','','','STING
CORY','0400003','0400002','ADAM BRYAN');
insert into EMPINFO VALUES ('','MF','STI4040','UNIX','STING
CORY','0400003','','');


For example I want to display all records of NANCY WALTER. The c_MGRID is blank
for 2nd and 3rd record of NANCY WALTER. Similar structure will follow for other
records as well.

Regards,
Rao

Hi Tom,
How do we find the data causing ORA-01436

create table asd( parent number, child number);
insert into asd values(1,2);
insert into asd values(2,1);
insert into asd values(1,3);

/NCLDBA/NV00 > select parent,child
2 from asd
3 start with parent=1
4 connect by prior child = parent;
ERROR:
ORA-01436: CONNECT BY loop in user data



no rows selected

/NCLDBA/NV00 >

Can we get (1,2) (2,1) etc.

Regards,
Shivdeep Modi

ops$tkyte%ORA10GR2> select parent,child, connect_by_iscycle
  2    from asd
  3   start with parent=1
  4  connect by NOCYCLE prior child = parent;

    PARENT      CHILD CONNECT_BY_ISCYCLE
---------- ---------- ------------------
         1          2                  0
         2          1                  1
         1          3                  0
         1          3                  0

ops$tkyte%ORA9IR2> begin
  2          for x in (
  3  select level l,parent,child
  4    from asd
  5   start with parent=1
  6  connect by prior child = parent )
  7          loop
  8                  dbms_output.put_line( to_char( x.l, '9999' ) || ') ' ||
  9                                        to_char( x.parent, '99999' ) || to_char(x.child,'99999') );
 10          end loop;
 11  end;
 12  /
1)      1     2
2)      2     1
3)      1     2
begin
*
ERROR at line 1:
ORA-01436: CONNECT BY loop in user data
ORA-06512: at line 2

Thanks Tom,

10g stuff is good, but 9i requires more work.
If in Oracle9i database, I've got something like.

insert into asd values(1,2);
insert into asd values(1,3);
insert into asd values(2,1);
insert into asd values(3,4);
insert into asd values(4,5);
insert into asd values(5,4);

I need to fix/remove (1,2) (2,1) to find (4,5) (5,4). I don't think we can find all the cyclic rows in a single SQL|PL/SQL or can we?

Regards,
Shivdeep

Hello Tom,
I have writtern ORACLE 10G PL/SQL Stored Procedure which has some "Hierarchical Queries".
I want to create same Stored Procedure in MS SQL SERVER 2005.
Do I need to perform any changes in the "Hierarchical Queries".

( I am a Oracle PL/SQL Developer.But current project needs to create same stored procedures in MS SQL SERVER 2005).

Please Advice me.
Thanks and Best Regards
-Hemal

Hi Tom,

We need to write a query to get the hierarchy among the financial advisers for our client. We wrote this:

SELECT parent_fa_no, finadv_no, LEVEL, --CONNECT_BY_ISCYCLE,
SYS_CONNECT_BY_PATH(itr_no, '/')
FROM Finadvtab
WHERE status = 'LIVE'
START WITH parent_fa_no = 100
CONNECT BY /*NOCYCLE */ PRIOR finadv_no = parent_fa_no

But, we faced this error:

ORA-01436: CONNECT BY loop in user data.

We are on 9i and we need to do what NOCYCLE and CONNECT_BY_ISCYCLE pseudocolumn can do in 10g.

Could you please suggest a workaround for this?

Thanks.

Sorry the query is rather:

SELECT parent_fa_no, finadv_no, LEVEL, --CONNECT_BY_ISCYCLE,
SYS_CONNECT_BY_PATH(finadv_no, '/')
FROM Finadvtab
WHERE status = 'LIVE'
START WITH parent_fa_no = 100
CONNECT BY /*NOCYCLE */ PRIOR finadv_no = parent_fa_no

Thanks.

Hi Tom,

....you need to answer this, can you logically look at a row and say "stop here")

Yes, we have a field (say, Subadv_count) that gives us the number of sub-advisers for a given finadv_no. We are happy to stop when this count becomes zero, i.e. when we have reached the leaf. Until then, we need to traverse down the hierarchy.

Is there anything you can put into your connect by to detect that you are entering a cycle...

Does my previous answer somehow address this too? I couldn't catch you here.

Thanks.

In that case, will it not prevent us from picking up the advisers at the leaf of the hierarchy? Did you mean rather this?

connect by ... and prior subadv_count > 0;

Well, we tried both with & without 'prior', still facing the error. May be, we are falling short somewhere on the information regarding "...how it relates to itself...".

Thanks.

Tom,

Well, we have 2 more fields in each row, which are related to the hierarchy:

MasterAdv_no: The adviser at the root of the hierarchy.

HierarchyPosition: Similar to Oracle's LEVEL keyword.

Thanks.

Tom Thanks for coming back and Staying Alive.

Please Keep it up. You dont know how these 5 days have been for us without AskTOM our Metalink 24/7 support.

Staying Alive Staying Alive .......

So, that's the way it is....

SELECT parent_fa_no, finadv_no, LEVEL, --CONNECT_BY_ISCYCLE,
SYS_CONNECT_BY_PATH(finadv_no, '/')
FROM Finadvtab
WHERE status = 'LIVE'
START WITH parent_fa_no = 100
CONNECT BY /*NOCYCLE */ PRIOR finadv_no = parent_fa_no
AND PRIOR MasterAdv_no = MasterAdv_no
AND PRIOR Subadv_count > 0
AND PRIOR HierarchyPosition < HierarchyPosition;

Thanks Tom. This solved the problem. The way you drive us to the solution is amazing.......

Many thanks again.

Hi Tom,
This is Rishipal, and want to store .pdf and .txt files in oracle databse. I am querying u very first time I visited your site and find something valuable.looking u forward
Thanks........

Hi Tom,

I am sure, there can be no-one else than you who can tell us a solution for this.

Basically, we have a table called payment_details as created & populated below:

CREATE TABLE payment_details
(start_date DATE,
end_date DATE,
pay_amount NUMBER(12,2)
);

INSERT INTO payment_details
SELECT trunc(add_months(SYSDATE,-24)), trunc(add_months(SYSDATE,-6)), 24.50
FROM dual
UNION ALL
SELECT trunc(add_months(SYSDATE,-30)),trunc(add_months(SYSDATE,-12)), 32.30
FROM dual;

Now, we need to write a query that would display all the payments done for each of the records in the above table in this format:

Payment_date, Pay_amount

The frequency of payment is say, monthly. So, for the first record in payment_details, it should display like below:

Payment_date Pay_amount
22/06/2005 24.50
22/07/2005 24.50
22/08/2005 24.50
.....
.....
22/12/2006 24.50

Similarly, for the 2nd record too. So, we need to expand one row in payment_details into multiple rows, but "how many" is not a static value and is dependent on the duration (end_date - start_date). So, we tried this query:

SELECT add_months(start_date,LEVEL-1) paid_date, --start_date, end_date, pay_amount
FROM payment_details, dual
CONNECT BY LEVEL < months_between(end_date,start_date);

But, this query works like an atomic bomb!!! It grows exponentially!! We tried a simple case:

SELECT add_months(start_date,LEVEL-1) paid_date, --start_date, end_date, pay_amount
FROM payment_details, dual
CONNECT BY LEVEL < 3 --months_between(end_date,start_date);

This creates 1 row for 22/06/2005, but 2 rows for 22/07/2005 (for the 1st row in payment_details)

When we do this:

SELECT add_months(start_date,LEVEL-1) paid_date, --start_date, end_date, pay_amount
FROM payment_details, dual
CONNECT BY LEVEL < 4 --months_between(end_date,start_date);

it creates 1 row for 22/06/2005, 2 rows for 22/07/2005 and 4 rows for 22/08/2005.

Could you please suggest a workaround for this? Obviously, we can add DISTINCT to the above query to get one row for each month's payment. But, that would be a drastically bad idea, I guess. Using dual this way seems to be "carcinogenic" for SQL!!

ops$tkyte%ORA10GR2> select start_date, end_date, pay_amount, ceil( months_between(end_date,start_date)) rws
  2    from payment_details
  3  /

START_DAT END_DATE  PAY_AMOUNT        RWS
--------- --------- ---------- ----------
22-JUN-05 22-DEC-06       24.5         18
22-DEC-04 22-JUL-06       32.3         19

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> select start_date, end_date, add_months(start_date,level-1) DT, pay_amount,
  2         ceil( months_between(end_date,start_date)) rws
  3    from payment_details
  4  connect by prior start_date = start_date and prior end_date = end_date and prior pay_amount = pay_amount
  5    and level <= ceil( months_between(end_date,start_date))
  6    AND PRIOR dbms_random.string ('p', 10) IS NOT NULL
  7   order by 1, 2, 5
  8  /

START_DAT END_DATE  DT        PAY_AMOUNT        RWS
--------- --------- --------- ---------- ----------
22-DEC-04 22-JUL-06 22-DEC-04       32.3         19
22-DEC-04 22-JUL-06 22-JAN-05       32.3         19
22-DEC-04 22-JUL-06 22-JUN-06       32.3         19
22-DEC-04 22-JUL-06 22-MAY-06       32.3         19
22-DEC-04 22-JUL-06 22-APR-06       32.3         19
22-DEC-04 22-JUL-06 22-MAR-06       32.3         19
22-DEC-04 22-JUL-06 22-FEB-06       32.3         19
22-DEC-04 22-JUL-06 22-JAN-06       32.3         19
22-DEC-04 22-JUL-06 22-DEC-05       32.3         19
22-DEC-04 22-JUL-06 22-NOV-05       32.3         19
22-DEC-04 22-JUL-06 22-OCT-05       32.3         19
22-DEC-04 22-JUL-06 22-SEP-05       32.3         19
22-DEC-04 22-JUL-06 22-AUG-05       32.3         19
22-DEC-04 22-JUL-06 22-JUL-05       32.3         19
22-DEC-04 22-JUL-06 22-JUN-05       32.3         19
22-DEC-04 22-JUL-06 22-MAY-05       32.3         19
22-DEC-04 22-JUL-06 22-APR-05       32.3         19
22-DEC-04 22-JUL-06 22-MAR-05       32.3         19
22-DEC-04 22-JUL-06 22-FEB-05       32.3         19
22-JUN-05 22-DEC-06 22-OCT-06       24.5         18
22-JUN-05 22-DEC-06 22-NOV-06       24.5         18
22-JUN-05 22-DEC-06 22-AUG-06       24.5         18
22-JUN-05 22-DEC-06 22-JUL-06       24.5         18
22-JUN-05 22-DEC-06 22-JUN-06       24.5         18
22-JUN-05 22-DEC-06 22-MAY-06       24.5         18
22-JUN-05 22-DEC-06 22-APR-06       24.5         18
22-JUN-05 22-DEC-06 22-MAR-06       24.5         18
22-JUN-05 22-DEC-06 22-FEB-06       24.5         18
22-JUN-05 22-DEC-06 22-JAN-06       24.5         18
22-JUN-05 22-DEC-06 22-DEC-05       24.5         18
22-JUN-05 22-DEC-06 22-NOV-05       24.5         18
22-JUN-05 22-DEC-06 22-OCT-05       24.5         18
22-JUN-05 22-DEC-06 22-SEP-05       24.5         18
22-JUN-05 22-DEC-06 22-AUG-05       24.5         18
22-JUN-05 22-DEC-06 22-JUL-05       24.5         18
22-JUN-05 22-DEC-06 22-JUN-05       24.5         18
22-JUN-05 22-DEC-06 22-SEP-06       24.5         18

37 rows selected.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> with data
  2  as
  3  (select level l
  4     from (select max( ceil( months_between(end_date,start_date)) ) rws from payment_details)
  5   connect by level <= rws
  6  )
  7  select start_date, end_date, add_months(start_date,l-1) DT, pay_amount,
  8         ceil( months_between(end_date,start_date)) rws
  9    from payment_details, data
 10   where l <= ceil( months_between(end_date,start_date))
 11   order by 1, 2, 5
 12  /

START_DAT END_DATE  DT        PAY_AMOUNT        RWS
--------- --------- --------- ---------- ----------
22-DEC-04 22-JUL-06 22-DEC-04       32.3         19
22-DEC-04 22-JUL-06 22-MAY-06       32.3         19
22-DEC-04 22-JUL-06 22-APR-06       32.3         19
22-DEC-04 22-JUL-06 22-MAR-06       32.3         19
22-DEC-04 22-JUL-06 22-FEB-06       32.3         19
22-DEC-04 22-JUL-06 22-JAN-06       32.3         19
22-DEC-04 22-JUL-06 22-DEC-05       32.3         19
22-DEC-04 22-JUL-06 22-NOV-05       32.3         19
22-DEC-04 22-JUL-06 22-OCT-05       32.3         19
22-DEC-04 22-JUL-06 22-SEP-05       32.3         19
22-DEC-04 22-JUL-06 22-AUG-05       32.3         19
22-DEC-04 22-JUL-06 22-JUL-05       32.3         19
22-DEC-04 22-JUL-06 22-JUN-05       32.3         19
22-DEC-04 22-JUL-06 22-MAY-05       32.3         19
22-DEC-04 22-JUL-06 22-APR-05       32.3         19
22-DEC-04 22-JUL-06 22-MAR-05       32.3         19
22-DEC-04 22-JUL-06 22-FEB-05       32.3         19
22-DEC-04 22-JUL-06 22-JAN-05       32.3         19
22-DEC-04 22-JUL-06 22-JUN-06       32.3         19
22-JUN-05 22-DEC-06 22-JUL-06       24.5         18
22-JUN-05 22-DEC-06 22-JUN-05       24.5         18
22-JUN-05 22-DEC-06 22-SEP-06       24.5         18
22-JUN-05 22-DEC-06 22-JAN-06       24.5         18
22-JUN-05 22-DEC-06 22-JUN-06       24.5         18
22-JUN-05 22-DEC-06 22-DEC-05       24.5         18
22-JUN-05 22-DEC-06 22-OCT-06       24.5         18
22-JUN-05 22-DEC-06 22-NOV-05       24.5         18
22-JUN-05 22-DEC-06 22-MAY-06       24.5         18
22-JUN-05 22-DEC-06 22-OCT-05       24.5         18
22-JUN-05 22-DEC-06 22-AUG-06       24.5         18
22-JUN-05 22-DEC-06 22-SEP-05       24.5         18
22-JUN-05 22-DEC-06 22-APR-06       24.5         18
22-JUN-05 22-DEC-06 22-AUG-05       24.5         18
22-JUN-05 22-DEC-06 22-NOV-06       24.5         18
22-JUN-05 22-DEC-06 22-JUL-05       24.5         18
22-JUN-05 22-DEC-06 22-MAR-06       24.5         18
22-JUN-05 22-DEC-06 22-FEB-06       24.5         18

37 rows selected.

Oh, and I see, dual is not required and the following works similar to that with dual:

SELECT add_months(start_date,LEVEL-1) paid_date, --start_date, end_date, pay_amount
FROM payment_details
CONNECT BY LEVEL < months_between(end_date,start_date);

Thanks.

Oh my god!!! I am literally spellbound!!!

Tom, you are a genius!!! That's why you are the guru of all gurus!!! And, that's why, I am a disciple of you!!!

I wish I can meet you and talk to you one day!!!

Many Thanks.

Hi,

Please help to write Hirearichal query

SELECT XLEVEL1_NAME,XLEVEL2_NAME, XLEVEL3_NAME
FROM
CATEGORY

XLEVEL1_NAME XLEVEL2_NAME XLEVEL3_NAME
Product
Product Hardware
Product Hardware Networking
CA

CA XXXX

CA XXXX YYYY


I want to write a query

If XLEVEL1_NAME is not null(Root) then return parnet record i.e Product

If XLEVEL1_NAME and XLEVEL2_NAME is not null then return the child record of 2nd row i.e.Hardware


If XLEVEL1_NAME and XLEVEL2_NAME and XLEVEL2_NAME not null then return child record of 3 row i.e.Networking


I want results like this



XLEVEL1_NAME XLEVEL2_NAME XLEVEL3_NAME level1 level 2
Product 1 1
Hardware 1 2
Networking 1 3


CA 2 1
XXX 2 2

YYYY 2 3


Thanks,
MM

Tom,
I have a rather large query using analytic functions, subqueries, and a WITH clause. The query runs in a couple minutes which considering the millions of records its processing and the complex analytics being performed is just fine. A general structure is like this:

WITH q1 AS (),
q2 AS ()
SELECT x, y, z
FROM q1, q2
WHERE q1.something = q2.something

you get the idea. q1 and q2 have some analytics and subqueries buried in them. My problem now comes when I want to use a hierarchical query on that result set. The above query only returns 1100 rows, so logically in my mind I'm thinking that when I wrap it like this:

SELECT x, y, z FROM ( above query )
START WITH ...
CONNECT BY PRIOR ...

it should run pretty quick. The problem is that it does not and when I look at the explain plan, it is actually showing the inner queries 3 times. I was hoping to see the inner query get performed once, then the hierarchical "stuff" get performed on a materialized version of that. How can I force the "START WITH ... CONNECT BY ..." to operate on the 1100 row results without re-executing the inner pieces of the query multiple times?

with q1 as (),
q2 as (),
q3 as (above query)
select ... from q3 start with....

Hello Tom,

I've tried to write a query which returns whether two people are in a chain of command or not. It behaves kind of strange: when I try one way (is A subordinate of B) and then the other (is B subordinate of A) then everything is OK. But when I try to union both ways in one query, I get an error.

example:
select * from v$version;

1 Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bi
2 PL/SQL Release 10.2.0.3.0 - Production
3 CORE 10.2.0.3.0 Production
4 TNS for IBM/AIX RISC System/6000: Version 10.2.0.3.0 - Productio
5 NLSRTL Version 10.2.0.3.0 - Production


select empno, ename, mgr from emp;

1 7369 SMITH 7902
2 7499 ALLEN 7698
3 7521 WARD 7698
4 7566 JONES 7839
5 7654 MARTIN 7698
6 7698 BLAKE 7839
7 7782 CLARK 7839
8 7788 SCOTT 7566
9 7839 KING
10 7844 TURNER 7698
11 7876 ADAMS 7788
12 7900 JAMES 7698
13 7902 FORD 7566
14 7934 MILLER 7782


select * from emp
where ename = 'KIN';
<empty set>
----------------------

select decode(count(*) , 0, 'NO', 'YES') in_chain
from (
select ename
from emp
start with ename = 'SCOTT'
connect by mgr = prior empno

)
where ename = 'KIN';

in_chain NO --KIN is not SCOTT's subordinate

select decode(count(*) , 0, 'NO', 'YES') in_chain
from (
select ename
from emp
start with ename = 'SCOTT'
connect by prior mgr = empno

)
where ename = 'KIN';

in_chain NO --KIN is not SCOTT's superordinate

So far, so good. But:
select decode(count(*) , 0, 'NO', 'YES') in_chain
from (

select ename
from emp
start with ename = 'SCOTT'
connect by prior mgr = empno

union

select ename
from emp
start with ename = 'SCOTT'
connect by mgr = prior empno

)
where ename = 'KIN';

in_chain YES --KIN is either SCOTT's superordinate or subordinate

Could you tell me where the error is?

ops$tkyte%ORA10GR2> create table emp as select * from scott.emp;

Table created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> select decode(count(*) , 0, 'NO', 'YES') in_chain
  2    from (
  3          select ename
  4            from emp
  5           start with ename = 'SCOTT'
  6          connect by mgr = prior empno
  7          )
  8   where ename = 'KIN';

IN_
---
NO

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> select decode(count(*) , 0, 'NO', 'YES') in_chain
  2    from (
  3          select ename
  4            from emp
  5           start with ename = 'SCOTT'
  6          connect by prior mgr = empno
  7          )
  8   where ename = 'KIN';

IN_
---
NO

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> select decode(count(*) , 0, 'NO', 'YES') in_chain
  2    from (
  3          select ename
  4            from emp
  5           start with ename = 'SCOTT'
  6          connect by prior mgr = empno
  7          union
  8          select ename
  9            from emp
 10           start with ename = 'SCOTT'
 11          connect by mgr = prior empno
 12          )
 13   where ename = 'KIN';

IN_
---
NO

Well, if it works well for you then you can't probably help me.

I was able only to further the bizarreness of the example:

select *
from (
select ename
from emp
start with ename = 'SCOTT'
connect by prior mgr = empno

)
where 0=1;

<empty set>

select *
from (
select ename
from emp
start with ename = 'SCOTT'
connect by prior mgr = empno
union
select '' ename from dual
)
where 0=1;

ename
-----
SCOTT

Basically it's the same as before, but the weirdness can be clearly seen, I think.

I have included DDL to be sure. Cut&Paste crippled some non-ascii characters, but it isn't relevant for this example. I included two selects. One with only the hierarchical query, and one with the hierarchical query 'unioned' with an empty set. The former behaves correctly, the latter is not as you can see.

Thank you for your help.


pravda_m@ISD> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bi
PL/SQL Release 10.2.0.3.0 - Production
CORE    10.2.0.3.0      Production
TNS for IBM/AIX RISC System/6000: Version 10.2.0.3.0 - Productio
NLSRTL Version 10.2.0.3.0 - Production

pravda_m@ISD> drop table emp_test;

Tabulka zru¿ena.

pravda_m@ISD> create table emp_test(
  2  empno number primary key,
  3  ename varchar2(50),
  4  job varchar2(50),
  5  mgr number);

Tabulka vytvoøena.

pravda_m@ISD> insert into emp_test(empno, ename, job, mgr) values (7369, 'SMITH', 'CLERK', 7902);

1 øádka vytvoøena.

pravda_m@ISD> insert into emp_test(empno, ename, job, mgr) values (7499, 'ALLEN', 'SALESMAN', 7698);


1 øádka vytvoøena.

pravda_m@ISD> insert into emp_test(empno, ename, job, mgr) values (7521, 'WARD', 'SALESMAN', 7698);

1 øádka vytvoøena.

pravda_m@ISD> insert into emp_test(empno, ename, job, mgr) values (7566, 'JONES', 'MANAGER', 7839);

1 øádka vytvoøena.

pravda_m@ISD> insert into emp_test(empno, ename, job, mgr) values (7654, 'MARTIN', 'SALESMAN', 7698)
;

1 øádka vytvoøena.

pravda_m@ISD> insert into emp_test(empno, ename, job, mgr) values (7698, 'BLAKE', 'MANAGER', 7839);

1 øádka vytvoøena.

pravda_m@ISD> insert into emp_test(empno, ename, job, mgr) values (7782, 'CLARK', 'MANAGER', 7839);

1 øádka vytvoøena.

pravda_m@ISD> insert into emp_test(empno, ename, job, mgr) values (7788, 'SCOTT', 'ANALYST', 7566);

1 øádka vytvoøena.

pravda_m@ISD> insert into emp_test(empno, ename, job, mgr) values (7839, 'KING', 'PRESIDENT', null);


1 øádka vytvoøena.

pravda_m@ISD> insert into emp_test(empno, ename, job, mgr) values (7844, 'TURNER', 'SALESMAN', 7698)
;

1 øádka vytvoøena.

pravda_m@ISD> insert into emp_test(empno, ename, job, mgr) values (7876, 'ADAMS', 'CLERK', 7788);

1 øádka vytvoøena.

pravda_m@ISD> insert into emp_test(empno, ename, job, mgr) values (7900, 'JAMES', 'CLERK', 7698);

1 øádka vytvoøena.

pravda_m@ISD> insert into emp_test(empno, ename, job, mgr) values (7902, 'FORD', 'ANALYST', 7566);

1 øádka vytvoøena.

pravda_m@ISD> insert into emp_test(empno, ename, job, mgr) values (7934, 'MILLER', 'CLERK', 7782);

1 øádka vytvoøena.

pravda_m@ISD>   commit;

Potvrzení dokonèeno.

pravda_m@ISD> set autotrace on
pravda_m@ISD>  select *
  2    from (
  3          select ename
  4            from emp_test
  5           start with ename = 'SCOTT'
  6          connect by prior mgr = empno
  7          /*union 
  8          select null ename from dual where 0=1  
  9          */
 10          )
 11   where 0=1;

nebyly vybrány ¿ádné øádky


Plán provedení
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=0 Card=1 Bytes=77)
   1    0   FILTER
   2    1     VIEW (Cost=1 Card=1 Bytes=77)
   3    2       CONNECT BY (WITH FILTERING)
   4    3         TABLE ACCESS (FULL) OF 'EMP_TEST' (TABLE) (Cost=3 Card=1 Bytes=180)
   5    3         NESTED LOOPS
   6    5           BUFFER (SORT)
   7    6             CONNECT BY PUMP
   8    5           TABLE ACCESS (BY INDEX ROWID) OF 'EMP_TEST' (TABLE) (Cost=1 Card=1 Bytes=103)
   9    8             INDEX (UNIQUE SCAN) OF 'SYS_C0027225' (INDEX (UNIQUE)) (Cost=1 Card=1)
  10    3         TABLE ACCESS (FULL) OF 'EMP_TEST' (TABLE) (Cost=3 Card=14 Bytes=1442)




Statistiky
----------------------------------------------------------
         10  recursive calls
          0  db block gets
         24  consistent gets
          0  physical reads
          0  redo size
        155  bytes sent via SQL*Net to client
        236  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed

pravda_m@ISD>  select *
  2    from (
  3          select ename
  4            from emp_test
  5           start with ename = 'SCOTT'
  6          connect by prior mgr = empno
  7          union 
  8          select null ename from dual where 0=1  
  9          )
 10   where 0=1;

ENAME
--------------------------------------------------
SCOTT


Plán provedení
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2 Card=2 Bytes=154)
   1    0   VIEW (Cost=2 Card=2 Bytes=154)
   2    1     SORT (UNIQUE) (Cost=2 Card=2 Bytes=103)
   3    2       UNION-ALL
   4    3         CONNECT BY (WITH FILTERING)
   5    4           TABLE ACCESS (FULL) OF 'EMP_TEST' (TABLE) (Cost=3 Card=1 Bytes=180)
   6    4           FILTER
   7    6             NESTED LOOPS
   8    7               BUFFER (SORT)
   9    8                 CONNECT BY PUMP
  10    7               TABLE ACCESS (BY INDEX ROWID) OF 'EMP_TEST' (TABLE) (Cost=1 Card=1 Bytes=103)
  11   10                 INDEX (UNIQUE SCAN) OF 'SYS_C0027225' (INDEX (UNIQUE)) (Cost=1 Card=1)
  12    4           TABLE ACCESS (FULL) OF 'EMP_TEST' (TABLE) (Cost=3 Card=14 Bytes=1442)
  13    3         FILTER
  14   13           FAST DUAL (Cost=2 Card=1)




Statistiky
----------------------------------------------------------
         10  recursive calls
          0  db block gets
         31  consistent gets
          0  physical reads
          0  redo size
        227  bytes sent via SQL*Net to client
        277  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          4  sorts (memory)
          0  sorts (disk)
          1  rows processed

pravda_m@ISD> 

It looks to me that filter operation moved from line "1" to line "6" in the explain plan. My guess is that that is what is wrong, but I don't know why.

I have a table with data as following.

SL_No (PK) Ref_No TIN
---------- ------ --------
11 Ref-11 52001
22 Ref-12 52006
33 Ref-56 65009
44 Ref-86 78004
55 Ref-57 52006
66 Ref-61 99011
77 Ref-96 52001
88 Ref-33 23055
99 Ref-26 65008

I want a Hierarchical Tree like this to use in Oracle Forms 10gR2 on windows xp and Oracle10g database.

23055
| |-Ref-33
|
52001
| |-Ref-11
| |-Ref-96
|
52006
| |-Ref-12
| |-Ref-57
|
65008
|
|
.......................
.......................
etc.

---scripts--------------
create table htree_test
(
sl_no number(2),
ref_no varchar2(10),
tin varchar2(5));
/
insert into htree_test values (11,'Ref-11','52001');
insert into htree_test values (22,'Ref-12','52006');
insert into htree_test values (33,'Ref-56','65009');
insert into htree_test values (44,'Ref-86','78004');
insert into htree_test values (55,'Ref-57','52006');
insert into htree_test values (66,'Ref-61','99011');
insert into htree_test values (77,'Ref-96','52001');
insert into htree_test values (88,'Ref-33','23055');
insert into htree_test values (99,'Ref-26','65008');
commit;
/

Pls help me.
Best regards.
Dulal

Hi,
Thanks for your quick reply.
But Sir, I want the query syntax only.
e.g.
SELECT 1, LEVEL, TIN, .......................

Pls help.

ops$tkyte%ORA10GR2> select tin, ref_no, grouping(tin), grouping(ref_no)
  2    from t
  3   group by rollup(tin,ref_no)
  4   order by tin, grouping(ref_no) DESC
  5  /

TIN   REF_NO     GROUPING(TIN) GROUPING(REF_NO)
----- ---------- ------------- ----------------
23055                        0                1
23055 Ref-33                 0                0
52001                        0                1
52001 Ref-96                 0                0
52001 Ref-11                 0                0
52006                        0                1
52006 Ref-12                 0                0
52006 Ref-57                 0                0
65008                        0                1
65008 Ref-26                 0                0
65009                        0                1
65009 Ref-56                 0                0
78004                        0                1
78004 Ref-86                 0                0
99011                        0                1
99011 Ref-61                 0                0
                             1                1

17 rows selected.

ops$tkyte%ORA10GR2> select decode(gref_no,1,tin) newtin, ref_no
  2    from (
  3  select tin, ref_no, grouping(tin), grouping(ref_no) gref_no
  4    from t
  5   group by rollup(tin,ref_no)
  6         )
  7   order by tin, gref_no DESC
  8  /

NEWTI REF_NO
----- ----------
23055
      Ref-33
52001
      Ref-96
      Ref-11
52006
      Ref-12
      Ref-57
65008
      Ref-26
65009
      Ref-56
78004
      Ref-86
99011
      Ref-61


17 rows selected.

Hi Tom,
I learnt a lot from this site, thank you for hosting it.
Could you please help me understand what is the difference between the queries pasted below.I am just trying to understand the concept so practically the data might not make sense. Create/insert below as you insist everytime ;)
create table COUNTER
(
X INTEGER,
Y VARCHAR2(10),
MGR NUMBER(2)
)

INSERT INTO counter VALUES (1,'A',0);
INSERT INTO counter VALUES (2,'B',1);
INSERT INTO counter VALUES (3,'C',1);
INSERT INTO counter VALUES (4,'D',2);
INSERT INTO counter VALUES (5,'E',3);
INSERT INTO counter VALUES (6,'F',2);
INSERT INTO counter VALUES (7,'G',4);
INSERT INTO counter VALUES (8,'H',6);
commit;
Here are the queries
------------------------------
SELECT x,y,mgr,LEVEL FROM (SELECT x,y,mgr FROM counter )
START WITH mgr=0 CONNECT BY PRIOR x=mgr
ORDER BY mgr
* This gives me the result what i "thought" Connect By is built for, however when I change x=mgr in the clause to mgr=x it gives me a totally different output.
----------------------------------------------------
Also,
Do not understand how Start With clause works
SELECT x,y,mgr,LEVEL FROM (SELECT x,y,mgr FROM counter )
/*START WITH mgr=0*/ CONNECT BY PRIOR x=mgr
ORDER BY mgr

Awaiting your reply.

Cheers!!!
Bhushan

ops$tkyte%ORA11GR1> select * from counter where mgr=0;

         X Y                 MGR
---------- ---------- ----------
         1 A                   0

ops$tkyte%ORA11GR1> select x from counter where mgr = 0;

         X
----------
         1

ops$tkyte%ORA11GR1> select * from counter where (select x from counter where mgr=0) = mgr;

         X Y                 MGR
---------- ---------- ----------
         2 B                   1
         3 C                   1

ops$tkyte%ORA11GR1> SELECT rpad('*',2*level,'*') data, x,y,mgr,LEVEL
  2  FROM (SELECT x,y,mgr FROM counter )
  3  START WITH mgr=0 CONNECT BY   PRIOR x=mgr
  4  ORDER BY mgr
  5  /

DATA                X Y                 MGR      LEVEL
---------- ---------- ---------- ---------- ----------
**                  1 A                   0          1
****                2 B                   1          2
****                3 C                   1          2
******              4 D                   2          3
******              6 F                   2          3
******              5 E                   3          3
********            7 G                   4          4
********            8 H                   6          4

8 rows selected.

ops$tkyte%ORA11GR1> SELECT rpad('*',2*level,'*') data, x,y,mgr,LEVEL
  2  FROM (SELECT x,y,mgr FROM counter )
  3  START WITH 1=1 CONNECT BY   PRIOR x=mgr
  4  ORDER BY mgr
  5  /

DATA                X Y                 MGR      LEVEL
---------- ---------- ---------- ---------- ----------
**                  1 A                   0          1
****                2 B                   1          2
****                3 C                   1          2
**                  2 B                   1          1
**                  3 C                   1          1
****                4 D                   2          2
******              6 F                   2          3
**                  6 F                   2          1
****                6 F                   2          2
**                  4 D                   2          1
******              4 D                   2          3
******              5 E                   3          3
****                5 E                   3          2
**                  5 E                   3          1
********            7 G                   4          4
******              7 G                   4          3
**                  7 G                   4          1
****                7 G                   4          2
****                8 H                   6          2
**                  8 H                   6          1
********            8 H                   6          4
******              8 H                   6          3

22 rows selected.

Can it get better guys!!!

Cheers to you Tom.

--Bhushan

Hi Tom,
Need some help on avoiding a FULL table scan on a connect by prior in a select statement. Is it possible to decrease the cost using a FBI or rewriting the sql to get the same resultset.

thanks for any feedback
lava

Added indexes on empno and mgr for doing index range scan.
create index idx1 on emp (empno);
create index idx2 on emp (mgr);

select empno
from emp
start with empno = 7698
connect by prior empno = mgr;

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 CONNECT BY (WITH FILTERING)
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'EMP'
3 2 INDEX (RANGE SCAN) OF 'IDX1' (NON-UNIQUE)
4 1 NESTED LOOPS
5 4 BUFFER (SORT)
6 5 CONNECT BY PUMP
7 4 TABLE ACCESS (BY INDEX ROWID) OF 'EMP'
8 7 INDEX (RANGE SCAN) OF 'IDX2' (NON-UNIQUE)
9 1 TABLE ACCESS (FULL) OF 'EMP'

select empno
          from emp
         start with empno  = 7698
        connect by prior empno = mgr

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.01       0.03          0          3          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.00       0.00          0          5          0           6
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.01       0.03          0          8          0           6

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 96

Rows     Row Source Operation
-------  ---------------------------------------------------
      6  CONNECT BY WITH FILTERING (cr=5 pr=0 pw=0 time=563 us)
      1   TABLE ACCESS BY INDEX ROWID EMP (cr=2 pr=0 pw=0 time=103 us)
      1    INDEX RANGE SCAN IDX1 (cr=1 pr=0 pw=0 time=66 us)(object id 56677)
      5   NESTED LOOPS  (cr=3 pr=0 pw=0 time=320 us)
      6    BUFFER SORT (cr=0 pr=0 pw=0 time=122 us)
      6     CONNECT BY PUMP  (cr=0 pr=0 pw=0 time=60 us)
      5    TABLE ACCESS BY INDEX ROWID EMP (cr=3 pr=0 pw=0 time=160 us)
      5     INDEX RANGE SCAN IDX2 (cr=2 pr=0 pw=0 time=101 us)(object id 56678)
      0   TABLE ACCESS FULL EMP (cr=0 pr=0 pw=0 time=0 us)

Was assisting on performance tuning on a database.
They use a similar query for online/dss application. The app team uses a similar query on a table with 50K rows in test and 77k rows in prod. Based on the predicate used, it takes 2+seconds. Based on the complex sql statement, being used it takes 2 to several minutes to bring up their webpage. The expected response time for their reports and webpages is sub-second < 2 seconds.
Below is some of the information related to this.

Thanks for your earlier response.
lava

SQL> select count(*) from org_point;

  COUNT(*)
----------
     54462

SQL> set timing on
SQL> set autotrace traceonly explain statistics
SQL> SELECT DISTINCT ORG_POINT_ID
  2  FROM ORG_POINT
  3  START WITH ORG_POINT_ID = 350
  4  CONNECT BY PRIOR ORG_POINT_ID = RPTS_TO_ORG_POINT_ID
  5  ;

11777 rows selected.

Elapsed: 00:00:04.99

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=13 Card=83 Bytes=830)
   1    0   SORT (UNIQUE) (Cost=13 Card=83 Bytes=830)
   2    1     CONNECT BY (WITH FILTERING)
   3    2       TABLE ACCESS (BY INDEX ROWID) OF 'ORG_POINT'
   4    3         INDEX (UNIQUE SCAN) OF 'ORG_POINT_PK' (UNIQUE) (Cost=2 Card=1 Bytes=5)
   5    2       NESTED LOOPS
   6    5         BUFFER (SORT)
   7    6           CONNECT BY PUMP
   8    5         TABLE ACCESS (BY INDEX ROWID) OF 'ORG_POINT' (Cost=12 Card=83 Bytes=830)
   9    8           INDEX (RANGE SCAN) OF 'ORG_POINT_I01' (NON-UNIQUE) (Cost=2 Card=75)
  10    2       TABLE ACCESS (FULL) OF 'ORG_POINT'


Statistics
----------------------------------------------------------
         14  recursive calls
      72602  db block gets
       1243  consistent gets
       1908  physical reads
          0  redo size
      94948  bytes sent via SQL*Net to client
       5738  bytes received via SQL*Net from client
        787  SQL*Net roundtrips to/from client
          8  sorts (memory)
          2  sorts (disk)
      11777  rows processed

Tom,
Below is the tkprof report. Sorry for not giving the right information in the original posting.

lava

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.01 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 787 2.05 2.85 1776 1239 72602 11777
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 789 2.05 2.86 1776 1239 72602 11777

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 592 (LAVATEST)

Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE
11777 SORT (UNIQUE)
11777 CONNECT BY (WITH FILTERING)
2 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF 'ORG_POINT'
2 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF 'ORG_POINT_PK' (UNIQUE)
5598 NESTED LOOPS
10 BUFFER (SORT)
10 CONNECT BY PUMP
5598 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF 'ORG_POINT'
5598 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'ORG_POINT_I01' (NON-UNIQUE)
54462 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'ORG_POINT'

We are trying to create a table of historical org charts. As long as I only have one organization in the table, the quaery returns correctly, but as soon as I add another organization, the children are duplicated. What am I doing wrong? I have tried changing the where to be part of the connect by to no avail.

here is my script:

set scan off
--
drop table org_chart;
create table org_chart
(quarter number(1),
year number(4),
main_id number(9),
seq number(3),
parent number(3),
id number(9));
--
insert into org_chart values( 1, 2008, 480228, 1, null, 3510);
insert into org_chart values( 1, 2008, 480228, 82, 1, 1099579);
insert into org_chart values( 1, 2008, 480228, 89, 1, 1426755);
insert into org_chart values( 1, 2008, 480228, 100, 1, 27666);
insert into org_chart values( 1, 2008, 480228, 102, 100, 27183);
insert into org_chart values( 1, 2008, 480228, 109, 1, 38172);
--
commit;
--
column seq_num format a30
select lpad(' ', 4*(level-1))||seq as seq_num,
level, parent, id
from org_chart
where main_id = 480228 and quarter = 1 and year = 2008
start with seq = 1
connect by parent = prior seq;
--
insert into org_chart values( 1, 2008, 1022269, 1, null, 1022269);
insert into org_chart values( 1, 2008, 1022269, 2, 1, 1022278);
insert into org_chart values( 1, 2008, 1022269, 3, 2, 23502);
commit;
--
select lpad(' ', 4*(level-1))||seq as seq_num,
level, parent, id
from org_chart
where main_id = 480228 and quarter = 1 and year = 2008
start with seq = 1
connect by parent = prior seq;
--


here is the output from the first select:

SEQ_NUM LEVEL PARENT ID
------------------------------ ---------- ---------- ----------
1 1 3510
82 2 1 1099579
89 2 1 1426755
100 2 1 27666
102 3 100 27183
109 2 1 38172



Here is the second select result:

SEQ_NUM LEVEL PARENT ID
------------------------------ ---------- ---------- ----------
1 1 3510
82 2 1 1099579
89 2 1 1426755
100 2 1 27666
102 3 100 27183
109 2 1 38172
82 2 1 1099579
89 2 1 1426755
100 2 1 27666
102 3 100 27183
109 2 1 38172

11 rows selected.

where   main_id = 480228 and quarter = 1 and year = 2008
start   with seq = 1
connect by parent = prior seq;

I thought I had given enough info, but I guess not! This table is to hold thousands of companies, for multiple time periods. The main_id is the id of the top-level parent, id is the individual company id. So main_id 480228 is one company with his siblings. main_id 1022269 is another company. The query works correctly when there is just one company in the table, but, when I add a second company, the results duplicate. When I try moving the where to the connect, I get the same result: duplicated. Based upon what you wrote, I would expect the following to work correctly, and return the same as the first query (with one company in the table):

select lpad(' ', 4*(level-1))||seq as seq_num,
level, parent, id
from org_chart
start with seq = 1
connect by parent = prior seq
and main_id = 480228 and quarter = 1 and year = 2008;

But this returns the same as using the where. Based upon what I have seen, it looks like one can not use this query if the table contains multiple company hierarchies (multiple families). I know my data, and have put together a very simple example for this. I know what I expect to get, but don't understand why I am getting duplicated results. thanks

Hi:
I have hierarchical query where i would like to know all childs of the parent and thier levels(considering 'n' levels of generation)

Hi:
I have hierarchical query where i would like to know all childs of the parent and thier levels(considering 'n' levels of generation)

Hi:
I have hierarchical query where i would like to know all childs of a parent if parent_id is given as input(considering 'n' levels of generation) in oracle

CREATE TABLE AD_PPARENT (

ID NUMBER (10) NOT NULL,

NAME VARCHAR2 (30),

PAID NUMBER (10),

PANAME VARCHAR2 (30),

GENDER CHAR (1),

PRIMARY KEY ( ID ));

CREATE OR REPLACE PROCEDURE SP_GETCHILD (PARENT_ID IN NUMBER)
AS
V_ID NUMBER(10,0);
V_GENDER CHAR(1);
V_NAME VARCHAR2(30);
CURSOR getchild IS SELECT id, name, gender FROM AD_PPARENT where PAID = PARENT_ID;
BEGIN
open getchild;
fetch getchild into V_ID,V_NAME , V_GENDER;
while(getchild%FOUND) loop
dbms_output.put_line (' CHILD ID == ' || V_ID || ' ' || 'CHILD NAME == ' || V_NAME || ' ' || 'CHILD GENDER == ' || V_GENDER );
fetch getchild into V_ID,V_NAME, V_GENDER;
end loop;
END;
/

CREATE OR REPLACE PROCEDURE SP_GETCHILD (p_PARENT_ID IN NUMBER)
AS
   CURSOR getchild  IS <b>
   SELECT level id, name, gender 
     FROM AD_PPARENT 
    start with PAID = PARENT_ID
  connect by prior id = parent_id;</b>
BEGIN
   for x in (getchild)
   loop
      dbms_output.put_line 
      (' CHILD ID == ' || x.ID || ' ' || 'CHILD NAME == ' || 
         x.NAME || ' ' || 'CHILD GENDER == ' || x.GENDER );
   end loop;
END;
/ 

hi Tom,
I have created the below store proc but it does not give me the required output.

I got the following feedback from my manager"Where are you getting the value of v_level_id in 'CURSOR getchild IS SELECT ID,NAME,LEVEL_ID FROM PARENT where PARENT_ID = P_PARENT_ID AND LEVEL_ID=V_LEVEL_ID;'.

i believe the initial value of v_level_id is zero and the above query would return you all rows corresponding to the given parentid with level=0 which does not solve the problem."

Please help me with the required changes to be made in the store proc for getting the right output.

CREATE TABLE PARENT (

NAME VARCHAR2 (30),

PARENT_ID NUMBER (10),

LEVEL_ID NUMBER (10),

ID NUMBER (10) NOT NULL,

CONSTRAINT PRIMARYKEY

PRIMARY KEY ( ID );

CREATE OR REPLACE PROCEDURE SP_GETCHILD (P_PARENT_ID IN NUMBER)
AS
V_ID NUMBER(10,0);
V_LEVEL_ID NUMBER(10,0);
V_NAME VARCHAR2(30);
CURSOR getchild IS SELECT ID,NAME,LEVEL_ID FROM PARENT where PARENT_ID = P_PARENT_ID AND LEVEL_ID=V_LEVEL_ID;
BEGIN
SELECT LEVEL_ID INTO V_LEVEL_ID FROM PARENT WHERE ID = P_PARENT_ID;
V_LEVEL_ID := V_LEVEL_ID + 1;
open getchild;
fetch getchild into V_ID,V_NAME ,V_LEVEL_ID;
while(getchild%FOUND) loop
dbms_output.put_line (' CHILD ID == ' || V_ID || ' ' || 'CHILD NAME == ' || V_NAME || ' ' || 'LEVEL_ID == ' || V_LEVEL_ID );
fetch getchild into V_ID,V_NAME, V_LEVEL_ID;
end loop;
END;
/

regards
khaliq

A note can be stored at any node of the CUSTOMER hierarchy below. I'm writing a query to retrieve all notes in the hierarchy for a specific customer. The query below returns all notes starting at a specific node (ie. CUSTOMER) on down the hierarchy.

Two questions:

1) How can I restrict the query to retrieve notes down to a certain level (say, all notes from CONTRACT to INVOICE_LINE_ITEM?

2) How can I remove the duplicate note (specifically the 2nd occurence) that will always appear at a node that has 2 parents (see: INVOICE_LINE and CREDIT_APPLICATION) and still keep the query sorted by Parent_ID and Note_ID? I've tried using DISTINCT but get the error "Not a SELECTed expression". I've also tried inlines views and correlated subquerys to no avail. I'm obviously missing something easy.

Note 1: If you start with CONTRACT, CREDIT_APPLICATION appears twice as well but shouldn't because one of its parents - CREDIT - is not included in the hierarchy.
Note 2: Having the Parent_ID column in the query is not necessary (just TABLE_NAME, DISPLAY, and NOTE in the correct order).

Thanks

Customer Hierarchy
==================

CUSTOMER (top)
    |
    ^
CUSTOMER_ACCOUNT <------------- CREDIT
    |                              |
    ^                              ^
CONTRACT <-------- INVOICE <---- CREDIT_APPLICATION
    |                 |
    ^                 ^
CONTRACT_LINE <--- INVOICE_LINE
                      |
                      ^
                   INVOICE_LINE_ITEM
                      |
                      ^
                   INVOICE_LINE_ITEM_CHG
                      |
                      ^
                   INVOICE_LINE_ITEM_CHG_TAX (bottom)


set linesize 140
column table_name format a25
column display_name format a30
column note format a65

SELECT srt.parent_ID,
       srt.table_name AS TABLE_NAME,
       srt.display_name AS Display,
       -- nrt.ref_uid_value AS REF_ID,
       -- n.id,
       n.note
FROM   note n,
       note_ref_table nrt,
       sys_ref_table srt,
       (SELECT DISTINCT sr1.id,
               sr1.table_name
        FROM   sys_ref_table sr1
        START  WITH sr1.id = (SELECT sr2.id
                              FROM   sys_ref_table sr2
                              WHERE  sr2.table_name = 'CUSTOMER')
        CONNECT BY PRIOR sr1.id = sr1.parent_id
        ORDER  BY sr1.id) ord
WHERE  n.id = nrt.note_id
AND    nrt.ref_table_id IN ord.id
AND    srt.table_name = ord.table_name
ORDER  BY srt.parent_ID,
          n.id;

 PARENT_ID TABLE_NAME                DISPLAY                        NOTE
---------- ------------------------- ------------------------------ ---------------------------------------------------------------
         0 CUSTOMER                  Customer                       CUSTOMER Note #1 for Invoice#: 000000000004273
         1 CUSTOMER_ACCOUNT          Customer Account               CUSTOMER_ACCOUNT Note #1 for Invoice#: 000000000004273
         2 CONTRACT                  Contract                       CONTRACT Note #1 for Invoice#: 000000000004273
         2 CREDIT                    Credit                         CREDIT Note #1 for Invoice#: 000000000004273
         3 INVOICE                   Invoice                        INVOICE Note #1 for Invoice#: 000000000004273
         3 CONTRACT_LINE             Contract Line                  CONTRACT_LINE Note #1 for Invoice#: 000000000004273
         4 INVOICE_LINE              Invoice Line                   INVOICE_LINE Note #1 for Invoice#: 000000000004273
         5 INVOICE_LINE              Invoice Line                   INVOICE_LINE Note #1 for Invoice#: 000000000004273
         5 CREDIT_APPLICATION        Credit Application             CREDIT_APPLICATION Note #1 for Invoice#: 000000000004273
         6 INVOICE_LINE_ITEM         Invoice Line Item              INVOICE_LINE_ITEM Note #1 for Invoice#: 000000000004273
         6 INVOICE_LINE_ITEM         Invoice Line Item              INVOICE_LINE_ITEM Note #2 for Invoice#: 000000000004273
         6 INVOICE_LINE_ITEM         Invoice Line Item              INVOICE_LINE_ITEM Note #3 for Invoice#: 000000000004273
         8 INV_LINE_ITEM_CHG         Invoice Line Item Charge       INVOICE_LINE_ITEM_CHG Note #1 for Invoice#: 000000000004273
         8 INV_LINE_ITEM_CHG         Invoice Line Item Charge       INVOICE_LINE_ITEM_CHG Note #2 for Invoice#: 000000000004273
         8 INV_LINE_ITEM_CHG         Invoice Line Item Charge       INVOICE_LINE_ITEM_CHG Note #3 for Invoice#: 000000000004271
         8 INV_LINE_ITEM_CHG         Invoice Line Item Charge       INVOICE_LINE_ITEM_CHG Note #4 for Invoice#: 000000000004273
         8 INV_LINE_ITEM_CHG         Invoice Line Item Charge       INVOICE_LINE_ITEM_CHG Note #5 for Invoice#: 000000000004273
         9 INV_LINE_ITEM_CHG_TAX     Invoice Line Item Charge Tax   INVOICE_LINE_ITEM_CHG_TAX Note #1 for Invoice#: 000000000004273
         9 INV_LINE_ITEM_CHG_TAX     Invoice Line Item Charge Tax   INVOICE_LINE_ITEM_CHG_TAX Note #2 for Invoice#: 000000000004273
         9 INV_LINE_ITEM_CHG_TAX     Invoice Line Item Charge Tax   INVOICE_LINE_ITEM_CHG_TAX Note #3 for Invoice#: 000000000004273
        11 CREDIT_APPLICATION        Credit Application             CREDIT_APPLICATION Note #1 for Invoice#: 000000000004273


/*   CREATE TABLES   */
CREATE TABLE SYS_REF_TABLE
(
  ID                     NUMBER,
  PARENT_ID              NUMBER,
  TABLE_NAME             VARCHAR2(30),
  DISPLAY_NAME           VARCHAR2(100)
);

CREATE UNIQUE INDEX SYS_REF_TABLE_PK ON SYS_REF_TABLE
(ID, PARENT_ID);

CREATE TABLE NOTE
(
  ID                     NUMBER                 NOT NULL,
  NOTE                   VARCHAR2(4000)         NOT NULL
);

CREATE UNIQUE INDEX NOTE_PK ON NOTE
(ID);

CREATE TABLE NOTE_REF_TABLE
(
  NOTE_ID                NUMBER                 NOT NULL,
  REF_TABLE_ID           NUMBER                 NOT NULL,
  REF_UID_VALUE          NUMBER                 NOT NULL
);

CREATE UNIQUE INDEX NOTE_REF_TABLE_PK ON NOTE_REF_TABLE
(NOTE_ID, REF_TABLE_ID, REF_UID_VALUE);


/*   SYS_REF_TABLE   */
Insert into SYS_REF_TABLE
   (ID, TABLE_NAME, DISPLAY_NAME, PARENT_ID)
 Values
   (1, 'CUSTOMER', 'Customer', 0);
Insert into SYS_REF_TABLE
   (ID, TABLE_NAME, DISPLAY_NAME, PARENT_ID)
 Values
   (2, 'CUSTOMER_ACCOUNT', 'Customer Account', 1);
Insert into SYS_REF_TABLE
   (ID, TABLE_NAME, DISPLAY_NAME, PARENT_ID)
 Values
   (7, 'ISSUE', 'Issue', 2);
Insert into SYS_REF_TABLE
   (ID, TABLE_NAME, DISPLAY_NAME, PARENT_ID)
 Values
   (3, 'CONTRACT', 'Contract', 2);
Insert into SYS_REF_TABLE
   (ID, TABLE_NAME, DISPLAY_NAME, PARENT_ID)
 Values
   (11, 'CREDIT', 'Credit', 2);
Insert into SYS_REF_TABLE
   (ID, TABLE_NAME, DISPLAY_NAME, PARENT_ID)
 Values
   (4, 'CONTRACT_LINE', 'Contract Line', 3);
Insert into SYS_REF_TABLE
   (ID, TABLE_NAME, DISPLAY_NAME, PARENT_ID)
 Values
   (5, 'INVOICE', 'Invoice', 3);
Insert into SYS_REF_TABLE
   (ID, TABLE_NAME, DISPLAY_NAME, PARENT_ID)
 Values
   (6, 'INVOICE_LINE', 'Invoice Line', 4);
Insert into SYS_REF_TABLE
   (ID, TABLE_NAME, DISPLAY_NAME, PARENT_ID)
 Values
   (6, 'INVOICE_LINE', 'Invoice Line', 5);
Insert into SYS_REF_TABLE
   (ID, TABLE_NAME, DISPLAY_NAME, PARENT_ID)
 Values
   (12, 'CREDIT_APPLICATION', 'Credit Application', 5);
Insert into SYS_REF_TABLE
   (ID, TABLE_NAME, DISPLAY_NAME, PARENT_ID)
 Values
   (8, 'INVOICE_LINE_ITEM', 'Invoice Line Item', 6);
Insert into SYS_REF_TABLE
   (ID, TABLE_NAME, DISPLAY_NAME, PARENT_ID)
 Values
   (9, 'INV_LINE_ITEM_CHG', 'Invoice Line Item Charge', 8);
Insert into SYS_REF_TABLE
   (ID, TABLE_NAME, DISPLAY_NAME, PARENT_ID)
 Values
   (10, 'INV_LINE_ITEM_CHG_TAX', 'Invoice Line Item Charge Tax', 9);
Insert into SYS_REF_TABLE
   (ID, TABLE_NAME, DISPLAY_NAME, PARENT_ID)
 Values
   (12, 'CREDIT_APPLICATION', 'Credit Application', 11);

/*   NOTE   */
Insert into NOTE
   (ID, NOTE)
 Values
   (10187, 'INVOICE Note #1 for Invoice#: 000000000004273');
Insert into NOTE
   (ID, NOTE)
 Values
   (10274, 'CREDIT Note #1 for Invoice#: 000000000004273');
Insert into NOTE
   (ID, NOTE)
 Values
   (10273, 'CUSTOMER Note #1 for Invoice#: 000000000004273');
Insert into NOTE
   (ID, NOTE)
 Values
   (10272, 'CONTRACT_LINE Note #1 for Invoice#: 000000000004273');
Insert into NOTE
   (ID, NOTE)
 Values
   (10271, 'CONTRACT Note #1 for Invoice#: 000000000004273');
Insert into NOTE
   (ID, NOTE)
 Values
   (10270, 'CUSTOMER_ACCOUNT Note #1 for Invoice#: 000000000004273');
Insert into NOTE
   (ID, NOTE)
 Values
   (10269, 'INVOICE_LINE_ITEM_CHG_TAX Note #3 for Invoice#: 000000000004273');
Insert into NOTE
   (ID, NOTE)
 Values
   (10268, 'INVOICE_LINE_ITEM_CHG_TAX Note #2 for Invoice#: 000000000004273');
Insert into NOTE
   (ID, NOTE)
 Values
   (10267, 'INVOICE_LINE_ITEM_CHG_TAX Note #1 for Invoice#: 000000000004273');
Insert into NOTE
   (ID, NOTE)
 Values
   (10266, 'INVOICE_LINE_ITEM_CHG Note #5 for Invoice#: 000000000004273');
Insert into NOTE
   (ID, NOTE)
 Values
   (10265, 'INVOICE_LINE_ITEM_CHG Note #4 for Invoice#: 000000000004273');
Insert into NOTE
   (ID, NOTE)
 Values
   (10264, 'INVOICE_LINE_ITEM_CHG Note #3 for Invoice#: 000000000004271');
Insert into NOTE
   (ID, NOTE)
 Values
   (10263, 'INVOICE_LINE_ITEM_CHG Note #2 for Invoice#: 000000000004273');
Insert into NOTE
   (ID, NOTE)
 Values
   (10262, 'INVOICE_LINE_ITEM_CHG Note #1 for Invoice#: 000000000004273');
Insert into NOTE
   (ID, NOTE)
 Values
   (10260, 'INVOICE_LINE_ITEM Note #3 for Invoice#: 000000000004273');
Insert into NOTE
   (ID, NOTE)
 Values
   (10259, 'INVOICE_LINE_ITEM Note #2 for Invoice#: 000000000004273');
Insert into NOTE
   (ID, NOTE)
 Values
   (10258, 'INVOICE_LINE_ITEM Note #1 for Invoice#: 000000000004273');
Insert into NOTE
   (ID, NOTE)
 Values
   (10257, 'INVOICE_LINE Note #1 for Invoice#: 000000000004273');
Insert into NOTE
   (ID, NOTE)
 Values
   (10280, 'CREDIT_APPLICATION Note #1 for Invoice#: 000000000004273');


/*   NOTE_REF_TABLE   */
Insert into NOTE_REF_TABLE
   (NOTE_ID, REF_TABLE_ID, REF_UID_VALUE)
 Values
   (10274, 11, 191);
Insert into NOTE_REF_TABLE
   (NOTE_ID, REF_TABLE_ID, REF_UID_VALUE)
 Values
   (10273, 1, 285);
Insert into NOTE_REF_TABLE
   (NOTE_ID, REF_TABLE_ID, REF_UID_VALUE)
 Values
   (10272, 4, 52);
Insert into NOTE_REF_TABLE
   (NOTE_ID, REF_TABLE_ID, REF_UID_VALUE)
 Values
   (10271, 3, 310);
Insert into NOTE_REF_TABLE
   (NOTE_ID, REF_TABLE_ID, REF_UID_VALUE)
 Values
   (10270, 2, 53);
Insert into NOTE_REF_TABLE
   (NOTE_ID, REF_TABLE_ID, REF_UID_VALUE)
 Values
   (10269, 10, 84756);
Insert into NOTE_REF_TABLE
   (NOTE_ID, REF_TABLE_ID, REF_UID_VALUE)
 Values
   (10268, 10, 84765);
Insert into NOTE_REF_TABLE
   (NOTE_ID, REF_TABLE_ID, REF_UID_VALUE)
 Values
   (10267, 10, 84761);
Insert into NOTE_REF_TABLE
   (NOTE_ID, REF_TABLE_ID, REF_UID_VALUE)
 Values
   (10266, 9, 44512);
Insert into NOTE_REF_TABLE
   (NOTE_ID, REF_TABLE_ID, REF_UID_VALUE)
 Values
   (10265, 9, 44511);
Insert into NOTE_REF_TABLE
   (NOTE_ID, REF_TABLE_ID, REF_UID_VALUE)
 Values
   (10264, 9, 44510);
Insert into NOTE_REF_TABLE
   (NOTE_ID, REF_TABLE_ID, REF_UID_VALUE)
 Values
   (10263, 9, 44507);
Insert into NOTE_REF_TABLE
   (NOTE_ID, REF_TABLE_ID, REF_UID_VALUE)
 Values
   (10262, 9, 44505);
Insert into NOTE_REF_TABLE
   (NOTE_ID, REF_TABLE_ID, REF_UID_VALUE)
 Values
   (10260, 8, 24784);
Insert into NOTE_REF_TABLE
   (NOTE_ID, REF_TABLE_ID, REF_UID_VALUE)
 Values
   (10259, 8, 24782);
Insert into NOTE_REF_TABLE
   (NOTE_ID, REF_TABLE_ID, REF_UID_VALUE)
 Values
   (10258, 8, 24781);
Insert into NOTE_REF_TABLE
   (NOTE_ID, REF_TABLE_ID, REF_UID_VALUE)
 Values
   (10187, 5, 4273);
Insert into NOTE_REF_TABLE
   (NOTE_ID, REF_TABLE_ID, REF_UID_VALUE)
 Values
   (10257, 6, 4271);
Insert into NOTE_REF_TABLE
   (NOTE_ID, REF_TABLE_ID, REF_UID_VALUE)
 Values
   (10280, 12, 256);

commit;

I have a procedure which has number of select statements. 
I tried to execute the queries separately. 
One of those queries is:

SELECT   COUNT(C.CASE_ID) p_count_new_cases 
FROM     T_CASES C, T_NOTES N, T_USERS TU 
WHERE    C.CASE_ID = N.CASE_ID 
AND      C.CASE_OWNER_USER_ID=TU.USER_ID 
AND      C.STATUS = 'OPEN' 
AND      N.CREATED_BY != C.CASE_OWNER_USER_ID 
AND      N.MOD_BY != C.CASE_OWNER_USER_ID 
START WITH TU.USER_ID=p_emp_id CONNECT BY PRIOR TU.USER_ID=TU.REPORTS_TO; 

The tables t_cases has around 1591256 rows, t_notes table has 12257426 rows and t_users has 3155. 
user_id column is primary key in t_users table. 
When I executed the query only for the condition TU.user_id=p_emp_id, the query gets executed taking small amount of time. 
When we add the hierarchical part (START WITH TU.USER_ID=p_emp_id CONNECT BY PRIOR TU.USER_ID=TU.REPORTS_TO), the query takes a long time to execute(more than 45 minutes). 

I created indexes on case_owner_user_id and status column. But the execution time is not reduced.

I am giving the execution plan for the query. 
SQL> set autotrace traceonly explain;
SQL> SELECT   COUNT(C.CASE_ID) p_count_new_cases 
  2  FROM     T_CASES C, T_NOTES N, T_USERS TU 
  3  WHERE    C.CASE_ID = N.CASE_ID 
  4  AND      C.CASE_OWNER_USER_ID=TU.USER_ID 
  5  AND      C.STATUS = 'OPEN' 
  6  AND      N.CREATED_BY != C.CASE_OWNER_USER_ID 
  7  AND      N.MOD_BY != C.CASE_OWNER_USER_ID 
  8  START WITH TU.USER_ID='X656629' CONNECT BY PRIOR TU.USER_ID=TU.REPORTS_TO;

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=111475 Card=1 Byte
          s=56)

   1    0   SORT (AGGREGATE)
   2    1     FILTER
   3    2       CONNECT BY (WITH FILTERING)
   4    3         FILTER
   5    4           COUNT
   6    5             NESTED LOOPS (Cost=111475 Card=12175742 Bytes=68
          1841552)

   7    6               NESTED LOOPS (Cost=90 Card=1591221 Bytes=57283
          956)

   8    7                 TABLE ACCESS (FULL) OF 'T_USERS' (TABLE) (Co
          st=26 Card=3153 Bytes=47295)

   9    7                 TABLE ACCESS (BY INDEX ROWID) OF 'T_CASES' (
          TABLE) (Cost=1 Card=505 Bytes=10605)

  10    9                   INDEX (RANGE SCAN) OF 'IDX_CASE_OWNER_USER
          _ID' (INDEX) (Cost=1 Card=2364)

  11    6               TABLE ACCESS (BY INDEX ROWID) OF 'T_NOTES' (TA
          BLE) (Cost=1 Card=8 Bytes=160)

  12   11                 INDEX (RANGE SCAN) OF 'NDXT_NOTES_CASE_ID' (
          INDEX) (Cost=1 Card=58)

  13    3         HASH JOIN
  14   13           CONNECT BY PUMP
  15   13           COUNT
  16   15             NESTED LOOPS (Cost=111475 Card=12175742 Bytes=68
          1841552)

  17   16               NESTED LOOPS (Cost=90 Card=1591221 Bytes=57283
          956)

  18   17                 TABLE ACCESS (FULL) OF 'T_USERS' (TABLE) (Co
          st=26 Card=3153 Bytes=47295)

  19   17                 TABLE ACCESS (BY INDEX ROWID) OF 'T_CASES' (
          TABLE) (Cost=1 Card=505 Bytes=10605)

  20   19                   INDEX (RANGE SCAN) OF 'IDX_CASE_OWNER_USER
          _ID' (INDEX) (Cost=1 Card=2364)

  21   16               TABLE ACCESS (BY INDEX ROWID) OF 'T_NOTES' (TA
          BLE) (Cost=1 Card=8 Bytes=160)

  22   21                 INDEX (RANGE SCAN) OF 'NDXT_NOTES_CASE_ID' (
          INDEX) (Cost=1 Card=58)

  23    3         COUNT
  24   23           NESTED LOOPS (Cost=111475 Card=12175742 Bytes=6818
          41552)

  25   24             NESTED LOOPS (Cost=90 Card=1591221 Bytes=5728395
          6)

  26   25               TABLE ACCESS (FULL) OF 'T_USERS' (TABLE) (Cost
          =26 Card=3153 Bytes=47295)

  27   25               TABLE ACCESS (BY INDEX ROWID) OF 'T_CASES' (TA
          BLE) (Cost=1 Card=505 Bytes=10605)

  28   27                 INDEX (RANGE SCAN) OF 'IDX_CASE_OWNER_USER_I
          D' (INDEX) (Cost=1 Card=2364)

  29   24             TABLE ACCESS (BY INDEX ROWID) OF 'T_NOTES' (TABL
          E) (Cost=1 Card=8 Bytes=160)

  30   29               INDEX (RANGE SCAN) OF 'NDXT_NOTES_CASE_ID' (IN
          DEX) (Cost=1 Card=58)

I could not give you tkprof report as I dont have access to that. 

Could you please tell me how to tune these queries?
I am using oracle 10.2.0.3.0 in windows.

SQL> SELECT   COUNT(C.CASE_ID) p_count_new_cases 
  2  FROM     T_CASES C, T_NOTES N, T_USERS TU 
  3  WHERE    C.CASE_ID = N.CASE_ID 
  4  AND      C.CASE_OWNER_USER_ID=TU.USER_ID 
  5  AND      C.STATUS = 'OPEN' 
  6  AND      N.CREATED_BY != C.CASE_OWNER_USER_ID 
  7  AND      N.MOD_BY != C.CASE_OWNER_USER_ID 
  8  START WITH TU.USER_ID='X656629' CONNECT BY PRIOR TU.USER_ID=TU.REPORTS_TO;

SQL> SELECT  *
  2  FROM    T_USERS TU 
  8  START WITH TU.USER_ID='X656629' CONNECT BY PRIOR TU.USER_ID=TU.REPORTS_TO;

Hi Tom,

I have three tables A<-- b -->C where b is mapping table.

I need to write hierarchical query that would return all rows from "A" where "A.id" has a match in "b.a_id".
Could you please show some primitive example using this condition if it's even possible?

Sorry for formatting couldn't get rid of this new line character in my text.

Thank you in advance.

hi Tom,

CREATE TABLE my_table (node VARCHAR2(1), linked_to VARCHAR2(1));

INSERT INTO my_table VALUES ('A','B');
INSERT INTO my_table VALUES ('B','A');
INSERT INTO my_table VALUES ('B','C');
INSERT INTO my_table VALUES ('C','B');

SELECT level,node,linked_to,SYS_CONNECT_BY_PATH(node,'/')||'/'||linked_to as PATH
FROM my_table
START WITH node = 'A'
CONNECT BY NOCYCLE node = PRIOR linked_to;


LEVEL N L PATH
--------- - - --------------------------------
1 A B /A/B
2 B A /A/B/A
2 B C /A/B/C

My question is: why my last inserted row (from C to B) is not selected? If this is how NOCYCLE works, why the second inserted row is selected?

You can see path like: /A/B/A

Node B is also linked back from C, why don't we see: /A/B/C/B ?

Thank you.

Dear Tom, I know you are very busy, but could you please take a look at the question above? Thank you!

start with A.

a connects to b
   b connects to A and C

ops$tkyte%ORA11GR1> insert into my_table values ( 'x', 'A' );

ops$tkyte%ORA11GR1> SELECT level,node,linked_to,SYS_CONNECT_BY_PATH(node,'/')||'/'||linked_to as PATH
  2    FROM my_table
  3   START WITH node = 'x'
  4   CONNECT BY NOCYCLE node = PRIOR linked_to;

     LEVEL N L PATH
---------- - - ----------------------------------------
         1 x A /x/A
         2 A B /x/A/B
         3 B C /x/A/B/C

x connects to A
  A connects to B
    B connects to C

hi Tom,
I have chart_of_account table, with items
(account_no,
account_name,
account_parent,
account_level, -- 1= main, 2= leave
balance)

balance is only real for leaves, but for main levels is accumulative,
So, when I make a tree, as follow:
account_no account_parent account_level balance
---------- -------------- ------------ -------
1000 null 1
1100 1000 1
1110 1100 1
1111 1110 2 50
1200 1000 1
1210 1200 1
1211 1210 2 70
1300 1000 1
1310 1300 1
1311 1310 2 90
I want the output to be as follow:
account_no account_parent account_level balance
---------- -------------- ------------ -------
1000 null 1 210
1100 1000 1 50
1110 1100 1 50
1111 1110 2 50
1200 1000 1 70
1210 1200 1 70
1211 1210 2 70
1300 1000 1 90
1310 1300 1 90
1311 1310 2 90

thank you.

ok. Mr. Tom, 
I have a table as follow:

SQL> create table chart_of_accounts 
  2  (account_no number(4), 
  3  account_name varchar2(50), 
  4  account_parent number(4), 
  5  account_level number(1),  -- 1= main, 2= leave 
  6  balance number(10,2) 
  7  )
  8  ;

Table created.

SQL> insert into chart_of_accounts values(1000, 'Assets', null, 1, null);

1 row created.

SQL> insert into chart_of_accounts values(1100, 'Fixed Assets', 1000, 1, null);

1 row created.

SQL> insert into chart_of_accounts values(1110, 'Furniture', 1100, 1, null);

1 row created.

SQL> insert into chart_of_accounts values(1111, 'Office Table', 1110, 2, 50);

1 row created.

SQL> insert into chart_of_accounts values(1200, 'Current Assets', 1000, 1, null);

1 row created.

SQL> insert into chart_of_accounts values(1210, 'Banks', 1200, 1, null);

1 row created.

SQL> insert into chart_of_accounts values(1211, 'Housing Bank', 1210, 2, 2560.73);

1 row created.

SQL> insert into chart_of_accounts values(1300, 'Cooperatives', 1000, 1, null);

1 row created.

SQL> insert into chart_of_accounts values(1310, 'Mobile Cooperatives', 1300, 1, null);

1 row created.

SQL> insert into chart_of_accounts values(1311, 'N95', 1310, 2, 600.00 );

1 row created.

SQL> 


balance is only real for leaves, but for main levels is accumulative, 
So, when I make a tree, as follow:

SQL> select * from chart_of_accounts ;

ACCOUNT_NO ACCOUNT_NAME                                       ACCOUNT_PARENT ACCOUNT_LEVEL    BALANCE
---------- -------------------------------------------------- -------------- ------------- ---------
      1000 Assets                                                                        1
      1100 Fixed Assets                                                 1000             1
      1110 Furniture                                                    1100             1
      1111 Office Table                                                 1110             2         50
      1200 Current Assets                                               1000             1
      1210 Banks                                                        1200             1
      1211 Housing Bank                                                 1210             2    2560.73
      1300 Cooperatives                                                 1000             1
      1310 Mobile Cooperatives                                          1300             1
      1311 N95                                                          1310             2        600

10 rows selected.

SQL> 

I want the output to be as follow:
ACCOUNT_NO ACCOUNT_NAME                                       ACCOUNT_PARENT ACCOUNT_LEVEL    BALANCE
---------- -------------------------------------------------- -------------- ------------- ---------
      1000 Assets                                                                        1    3210.73
      1100 Fixed Assets                                                 1000             1         50
      1110 Furniture                                                    1100             1         50
      1111 Office Table                                                 1110             2         50
      1200 Current Assets                                               1000             1    2560.73
      1210 Banks                                                        1200             1    2560.73
      1211 Housing Bank                                                 1210             2    2560.73
      1300 Cooperatives                                                 1000             1        600
      1310 Mobile Cooperatives                                          1300             1        600
      1311 N95                                                          1310             2        600


thank you.

ops$tkyte%ORA10GR2> select rpad('*',2*level,'*') || account_name nm, account_no, balance,
  2        (select sum(balance) from chart_of_accounts c2 start with c2.rowid = c1.rowid connect by prior account_no = account_parent ) bal
  3    from chart_of_accounts c1
  4   start with account_parent is null
  5  connect by prior account_no = account_parent
  6  /

NM                                       ACCOUNT_NO    BALANCE        BAL
---------------------------------------- ---------- ---------- ----------
**Assets                                       1000               3210.73
****Fixed Assets                               1100                    50
******Furniture                                1110                    50
********Office Table                           1111         50         50
****Current Assets                             1200               2560.73
******Banks                                    1210               2560.73
********Housing Bank                           1211    2560.73    2560.73
****Cooperatives                               1300                   600
******Mobile Cooperatives                      1310                   600
********N95                                    1311        600        600

10 rows selected.

Oh, Mr. Tom,
you are a great man.

thank you very much.

but, can you emplain me how you think with this problem.
i.e trace the sql statement.

so thanks.

ops$tkyte%ORA10GR2> select rpad('*',2*level,'*') || account_name nm, account_no, balance,
  2        <b> (select sum(balance) from chart_of_accounts c2 start with c2.rowid = c1.rowid connect by 
prior account_no = account_parent ) bal </b>
  3    from chart_of_accounts c1
  4   start with account_parent is null
  5  connect by prior account_no = account_parent
  6  /

Hi Tom,
I was trying to write a SQL which can give me the Main or the parent table.
If the input to the table is X has Foreign Key linked to Y and Y has a foreign key linked to Z then tha SQL should give me all the details
x(col1)->y(col1)
y(col1) -> z(col1)

The SQL might make clear what i am trying to achieve.
select DISTINCT a.table_name tab_name,c.column_name col_name ,c.table_name tab_name1,d.column_name col_name1
from
(select r_constraint_name,constraint_type,constraint_name,table_name from all_constraints) A,
(select r_constraint_name,constraint_type,constraint_name,table_name from all_constraints) b,
(select * from all_cons_columns) c,
(select * from all_cons_columns) d
where a.r_constraint_name=b.constraint_name and
a.constraint_type='R' and a.r_constraint_name=c.constraint_name
and a.constraint_name=d.constraint_name
START WITH a.table_name='X'
CONNECT BY PRIOR c.table_name=a.table_name

Your comments would be helpful and appreciated.

Thanks & Regards,
Bhushan Salgar

ops$tkyte%ORA10GR2> create table p
  2  ( a int, b int,
  3    constraint p_pk primary key(a,b)
  4  );

Table created.

ops$tkyte%ORA10GR2> create table c1
  2  ( x int, y int, z int,
  3    constraint c1_fk_p foreign key(x,y) references p(a,b),
  4    constraint c1_pk primary key(x,y,z)
  5  );

Table created.

ops$tkyte%ORA10GR2> create table c2
  2  ( x int, y int, z int,
  3    constraint c2_fk_c1 foreign key(x,y,z) references c1(x,y,z),
  4    constraint c2_pk primary key(x,y,z)
  5  );

Table created.

ops$tkyte%ORA10GR2> create table c3
  2  ( x int, y int, z int,
  3    constraint c3_fk_c2 foreign key(x,y,z) references c2(x,y,z),
  4    constraint c3_pk primary key(x,y,z),
  5    constraint c3_fk_p foreign key(x,y) references p(a,b),
  6    constraint c3_unique unique (x,y)
  7  );

Table created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> alter table p add
  2  constraint p_fk_c3 foreign key(a,b) references c3(x,y);

Table altered.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> variable bv varchar2(30)
ops$tkyte%ORA10GR2> exec :bv := 'P'

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> with data
  2  as
  3  (select a.table_name child, a.constraint_name child_cons,
  4          b.table_name parent, b.constraint_name parent_cons
  5     from user_constraints a, user_constraints b
  6    where a.constraint_type = 'R'
  7      and a.r_constraint_name = b.constraint_name
  8  )
  9  select rpad('*',2*level,'*') || child tree, data.* ,
 10         (select rtrim(
 11                     max(decode(position,1,column_name||',')) ||
 12                 max(decode(position,2,column_name||',')) ||
 13                 max(decode(position,3,column_name||',')) ||
 14                 max(decode(position,4,column_name||',')) ||
 15                 max(decode(position,5,column_name)) ||
 16                             max(decode(position,6,'...')), ',' )
 17            from user_cons_columns
 18                   where constraint_name = child_cons ) child_fkey,
 19         (select rtrim(
 20                     max(decode(position,1,column_name||',')) ||
 21                 max(decode(position,2,column_name||',')) ||
 22                 max(decode(position,3,column_name||',')) ||
 23                 max(decode(position,4,column_name||',')) ||
 24                 max(decode(position,5,column_name)) ||
 25                             max(decode(position,6,'...')), ',' )
 26            from user_cons_columns
 27                   where constraint_name = parent_cons ) parent_key
 28    from data
 29   start with child = :bv
 30  connect by NOCYCLE prior parent = child
 31  /

TREE                 PAR CHILD_CONS PARENT_CON CHILD PAREN
-------------------- --- ---------- ---------- ----- -----
**P                  C3  P_FK_C3    C3_UNIQUE  A,B   X,Y
****C3               P   C3_FK_P    P_PK       X,Y   A,B
****C3               C2  C3_FK_C2   C2_PK      X,Y,Z X,Y,Z
******C2             C1  C2_FK_C1   C1_PK      X,Y,Z X,Y,Z
********C1           P   C1_FK_P    P_PK       X,Y   A,B

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> exec :bv := 'C1'

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> /

TREE                 PAR CHILD_CONS PARENT_CON CHILD PAREN
-------------------- --- ---------- ---------- ----- -----
**C1                 P   C1_FK_P    P_PK       X,Y   A,B
****P                C3  P_FK_C3    C3_UNIQUE  A,B   X,Y
******C3             C2  C3_FK_C2   C2_PK      X,Y,Z X,Y,Z
********C2           C1  C2_FK_C1   C1_PK      X,Y,Z X,Y,Z

ops$tkyte%ORA10GR2> exec :bv := 'C2'

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> /

TREE                 PAR CHILD_CONS PARENT_CON CHILD PAREN
-------------------- --- ---------- ---------- ----- -----
**C2                 C1  C2_FK_C1   C1_PK      X,Y,Z X,Y,Z
****C1               P   C1_FK_P    P_PK       X,Y   A,B
******P              C3  P_FK_C3    C3_UNIQUE  A,B   X,Y
********C3           C2  C3_FK_C2   C2_PK      X,Y,Z X,Y,Z

ops$tkyte%ORA10GR2> exec :bv := 'C3'

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> /

TREE                 PAR CHILD_CONS PARENT_CON CHILD PAREN
-------------------- --- ---------- ---------- ----- -----
**C3                 P   C3_FK_P    P_PK       X,Y   A,B
****P                C3  P_FK_C3    C3_UNIQUE  A,B   X,Y
******C3             C2  C3_FK_C2   C2_PK      X,Y,Z X,Y,Z
********C2           C1  C2_FK_C1   C1_PK      X,Y,Z X,Y,Z
**C3                 C2  C3_FK_C2   C2_PK      X,Y,Z X,Y,Z
****C2               C1  C2_FK_C1   C1_PK      X,Y,Z X,Y,Z
******C1             P   C1_FK_P    P_PK       X,Y   A,B
********P            C3  P_FK_C3    C3_UNIQUE  A,B   X,Y

8 rows selected.

Hi Tom,
Above by Parent table i mean the root table, meaning we should be able to get even the parent of the parent and it might also branch to other tables if tehre are more than one FK on a Table.

Also,
Are you coming to Mumbai on 16-17 Dec?

Thanks & Regards,
Bhushan Salgar

Hi Tom,

I have a table that contains some rows with parent child relationships and other rows that are not related.  I wish to return all the rows, bringing back any children of the current row before processing the next row

eg

BUBBLE@scrappy > create table t (id number, master_id number);

Table created.

BUBBLE@scrappy > 
BUBBLE@scrappy > insert into t values (1, null);

1 row created.

BUBBLE@scrappy > insert into t values (2, null);

1 row created.

BUBBLE@scrappy > insert into t values (3, 1);

1 row created.

BUBBLE@scrappy > insert into t values (4, 3);

1 row created.

BUBBLE@scrappy > insert into t values (5, 4);

1 row created.

BUBBLE@scrappy > insert into t values (6, null);

1 row created.

BUBBLE@scrappy > insert into t values (7, null);

1 row created.

BUBBLE@scrappy > insert into t values (8, 7);

1 row created.

BUBBLE@scrappy > insert into t values (9, 8);

1 row created.

BUBBLE@scrappy > insert into t values (10, 9);

1 row created.

BUBBLE@scrappy > 
BUBBLE@scrappy > select * from t;

        ID  MASTER_ID
---------- ----------
         1
         2
         3          1
         4          3
         5          4
         6
         7
         8          7
         9          8
        10          9

10 rows selected.


So i want to return the rows in the order: 

Id Level 
1 1 
3 2 
4 3 
5 4 
2 1 
6 1 
7 1 
8 2 
9 3 
10 4

But I'm having trouble doing this in a single SQL statement.  I've had to do it in a procedure.


BUBBLE@scrappy >   declare
  2      cursor curGetAllMessages is
  3      select connect_by_isleaf cbl
  4            ,id
  5            ,master_id
  6      from   t
  7      connect by prior id =  master_id
  8      order by id;
  9      
 10      cursor curGetChildRecs (pId number) is
 11      select level
 12            ,id
 13            ,master_id
 14      from   t
 15      connect by prior id =  master_id
 16      start with id = pId;
 17      
 18    begin
 19      for x in curGetAllMessages loop
 20        if x.cbl = 1 and x.master_id is null then 
 21          -- record is childless
 22          dbms_output.put_line('level=1, x='||x.id);
 23        elsif x.cbl = 0 AND x.master_id is null then
 24          -- record is parent so we need to display the child messages
 25          for y in curGetChildRecs(x.id) loop
 26            dbms_output.put_line('level='||y.level||', y='||y.id);
 27          end loop;
 28        end if;
 29      end loop;
 30    end; 
 31  /
level=1, y=1
level=2, y=3
level=3, y=4
level=4, y=5
level=1, x=2
level=1, x=6
level=1, y=7
level=2, y=8
level=3, y=9
level=4, y=10

PL/SQL procedure successfully completed.

BUBBLE@scrappy > 



Is there a simpler way to do this without looking up the table twice?  

Cheers

ops$tkyte%ORA10GR2> select rpad('*',2*level,'*') || '-' || id theid, id, master_id, level
  2    from t
  3  start with master_id is null
  4  connect by prior id = master_id
  5  order siblings by id
  6  /

THEID                        ID  MASTER_ID      LEVEL
-------------------- ---------- ---------- ----------
**-1                          1                     1
****-3                        3          1          2
******-4                      4          3          3
********-5                    5          4          4
**-2                          2                     1
**-6                          6                     1
**-7                          7                     1
****-8                        8          7          2
******-9                      9          8          3
********-10                  10          9          4

10 rows selected.

Many thanks, obvious when you see it

Hi Tom

Can you help with a Hierarchical query requirement? This case involves the hierarchy details being stored in a second table (the Children) of the Parent table. The case is People Skills / Competencies, whereby having One skill may mean you have lots of others too.

---------        -------------------
| Skill | ----<  | Skill_Hierarchy |
---------        -------------------

create table Skill (
  Skill_Id   number       not null,
  Skill_Name varchar2(40) not null,
  constraint Skill_Pk primary key (Skill_Id)
);

create table Skill_Hierarchy (
  Parent_Skill_Id    number not null,
  Child_Skill_Id     number not null,
  constraint Skill_Hierarchy_Pk primary key (Parent_Skill_Id, Child_Skill_Id)
); 
alter table Skill_Hierarchy
  add constraint Skill_Hierarchy_to_Skill_Fk1 foreign key (Parent_Skill_Id)
  references Skill(Skill_Id) enable;
alter table Skill_Hierarchy
  add constraint Skill_Hierarchy_to_Skill_Fk2 foreign key (Child_Skill_Id)
  references Skill(Skill_Id) enable;

insert into Skill values (1, 'Off-Road - L1');
insert into Skill values (2, 'Off-Road - L2');
insert into Skill values (3, 'Off-Road - L3');
insert into Skill values (11, 'Cooking');
insert into Skill values (12, 'Washing Up');

insert into Skill_Hierarchy values (1, 2);
insert into Skill_Hierarchy values (2, 3);
insert into Skill_Hierarchy values (1, 11);
insert into Skill_Hierarchy values (11, 12);

So
If you have 'Off-Road - L1' you also have:
{'Off-Road - L2', 'Off-Road - L3', 'Cooking', 'Washing Up'}

If you have 'Off-Road - L2' you also have:
{'Off-Road - L3'}

If you have 'Off-Road - L3' thats all you have.

If you have 'Cooking' you also have:
{'Washing Up'}

There is a circularity there as 'Washing up' is achieved by 'Off-Road - L1' or 'Cooking' independently (I'm testing - not sure if the Data Model is correct yet)

I want to collect the Child/Dependent skills up into a Pl/SQL collection (array) for each Parent skill so need to get something like the following;

Parent_Skill_Id Parent_Skill_Name Child_Skill_Id Child_Skill_Name
--------------- ----------------- -------------- ----------------
              1 Off-Road - L1                  2 Off-Road - L2
              1 Off-Road - L1                  3 Off-Road - L3
              1 Off-Road - L1                 11 Cooking
              1 Off-Road - L1                 12 Washing Up

              2 Off-Road - L2                  3 Off-Road - L3

              3 Off-Road - L3               Null Null

             11 Cooking                       12 Washing Up

             12 Washing Up                  Null Null

Still struggling with Connecy By :(

many thanks
Craig

ops$tkyte%ORA10GR2> select connect_by_root parent_skill_id pskill_id ,
  2         connect_by_root parent_skill_name pskill_name,
  3             child_skill_id,
  4             (select skill_name from skill where skill_id = X.CHILD_SKILL_ID) child_skill_name
  5    from ( select s.skill_name parent_skill_name, s.skill_id parent_skill_id, sh.child_skill_id
  6             from skill s, skill_hierarchy sh
  7            where s.skill_id = sh.parent_skill_id(+)
  8             ) X
  9  connect by prior child_skill_id = parent_skill_id and child_skill_id is not null
 10  order siblings by parent_skill_id
 11  /

 PSKILL_ID PSKILL_NAME     CHILD_SKILL_ID CHILD_SKILL_NAM
---------- --------------- -------------- ---------------
         1 Off-Road - L1                2 Off-Road - L2
         1 Off-Road - L1                3 Off-Road - L3
         1 Off-Road - L1               11 Cooking
         1 Off-Road - L1               12 Washing Up
         2 Off-Road - L2                3 Off-Road - L3
         3 Off-Road - L3
        11 Cooking                     12 Washing Up
        12 Washing Up

8 rows selected.

Tom,

I have an employee table and location table. Each employee has 3 locations with 3 contact numbers & fax numbers. How I can display with single select statement using analytics.

I am using something like this

select e.empno,e.ename,cursor(select l.phone1,l.fax from emp e1,location l where e.empno = l.empno),d.dname from
emp e,dept d where e.dno = d.dno;

pls. advice

ops$tkyte%ORA9IR2> create table p as select * from scott.emp;

Table created.

ops$tkyte%ORA9IR2> alter table p add constraint p_pk primary key(empno);

Table altered.

ops$tkyte%ORA9IR2>
ops$tkyte%ORA9IR2> create table c
  2  ( empno references p,
  3    seq   number,
  4    loc   varchar2(6),
  5    phone varchar2(8),
  6    primary key(empno,seq)
  7  )
  8  /

Table created.

ops$tkyte%ORA9IR2>
ops$tkyte%ORA9IR2> create sequence s;

Sequence created.

ops$tkyte%ORA9IR2>
ops$tkyte%ORA9IR2> insert into c
  2  (empno,seq,loc,phone)
  3  select empno, s.nextval, 'loc-' || rownum, 'phone-'||rownum
  4    from p,(select level from dual connect by level <= 3)
  5  /

42 rows created.

ops$tkyte%ORA9IR2>
ops$tkyte%ORA9IR2> select p.empno, p.ename,
  2         max( decode( c.rn, 1, c.loc ) ) loc1,
  3         max( decode( c.rn, 2, c.phone ) ) phone1,
  4         max( decode( c.rn, 1, c.loc ) ) loc2,
  5         max( decode( c.rn, 2, c.phone ) ) phone2,
  6         max( decode( c.rn, 1, c.loc ) ) loc3,
  7         max( decode( c.rn, 2, c.phone ) ) phone3
  8    from p, (select empno, loc, phone, row_number() over (partition by empno order by seq) rn
  9               from c) c
 10   where p.empno = c.empno
 11   group by p.empno, p.ename
 12  /

     EMPNO ENAME      LOC1   PHONE1   LOC2   PHONE2   LOC3   PHONE3
---------- ---------- ------ -------- ------ -------- ------ --------
      7900 JAMES      loc-12 phone-26 loc-12 phone-26 loc-12 phone-26
      7369 SMITH      loc-1  phone-15 loc-1  phone-15 loc-1  phone-15
      7499 ALLEN      loc-2  phone-16 loc-2  phone-16 loc-2  phone-16
      7521 WARD       loc-3  phone-17 loc-3  phone-17 loc-3  phone-17
      7566 JONES      loc-4  phone-18 loc-4  phone-18 loc-4  phone-18
      7654 MARTIN     loc-5  phone-19 loc-5  phone-19 loc-5  phone-19
      7698 BLAKE      loc-6  phone-20 loc-6  phone-20 loc-6  phone-20
      7782 CLARK      loc-7  phone-21 loc-7  phone-21 loc-7  phone-21
      7788 SCOTT      loc-8  phone-22 loc-8  phone-22 loc-8  phone-22
      7839 KING       loc-9  phone-23 loc-9  phone-23 loc-9  phone-23
      7844 TURNER     loc-10 phone-24 loc-10 phone-24 loc-10 phone-24
      7876 ADAMS      loc-11 phone-25 loc-11 phone-25 loc-11 phone-25
      7902 FORD       loc-13 phone-27 loc-13 phone-27 loc-13 phone-27
      7934 MILLER     loc-14 phone-28 loc-14 phone-28 loc-14 phone-28

14 rows selected.

Tom, did you perhaps mean :

max( decode( c.rn, 1, c.loc ) ) loc1,
max( decode( c.rn, 1, c.phone ) ) phone1,
max( decode( c.rn, 2, c.loc ) ) loc2,
max( decode( c.rn, 2, c.phone ) ) phone2,
max( decode( c.rn, 3, c.loc ) ) loc3,
max( decode( c.rn, 3, c.phone ) ) phone3
...

And welcome back from what I hope was an enjoyable vacation.

ops$tkyte%ORA10GR2> select p.empno, p.ename,
  2         max( decode( c.rn, 1, c.loc ) ) loc1,
  3         max( decode( c.rn, 1, c.phone ) ) phone1,
  4         max( decode( c.rn, 2, c.loc ) ) loc2,
  5         max( decode( c.rn, 2, c.phone ) ) phone2,
  6         max( decode( c.rn, 3, c.loc ) ) loc3,
  7         max( decode( c.rn, 3, c.phone ) ) phone3
  8    from p, (select empno, loc, phone, row_number() over (partition by empno order by seq) rn
  9               from c) c
 10   where p.empno = c.empno
 11   group by p.empno, p.ename
 12  /

     EMPNO ENAME      LOC1   PHONE1   LOC2   PHONE2   LOC3   PHONE3
---------- ---------- ------ -------- ------ -------- ------ --------
      7521 WARD       loc-3  phone-3  loc-17 phone-17 loc-31 phone-31
      7566 JONES      loc-4  phone-4  loc-18 phone-18 loc-32 phone-32
      7844 TURNER     loc-10 phone-10 loc-24 phone-24 loc-38 phone-38
      7876 ADAMS      loc-11 phone-11 loc-25 phone-25 loc-39 phone-39
      7499 ALLEN      loc-2  phone-2  loc-16 phone-16 loc-30 phone-30
      7369 SMITH      loc-1  phone-1  loc-15 phone-15 loc-29 phone-29
      7782 CLARK      loc-7  phone-7  loc-21 phone-21 loc-35 phone-35
      7839 KING       loc-9  phone-9  loc-23 phone-23 loc-37 phone-37
      7698 BLAKE      loc-6  phone-6  loc-20 phone-20 loc-34 phone-34
      7900 JAMES      loc-12 phone-12 loc-26 phone-26 loc-40 phone-40
      7902 FORD       loc-13 phone-13 loc-27 phone-27 loc-41 phone-41
      7788 SCOTT      loc-8  phone-8  loc-22 phone-22 loc-36 phone-36
      7654 MARTIN     loc-5  phone-5  loc-19 phone-19 loc-33 phone-33
      7934 MILLER     loc-14 phone-14 loc-28 phone-28 loc-42 phone-42

14 rows selected.

Dear Tom,

good day to you, I am using hierarchical query in below scenario and I have few queries related to the same if you can please help me on this. We are on 10.2.0.3.0 windows box. I felt that this thread is on hierarchical queries so I have posted here and not raised a new query.

1) if you can please help me in understanding the plan when hierarchical queries are used, if you have already done so in some article/post kindly point me to the same.

2) I am confussed why there's full scan of branchtest table twice in the plan, Full scan of the branchtest table takes place when the account table below is IOT but when it's heap organized then branchtest table is scanned once.

Test case
---------
-- if the account is moved to another branch then the brn_chng and accno_chng will be populated and that record will become the child record

create table account(brn_number varchar2(6) not null, account_no varchar2(8) not null,acc_closed varchar2(1), acc_type varchar2(10),acc_subtype varchar2(10),brn_chng varchar2(6),accno_chng varchar2(8),
primary key (brn_number,account_no)
)organization index

;

-- if the branch is moved then the brn_moved will be populated and record with that value in brn_number will become child record

create table branchtest(brn_number varchar2(6), brn_moved varchar2(6));

-- Create 10 branch records

insert into branchtest select to_char(rownum,'fm000000') brn_number,to_char(rownum,'fm000000') brn_moved from dual connect by level <= 10;

commit;

-- create 100k accounts

insert into account select to_char(decode(mod(rownum,10),0,10,mod(rownum,10)),'fm000000') brn_number,
to_char(rownum,'fm00000000') account_no,decode(mod(rownum,2),0,'Y','N') acc_closed,decode(mod(rownum,4),0,'0000',1,'1111',2,'2222',3,'3333')acc_type,
decode(mod(rownum,4),0,'0000',1,'1111',2,'2222',3,'3333')acc_subtype,null brn_chng,null accno_chng from dual connect by level <= 100000;

commit;

exec dbms_stats.gather_table_stats(user,'BRANCHTEST',estimate_percent => null);

exec dbms_stats.gather_table_stats(user,'ACCOUNT',estimate_percent => null,cascade => true);

-- Find one account for query

select brn_number,account_no from account where account_no = '00050000'

-- query the account table
-- Set autot traceonly

-- if above query returns brn_number to be '000010', for now i have popluated brn_moved with same values as brn_number

select  * from account start with account_no = '00005000' 
and brn_number = ( select brn_number from branchtest where brn_moved = '000010') connect by prior brn_chng = brn_number and prior accno_chng = account_no;

-- output when account table is IOT

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=3 Card=1 Bytes=28)
   1    0   CONNECT BY (WITH FILTERING)
   2    1     INDEX (UNIQUE SCAN) OF 'SYS_IOT_TOP_70752' (INDEX (UNIQU
          E)) (Cost=2 Card=1 Bytes=28)

   3    2       TABLE ACCESS (FULL) OF 'BRANCHTEST' (TABLE) (Cost=3 Ca
          rd=1 Bytes=10)

   4    2       TABLE ACCESS (FULL) OF 'BRANCHTEST' (TABLE) (Cost=3 Ca
          rd=1 Bytes=10)

   5    1     NESTED LOOPS
   6    5       BUFFER (SORT)
   7    6         CONNECT BY PUMP
   8    5       INDEX (UNIQUE SCAN) OF 'SYS_IOT_TOP_70752' (INDEX (UNI
          QUE)) (Cost=2 Card=1 Bytes=28)

   9    1     INDEX (FULL SCAN) OF 'SYS_IOT_TOP_70752' (INDEX (UNIQUE)
          ) (Cost=2 Card=1 Bytes=28)

-- output when account table is heap organized

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=3 Card=1 Bytes=28)
   1    0   CONNECT BY (WITH FILTERING)
   2    1     TABLE ACCESS (BY INDEX ROWID) OF 'ACCOUNT' (TABLE)
   3    2       INDEX (UNIQUE SCAN) OF 'SYS_C0012652' (INDEX (UNIQUE))
           (Cost=2 Card=1 Bytes=16)

   4    3         TABLE ACCESS (FULL) OF 'BRANCHTEST' (TABLE) (Cost=3
          Card=1 Bytes=14)

   5    1     NESTED LOOPS
   6    5       BUFFER (SORT)
   7    6         CONNECT BY PUMP
   8    5       TABLE ACCESS (BY INDEX ROWID) OF 'ACCOUNT' (TABLE) (Co
          st=3 Card=1 Bytes=28)

   9    8         INDEX (UNIQUE SCAN) OF 'SYS_C0012652' (INDEX (UNIQUE
          )) (Cost=2 Card=1)

  10    1     TABLE ACCESS (FULL) OF 'ACCOUNT' (TABLE) (Cost=3 Card=1
          Bytes=28)

3) below is the output (chopped after a-time) generated with gather_plan_statistics hint when account table is IOT, if you can please
help me in understanding predicate information
a) for step 2 there's no value for brn_number
b) step 8 we have brn_number = prior null

-----------------------------------------------------------------------------------------------
| Id  | Operation                 | Name              | Starts | E-Rows | A-Rows |   A-Time   |
-----------------------------------------------------------------------------------------------
|*  1 |  CONNECT BY WITH FILTERING|                   |      1 |        |      1 |00:00:00.02 |
|*  2 |   INDEX UNIQUE SCAN       | SYS_IOT_TOP_70760 |      1 |      1 |      1 |00:00:00.02 |
|*  3 |    TABLE ACCESS FULL      | BRANCHTEST        |      1 |      1 |      1 |00:00:00.01 |
|*  4 |    TABLE ACCESS FULL      | BRANCHTEST        |      1 |      1 |      1 |00:00:00.02 |
|   5 |   NESTED LOOPS            |                   |      1 |        |      0 |00:00:00.01 |
|   6 |    BUFFER SORT            |                   |      1 |        |      1 |00:00:00.01 |
|   7 |     CONNECT BY PUMP       |                   |      1 |        |      1 |00:00:00.01 |
|*  8 |    INDEX UNIQUE SCAN      | SYS_IOT_TOP_70760 |      1 |      1 |      0 |00:00:00.01 |
|   9 |   INDEX FULL SCAN         | SYS_IOT_TOP_70760 |      0 |      1 |      0 |00:00:00.01 |
-----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("BRN_NUMBER"=PRIOR NULL AND "ACCOUNT_NO"=PRIOR NULL)
   2 - access("BRN_NUMBER"= AND "ACCOUNT_NO"='00005000')
       filter(("ACCOUNT_NO"='00005000' AND "BRN_NUMBER"=))
   3 - filter("BRN_MOVED"='000010')
   4 - filter("BRN_MOVED"='000010')
   8 - access("BRN_NUMBER"=PRIOR NULL AND "ACCOUNT_NO"=PRIOR NULL)

4) tkprof shows branchtest fullscan thrice when account table is IOT but only once when account table is heap organzied.Also there's block in execute phase where as this is select any reasons for this.

a) when account table is IOT

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.01          0          0          0           0
Execute      1      0.00       0.00          0          7          0           0
Fetch        2      0.00       0.00          0         10          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.00       0.02          0         17          0           1

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 52  

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  CONNECT BY WITH FILTERING (cr=17 pr=0 pw=0 time=335 us)
      1   INDEX UNIQUE SCAN SYS_IOT_TOP_47796 (cr=17 pr=0 pw=0 time=179 us)(object id 47797)
      1    TABLE ACCESS FULL BRANCHTEST (cr=7 pr=0 pw=0 time=48 us)
      1    TABLE ACCESS FULL BRANCHTEST (cr=7 pr=0 pw=0 time=71 us)
      0   NESTED LOOPS  (cr=0 pr=0 pw=0 time=85 us)
      1    BUFFER SORT (cr=0 pr=0 pw=0 time=56 us)
      1     CONNECT BY PUMP  (cr=0 pr=0 pw=0 time=28 us)
      0    INDEX UNIQUE SCAN SYS_IOT_TOP_47796 (cr=0 pr=0 pw=0 time=10 us)(object id 47797)
      0   INDEX FULL SCAN SYS_IOT_TOP_47796 (cr=0 pr=0 pw=0 time=0 us)(object id 47797)
      1   TABLE ACCESS FULL BRANCHTEST (cr=7 pr=0 pw=0 time=48 us)  

b) when account table is heap organized

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.01       0.00          0          0          0           0
Execute      1      0.00       0.00          0          7          0           0
Fetch        2      0.00       0.00          0          4          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.01       0.00          0         11          0           1

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 52  

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  CONNECT BY WITH FILTERING (cr=11 pr=0 pw=0 time=390 us)
      1   TABLE ACCESS BY INDEX ROWID ACCOUNT (cr=11 pr=0 pw=0 time=188 us)
      1    INDEX UNIQUE SCAN SYS_C004535 (cr=10 pr=0 pw=0 time=147 us)(object id 47800)
      1     TABLE ACCESS FULL BRANCHTEST (cr=7 pr=0 pw=0 time=89 us)
      0   NESTED LOOPS  (cr=0 pr=0 pw=0 time=106 us)
      1    BUFFER SORT (cr=0 pr=0 pw=0 time=66 us)
      1     CONNECT BY PUMP  (cr=0 pr=0 pw=0 time=32 us)
      0    TABLE ACCESS BY INDEX ROWID ACCOUNT (cr=0 pr=0 pw=0 time=22 us)
      0     INDEX UNIQUE SCAN SYS_C004535 (cr=0 pr=0 pw=0 time=9 us)(object id 47800)
      0   TABLE ACCESS FULL ACCOUNT (cr=0 pr=0 pw=0 time=0 us)
      0   TABLE ACCESS FULL BRANCHTEST (cr=0 pr=0 pw=0 time=0 us)

thanks for your help and time on this.

Regards,
jvs.

Dear Tom,
thanks for your help and time on this, I belive my queries were not clear but if you can please help me with query no 1 and 3, also with regards to cr=0 in tkprof below is the tkprof output when account table is IOT, in this case the cr is 7 for branchtest table thrice, lines marked with *, I am not able to understand why it is being scanned thrice am i missing something.

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.01          0          0          0           0
Execute      1      0.00       0.00          0          7          0           0
Fetch        2      0.00       0.00          0         10          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.00       0.02          0         17          0           1

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 52  

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  CONNECT BY WITH FILTERING (cr=17 pr=0 pw=0 time=335 us)
      1   INDEX UNIQUE SCAN SYS_IOT_TOP_47796 (cr=17 pr=0 pw=0 time=179 us)(object id 47797)
*      1    TABLE ACCESS FULL BRANCHTEST (cr=7 pr=0 pw=0 time=48 us) 
*     1    TABLE ACCESS FULL BRANCHTEST (cr=7 pr=0 pw=0 time=71 us)
      0   NESTED LOOPS  (cr=0 pr=0 pw=0 time=85 us)
      1    BUFFER SORT (cr=0 pr=0 pw=0 time=56 us)
      1     CONNECT BY PUMP  (cr=0 pr=0 pw=0 time=28 us)
      0    INDEX UNIQUE SCAN SYS_IOT_TOP_47796 (cr=0 pr=0 pw=0 time=10 us)(object id 47797)
      0   INDEX FULL SCAN SYS_IOT_TOP_47796 (cr=0 pr=0 pw=0 time=0 us)(object id 47797)
*      1   TABLE ACCESS FULL BRANCHTEST (cr=7 pr=0 pw=0 time=48 us)

thanks once again for your help and time on this.

Regards,
jvs

Hei Tom

Just wanted to ask for your comment on this.
I got a query that works fine using sys_connect_by_path in 8i after setting the "_new_connect_by_enabled=true".
But when using PreparedStatement(In Java) it returns a "ORA-1008" (and yes - I'm setting the values for the parameters). After spending some hours on the problem, I found that a rewriting of the query where i moved the bind-variables inside a GROUP BY and CONNECT BY that worked. Is this a expected behavior or could it be a bug. Here are the queries before and after the rewrite:

Before rewrite (Gives ORA-1008 with PreparedStatement and BIND):

SELECT k.kid, k.mdate,  k.bdate, k.valdate, k.refnr, k.recordtype, k.bid,
       k.messageid, k.arcref, k.amount, ko.valcode, ibt.amounttext,
       k.chk_time, k.chk_id, ka.accountalias, k.transcount, k.name
FROM mtext k, accountalias ka, account ko,
    (SELECT d.bid, d.mdate, substr(max(replace(sys_connect_by_path(amounttext,'chr(10)'),'chr(10)',' ')),2) amounttext
     FROM (SELECT t.bid, t.mdate, t.amounttext,
                  row_number() OVER (partition by t.bid, t.mdate order by t.bid, t.mdate) ROW#
           FROM mtext t,  mcontext k
           WHERE t.mdate = k.mdate
             AND t.bid = k.bid
             AND t.mdate >= to_date(?, 'dd.MM.yyyy') AND t.mdate <= to_date(?, 'dd.MM.yyyy')
             AND k.kid IN (?)
             AND k.amount>0) d
     START WITH ROW#=1
     CONNECT BY PRIOR bid=bid AND PRIOR mdato=mdato AND PRIOR row# = row# -1
     GROUP BY d.bid, d.mdato) ibt
WHERE ka.aid = 15097
  AND ka.kid = ko.kid
  AND k.kid = ko.kid
  AND ibt.mdate = k.mdate
  AND ibt.bid = k.bid
ORDER BY k.bdate;

AFTER (Works fine with BIND and PreparedStatement):

SELECT b.kid, b.mdate, b.bdate, b.valdate, b.refnr, b.recordtype, b.bid,
       b.messageid, b.arcref, b.amount, o.valcode, o.amounttext,
       b.chk_time, b.chk_id, o.accountalias, b.transcount, b.name
FROM mcontext b, 
    (SELECT d.bid, d.mdate, d.valcode, d.accountalias,
            substr(max(replace(sys_connect_by_path(amounttext,'chr(10)'),'chr(10)',' ')),2) amounttext
     FROM (SELECT t.bid, t.mdate, ko.valcode, ka.accountalias, t.amounttext,
                  row_number() OVER (partition by t.bid, t.mdate order by t.bid, t.mdate) ROW#
           FROM mtext t,  mcontext k, accountalias ka, account ko,
           WHERE t.mdate = k.mdate
             AND t.bid = k.bid
             AND ka.kid=ko.kid
             AND k.kid=ko.kid
             AND t.mdate >= to_date(?, 'dd.MM.yyyy') AND t.mdate <= to_date(?, 'dd.MM.yyyy')
             AND k.kid IN (?)
             AND ka.aid = ?
             AND k.amount>0) d
     START WITH ROW#=1
     CONNECT BY PRIOR bid=bid AND PRIOR mdato=mdato AND PRIOR row# = row# -1
     GROUP BY d.bid, d.mdato) o
WHERE o.mdate = b.mdate
  AND o.bid = b.bid
ORDER BY b.bdate;

Bug or feature?

Sorry. I see that the two queries I presented was not totaly correct. The second query(AFTER) is missing some columns in the group by, and the first query(BEFORE) is missing a bind variabel after the inline view. This last bind variable seems to be the problem (when its outside the inline view).

The BEFORE query should not be:

...
WHERE ka.aid = 15097

...but - instead:

...
WHERE ka.aid = ?

So then I guess i have to make a function taking an PK from table one as argument, looping through the related rows (not fix count, so cannot decode it) in table two and return concatenated text field. Or do you suggest other options? Have an idea that you might suggest using callable statement and generate entire resultset within a PL/SQL function/package. Considering that the samme application code should run towards both Oracle 8i and 9i, that would actually be a good ide. Then I could still use the hierarchical query in 9i and use a function in 8i. Pays off reading your books ... i guess :-)

I have a table of hierarchical items and their components. I need to get output from a query that produces the lowest-level components only, excluding intermediary components.

In the following example, item A is made up of 3 components. Two of those components are in turn made up of other components. The two components of A that are made up of components should be excluded. How to accomplish this?

SQL> create table items (item varchar2(2) not null, sub_item varchar2(2), seq_no number not null);

Table created.

SQL> insert into items values ('A','A1',10);

1 row created.

SQL> insert into items values ('A','A2',20);

1 row created.

SQL> insert into items values ('A','A3',30);

1 row created.

SQL> insert into items values ('A1','B1',10);

1 row created.

SQL> insert into items values ('A1','B2',20);

1 row created.

SQL> insert into items values ('A1','B3',30);

1 row created.

SQL> insert into items values ('B2','C1',10);

1 row created.

SQL> insert into items values ('B2','C2',20);

1 row created.

SQL> insert into items values ('A2','D1',10);

1 row created.

SQL> insert into items values ('A2','D2',20);

1 row created.

SQL> insert into items values ('A2','D3',30);

1 row created.

SQL> commit;

Commit complete.

SQL> select level lvl_no,i.*
  2  from items i
  3  start with item='A'
  4  connect by prior sub_item=item
  5  order siblings by seq_no;

    LVL_NO IT SU     SEQ_NO
---------- -- -- ----------
         1 A  A1         10 <== Exclude
         2 A1 B1         10
         2 A1 B2         20
         3 B2 C1         10
         3 B2 C2         20
         2 A1 B3         30
         1 A  A2         20 <== Exclude
         2 A2 D1         10
         2 A2 D2         20
         2 A2 D3         30
         1 A  A3         30

11 rows selected.

ops$tkyte%ORA10GR2> select rpad('*',2*level,'*')|| item itm, level lvl_no,i.*, connect_by_isleaf
  2  from items i
  3  where level > 1 OR connect_by_isleaf = 1
  4  start with item='A'
  5  connect by prior sub_item=item
  6  order siblings by seq_no;

ITM                 LVL_NO IT SU     SEQ_NO CONNECT_BY_ISLEAF
--------------- ---------- -- -- ---------- -----------------
****A1                   2 A1 B1         10                 1
****A1                   2 A1 B2         20                 0
******B2                 3 B2 C1         10                 1
******B2                 3 B2 C2         20                 1
****A1                   2 A1 B3         30                 1
****A2                   2 A2 D1         10                 1
****A2                   2 A2 D2         20                 1
****A2                   2 A2 D3         30                 1
**A                      1 A  A3         30                 1

9 rows selected.

RTFM rules the day! connect_by_isleaf solved the problem, and identified a row I missed on visual inspection to exclude:

    LVL_NO     ISLEAF IT SU     SEQ_NO
---------- ---------- -- -- ----------
         2          0 A1 B2         20

Is it valid when having a hierarchical in a subquery to reference a column as prior?
Like the query below:

SELECT sl.slot2containingcard, SL.SLOTID
FROM slot sl
WHERE sl.slotid IN
(SELECT cs.cardinslot2slot
FROM cardinslot cs
CONNECT BY NOCYCLE cs.cardinslot2slot = PRIOR sl.slotid
START WITH cs.cardinslot2card = :cardid

It is syntactically correct. But what does it semantically mean.

I think that I was not clear enough.
What I meant was

SELECT sl.slot2containingcard, SL.SLOTID
FROM slot sl
WHERE sl.slotid IN
(SELECT cs.cardinslot2slot
FROM cardinslot cs
CONNECT BY NOCYCLE cs.cardinslot2slot = PRIOR sl.slotid <<<
START WITH cs.cardinslot2card = :cardid

The column referenced after the PRIOR is a reference to a column from the table in the main select.

So to change your example:

select * from dept d
where d.deptno in
(select e.deptno from emp e
start with e.ename = :X
connect by prior e.some_column = d.some_other_column)

ops$tkyte%ORA11GR2> select *
  2    from scott.dept dept
  3   where deptno in (select emp.deptno from scott.emp where emp.deptno = dept.deptno)
  4  /

Hi Tom,
I have an interesting question for you based on a real life scenario.
I will try and explain the scenario with the scripts below:
##############
CREATE TABLE ISCT
(
ITEM_NO VARCHAR2(15 CHAR) NOT NULL,
ITEM_TYPE VARCHAR2(3 CHAR) NOT NULL,
ITEM_TYPE_SCO VARCHAR2(3 CHAR) NOT NULL,
ITEM_NO_SCO VARCHAR2(15 CHAR) NOT NULL,
ARTSAL_SEQ_PRIO NUMBER(3) NOT NULL,
ART_SCO_QTY NUMBER(6,3) NOT NULL,
ITEM_SEQ_PRIO NUMBER(3),
PCKL_SEQ_PRIO NUMBER(3),
REG_DATE DATE NOT NULL,
UPD_DATE DATE NOT NULL,
DELETE_DATE DATE,
USER_NO_CHG VARCHAR2(7 CHAR) NOT NULL
);
#########
CREATE TABLE ICONT
(
ITEM_TYPE VARCHAR2(3 CHAR) NOT NULL,
ITEM_NO VARCHAR2(15 CHAR) NOT NULL,
ITEM_TYPE_CHILD VARCHAR2(3 CHAR) NOT NULL,
ITEM_NO_CHILD VARCHAR2(15 CHAR) NOT NULL,
ITEM_QTY_CHILD NUMBER(5) NOT NULL,
REG_DATE DATE NOT NULL,
UPD_DATE DATE NOT NULL,
DELETE_DATE DATE,
II_DATE DATE NOT NULL,
IU_DATE DATE NOT NULL,
SORT_NO NUMBER(5)
);
--
#######
Now inserting rows:
#######
Insert into isct
(ITEM_NO, ITEM_TYPE, ITEM_TYPE_SCO, ITEM_NO_SCO, ARTSAL_SEQ_PRIO,
ART_SCO_QTY, ITEM_SEQ_PRIO, PCKL_SEQ_PRIO, REG_DATE, UPD_DATE,
DELETE_DATE, USER_NO_CHG)
Values
('15795', 'CCI', 'SCI', '00245905', 25,
1, NULL, NULL, TO_DATE('10/03/2009 15:40:01', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('10/03/2009 15:46:04', 'MM/DD/YYYY HH24:MI:SS'),
NULL, '1008920');
Insert into isct
(ITEM_NO, ITEM_TYPE, ITEM_TYPE_SCO, ITEM_NO_SCO, ARTSAL_SEQ_PRIO,
ART_SCO_QTY, ITEM_SEQ_PRIO, PCKL_SEQ_PRIO, REG_DATE, UPD_DATE,
DELETE_DATE, USER_NO_CHG)
Values
('00245781', 'SCI', 'SCI', '00245905', 1,
1, NULL, 1, TO_DATE('10/03/2009 15:46:04', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('10/03/2009 15:46:04', 'MM/DD/YYYY HH24:MI:SS'),
NULL, '1008920');
Insert into isct
(ITEM_NO, ITEM_TYPE, ITEM_TYPE_SCO, ITEM_NO_SCO, ARTSAL_SEQ_PRIO,
ART_SCO_QTY, ITEM_SEQ_PRIO, PCKL_SEQ_PRIO, REG_DATE, UPD_DATE,
DELETE_DATE, USER_NO_CHG)
Values
('80067553', 'ART', 'SCI', '00245905', 2,
2, NULL, 1, TO_DATE('10/03/2009 15:46:04', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('10/03/2009 15:46:04', 'MM/DD/YYYY HH24:MI:SS'),
NULL, '1008920');
Insert into isct
(ITEM_NO, ITEM_TYPE, ITEM_TYPE_SCO, ITEM_NO_SCO, ARTSAL_SEQ_PRIO,
ART_SCO_QTY, ITEM_SEQ_PRIO, PCKL_SEQ_PRIO, REG_DATE, UPD_DATE,
DELETE_DATE, USER_NO_CHG)
Values
('84178910', 'ART', 'SCI', '00245905', 3,
1, NULL, 1, TO_DATE('10/03/2009 15:46:04', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('10/03/2009 15:46:04', 'MM/DD/YYYY HH24:MI:SS'),
NULL, '1008920');
Insert into isct
(ITEM_NO, ITEM_TYPE, ITEM_TYPE_SCO, ITEM_NO_SCO, ARTSAL_SEQ_PRIO,
ART_SCO_QTY, ITEM_SEQ_PRIO, PCKL_SEQ_PRIO, REG_DATE, UPD_DATE,
DELETE_DATE, USER_NO_CHG)
Values
('70132375', 'ART', 'SCI', '00245905', 4,
8, NULL, 1, TO_DATE('10/03/2009 15:46:04', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('10/03/2009 15:46:04', 'MM/DD/YYYY HH24:MI:SS'),
NULL, '1008920');
Insert into isct
(ITEM_NO, ITEM_TYPE, ITEM_TYPE_SCO, ITEM_NO_SCO, ARTSAL_SEQ_PRIO,
ART_SCO_QTY, ITEM_SEQ_PRIO, PCKL_SEQ_PRIO, REG_DATE, UPD_DATE,
DELETE_DATE, USER_NO_CHG)
Values
('30067555', 'ART', 'SCI', '00245905', 5,
2, NULL, 1, TO_DATE('10/03/2009 15:46:04', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('10/03/2009 15:46:04', 'MM/DD/YYYY HH24:MI:SS'),
NULL, '1008920');
Insert into isct
(ITEM_NO, ITEM_TYPE, ITEM_TYPE_SCO, ITEM_NO_SCO, ARTSAL_SEQ_PRIO,
ART_SCO_QTY, ITEM_SEQ_PRIO, PCKL_SEQ_PRIO, REG_DATE, UPD_DATE,
DELETE_DATE, USER_NO_CHG)
Values
('50067559', 'ART', 'SCI', '00245905', 6,
5, NULL, 1, TO_DATE('10/03/2009 15:46:04', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('10/03/2009 15:46:04', 'MM/DD/YYYY HH24:MI:SS'),
NULL, '1008920');
Insert into isct
(ITEM_NO, ITEM_TYPE, ITEM_TYPE_SCO, ITEM_NO_SCO, ARTSAL_SEQ_PRIO,
ART_SCO_QTY, ITEM_SEQ_PRIO, PCKL_SEQ_PRIO, REG_DATE, UPD_DATE,
DELETE_DATE, USER_NO_CHG)
Values
('14278710', 'ART', 'SCI', '00245905', 7,
2, NULL, 1, TO_DATE('10/03/2009 15:46:04', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('10/03/2009 15:46:04', 'MM/DD/YYYY HH24:MI:SS'),
NULL, '1008920');
Insert into isct
(ITEM_NO, ITEM_TYPE, ITEM_TYPE_SCO, ITEM_NO_SCO, ARTSAL_SEQ_PRIO,
ART_SCO_QTY, ITEM_SEQ_PRIO, PCKL_SEQ_PRIO, REG_DATE, UPD_DATE,
DELETE_DATE, USER_NO_CHG)
Values
('00130092', 'ART', 'SCI', '00245905', 8,
1, NULL, 1, TO_DATE('10/03/2009 15:46:04', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('10/03/2009 15:46:04', 'MM/DD/YYYY HH24:MI:SS'),
NULL, '1008920');
Insert into isct
(ITEM_NO, ITEM_TYPE, ITEM_TYPE_SCO, ITEM_NO_SCO, ARTSAL_SEQ_PRIO,
ART_SCO_QTY, ITEM_SEQ_PRIO, PCKL_SEQ_PRIO, REG_DATE, UPD_DATE,
DELETE_DATE, USER_NO_CHG)
Values
('10110159', 'ART', 'SCI', '00245905', 9,
2, NULL, 1, TO_DATE('10/03/2009 15:46:04', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('10/03/2009 15:46:04', 'MM/DD/YYYY HH24:MI:SS'),
NULL, '1008920');
Insert into isct
(ITEM_NO, ITEM_TYPE, ITEM_TYPE_SCO, ITEM_NO_SCO, ARTSAL_SEQ_PRIO,
ART_SCO_QTY, ITEM_SEQ_PRIO, PCKL_SEQ_PRIO, REG_DATE, UPD_DATE,
DELETE_DATE, USER_NO_CHG)
Values
('30124693', 'ART', 'SCI', '00245905', 10,
4, NULL, 1, TO_DATE('10/03/2009 15:46:04', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('10/03/2009 15:46:04', 'MM/DD/YYYY HH24:MI:SS'),
NULL, '1008920');
Insert into isct
(ITEM_NO, ITEM_TYPE, ITEM_TYPE_SCO, ITEM_NO_SCO, ARTSAL_SEQ_PRIO,
ART_SCO_QTY, ITEM_SEQ_PRIO, PCKL_SEQ_PRIO, REG_DATE, UPD_DATE,
DELETE_DATE, USER_NO_CHG)
Values
('80130093', 'ART', 'SCI', '00245905', 11,
1, NULL, 1, TO_DATE('10/03/2009 15:46:04', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('10/03/2009 15:46:04', 'MM/DD/YYYY HH24:MI:SS'),
NULL, '1008920');
Insert into isct
(ITEM_NO, ITEM_TYPE, ITEM_TYPE_SCO, ITEM_NO_SCO, ARTSAL_SEQ_PRIO,
ART_SCO_QTY, ITEM_SEQ_PRIO, PCKL_SEQ_PRIO, REG_DATE, UPD_DATE,
DELETE_DATE, USER_NO_CHG)
Values
('90110155', 'ART', 'SCI', '00245905', 12,
2, NULL, 1, TO_DATE('10/03/2009 15:46:04', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('10/03/2009 15:46:04', 'MM/DD/YYYY HH24:MI:SS'),
NULL, '1008920');
Insert into isct
(ITEM_NO, ITEM_TYPE, ITEM_TYPE_SCO, ITEM_NO_SCO, ARTSAL_SEQ_PRIO,
ART_SCO_QTY, ITEM_SEQ_PRIO, PCKL_SEQ_PRIO, REG_DATE, UPD_DATE,
DELETE_DATE, USER_NO_CHG)
Values
('60035916', 'ART', 'SCI', '00245905', 13,
2, NULL, 1, TO_DATE('10/03/2009 15:46:04', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('10/03/2009 15:46:04', 'MM/DD/YYYY HH24:MI:SS'),
NULL, '1008920');
Insert into isct
(ITEM_NO, ITEM_TYPE, ITEM_TYPE_SCO, ITEM_NO_SCO, ARTSAL_SEQ_PRIO,
ART_SCO_QTY, ITEM_SEQ_PRIO, PCKL_SEQ_PRIO, REG_DATE, UPD_DATE,
DELETE_DATE, USER_NO_CHG)
Values
('54249810', 'ART', 'SCI', '00245905', 14,
2, NULL, 1, TO_DATE('10/03/2009 15:46:04', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('10/03/2009 15:46:04', 'MM/DD/YYYY HH24:MI:SS'),
NULL, '1008920');
Insert into isct
(ITEM_NO, ITEM_TYPE, ITEM_TYPE_SCO, ITEM_NO_SCO, ARTSAL_SEQ_PRIO,
ART_SCO_QTY, ITEM_SEQ_PRIO, PCKL_SEQ_PRIO, REG_DATE, UPD_DATE,
DELETE_DATE, USER_NO_CHG)
Values
('14180110', 'ART', 'SCI', '00245905', 15,
1, NULL, 1, TO_DATE('10/03/2009 15:46:04', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('10/03/2009 15:46:04', 'MM/DD/YYYY HH24:MI:SS'),
NULL, '1008920');
Insert into isct
(ITEM_NO, ITEM_TYPE, ITEM_TYPE_SCO, ITEM_NO_SCO, ARTSAL_SEQ_PRIO,
ART_SCO_QTY, ITEM_SEQ_PRIO, PCKL_SEQ_PRIO, REG_DATE, UPD_DATE,
DELETE_DATE, USER_NO_CHG)
Values
('97872010', 'ART', 'SCI', '00245905', 16,
1, NULL, 1, TO_DATE('10/03/2009 15:46:04', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('10/03/2009 15:46:04', 'MM/DD/YYYY HH24:MI:SS'),
NULL, '1008920');
Insert into isct
(ITEM_NO, ITEM_TYPE, ITEM_TYPE_SCO, ITEM_NO_SCO, ARTSAL_SEQ_PRIO,
ART_SCO_QTY, ITEM_SEQ_PRIO, PCKL_SEQ_PRIO, REG_DATE, UPD_DATE,
DELETE_DATE, USER_NO_CHG)
Values
('34180010', 'ART', 'SCI', '00245905', 17,
1, NULL, 1, TO_DATE('10/03/2009 15:46:04', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('10/03/2009 15:46:04', 'MM/DD/YYYY HH24:MI:SS'),
NULL, '1008920');
Insert into isct
(ITEM_NO, ITEM_TYPE, ITEM_TYPE_SCO, ITEM_NO_SCO, ARTSAL_SEQ_PRIO,
ART_SCO_QTY, ITEM_SEQ_PRIO, PCKL_SEQ_PRIO, REG_DATE, UPD_DATE,
DELETE_DATE, USER_NO_CHG)
Values
('57871710', 'ART', 'SCI', '00245905', 18,
1, NULL, 1, TO_DATE('10/03/2009 15:46:04', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('10/03/2009 15:46:04', 'MM/DD/YYYY HH24:MI:SS'),
NULL, '1008920');
Insert into isct
(ITEM_NO, ITEM_TYPE, ITEM_TYPE_SCO, ITEM_NO_SCO, ARTSAL_SEQ_PRIO,
ART_SCO_QTY, ITEM_SEQ_PRIO, PCKL_SEQ_PRIO, REG_DATE, UPD_DATE,
DELETE_DATE, USER_NO_CHG)
Values
('40067588', 'ART', 'SCI', '00245905', 19,
2, NULL, 1, TO_DATE('10/03/2009 15:46:04', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('10/03/2009 15:46:04', 'MM/DD/YYYY HH24:MI:SS'),
NULL, '1008920');
Insert into isct
(ITEM_NO, ITEM_TYPE, ITEM_TYPE_SCO, ITEM_NO_SCO, ARTSAL_SEQ_PRIO,
ART_SCO_QTY, ITEM_SEQ_PRIO, PCKL_SEQ_PRIO, REG_DATE, UPD_DATE,
DELETE_DATE, USER_NO_CHG)
Values
('00067590', 'ART', 'SCI', '00245905', 20,
1, NULL, 1, TO_DATE('10/03/2009 15:46:04', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('10/03/2009 15:46:04', 'MM/DD/YYYY HH24:MI:SS'),
NULL, '1008920');
Insert into isct
(ITEM_NO, ITEM_TYPE, ITEM_TYPE_SCO, ITEM_NO_SCO, ARTSAL_SEQ_PRIO,
ART_SCO_QTY, ITEM_SEQ_PRIO, PCKL_SEQ_PRIO, REG_DATE, UPD_DATE,
DELETE_DATE, USER_NO_CHG)
Values
('80067586', 'ART', 'SCI', '00245905', 21,
1, NULL, 1, TO_DATE('10/03/2009 15:46:04', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('10/03/2009 15:46:04', 'MM/DD/YYYY HH24:MI:SS'),
NULL, '1008920');
Insert into isct
(ITEM_NO, ITEM_TYPE, ITEM_TYPE_SCO, ITEM_NO_SCO, ARTSAL_SEQ_PRIO,
ART_SCO_QTY, ITEM_SEQ_PRIO, PCKL_SEQ_PRIO, REG_DATE, UPD_DATE,
DELETE_DATE, USER_NO_CHG)
Values
('20145196', 'ART', 'SCI', '00245905', 22,
2, NULL, 1, TO_DATE('10/03/2009 15:46:04', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('10/03/2009 15:46:04', 'MM/DD/YYYY HH24:MI:SS'),
NULL, '1008920');
Insert into isct
(ITEM_NO, ITEM_TYPE, ITEM_TYPE_SCO, ITEM_NO_SCO, ARTSAL_SEQ_PRIO,
ART_SCO_QTY, ITEM_SEQ_PRIO, PCKL_SEQ_PRIO, REG_DATE, UPD_DATE,
DELETE_DATE, USER_NO_CHG)
Values
('00067585', 'ART', 'SCI', '00245905', 23,
1, NULL, 1, TO_DATE('10/03/2009 15:46:04', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('10/03/2009 15:46:04', 'MM/DD/YYYY HH24:MI:SS'),
NULL, '1008920');
Insert into isct
(ITEM_NO, ITEM_TYPE, ITEM_TYPE_SCO, ITEM_NO_SCO, ARTSAL_SEQ_PRIO,
ART_SCO_QTY, ITEM_SEQ_PRIO, PCKL_SEQ_PRIO, REG_DATE, UPD_DATE,
DELETE_DATE, USER_NO_CHG)
Values
('80124087', 'ART', 'SCI', '00245905', 24,
1, NULL, 1, TO_DATE('10/03/2009 15:46:04', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('10/03/2009 15:46:04', 'MM/DD/YYYY HH24:MI:SS'),
NULL, '1008920');
Insert into isct
(ITEM_NO, ITEM_TYPE, ITEM_TYPE_SCO, ITEM_NO_SCO, ARTSAL_SEQ_PRIO,
ART_SCO_QTY, ITEM_SEQ_PRIO, PCKL_SEQ_PRIO, REG_DATE, UPD_DATE,
DELETE_DATE, USER_NO_CHG)
Values
('39809661', 'SPR', 'SCI', '00245781', 1,
1, 1, 1, TO_DATE('10/04/2009 15:24:22', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('10/04/2009 15:24:22', 'MM/DD/YYYY HH24:MI:SS'),
NULL, '1007193');
Insert into icont (ITEM_TYPE,ITEM_NO,ITEM_TYPE_CHILD,ITEM_NO_CHILD,ITEM_QTY_CHILD,REG_DATE,UPD_DATE,DELETE_DATE,II_DATE,IU_DATE,SORT_NO) values ('SPR','39809661','ART','44178610',1,to_timestamp('23-FEB-07','DD-MON-RR HH24.MI.SSXFF'),to_timestamp('23-FEB-07','DD-MON-RR HH24.MI.SSXFF'),null,to_timestamp('15-NOV-07','DD-MON-RR HH24.MI.SSXFF'),to_timestamp('20-DEC-07','DD-MON-RR HH24.MI.SSXFF'),2);
Insert into icont (ITEM_TYPE,ITEM_NO,ITEM_TYPE_CHILD,ITEM_NO_CHILD,ITEM_QTY_CHILD,REG_DATE,UPD_DATE,DELETE_DATE,II_DATE,IU_DATE,SORT_NO) values ('SPR','39809661','ART','70132375',1,to_timestamp('06-SEP-07','DD-MON-RR HH24.MI.SSXFF'),to_timestamp('06-SEP-07','DD-MON-RR HH24.MI.SSXFF'),null,to_timestamp('15-NOV-07','DD-MON-RR HH24.MI.SSXFF'),to_timestamp('20-DEC-07','DD-MON-RR HH24.MI.SSXFF'),4);
Insert into icont (ITEM_TYPE,ITEM_NO,ITEM_TYPE_CHILD,ITEM_NO_CHILD,ITEM_QTY_CHILD,REG_DATE,UPD_DATE,DELETE_DATE,II_DATE,IU_DATE,SORT_NO) values ('SPR','39809661','ART','74249710',1,to_timestamp('23-FEB-07','DD-MON-RR HH24.MI.SSXFF'),to_timestamp('23-FEB-07','DD-MON-RR HH24.MI.SSXFF'),null,to_timestamp('15-NOV-07','DD-MON-RR HH24.MI.SSXFF'),to_timestamp('20-DEC-07','DD-MON-RR HH24.MI.SSXFF'),3);
Insert into icont (ITEM_TYPE,ITEM_NO,ITEM_TYPE_CHILD,ITEM_NO_CHILD,ITEM_QTY_CHILD,REG_DATE,UPD_DATE,DELETE_DATE,II_DATE,IU_DATE,SORT_NO) values ('SPR','39809661','ART','80067553',1,to_timestamp('23-FEB-07','DD-MON-RR HH24.MI.SSXFF'),to_timestamp('23-FEB-07','DD-MON-RR HH24.MI.SSXFF'),null,to_timestamp('15-NOV-07','DD-MON-RR HH24.MI.SSXFF'),to_timestamp('20-DEC-07','DD-MON-RR HH24.MI.SSXFF'),1);
COMMIT;
##########################################
--------------------------------------------------------------------------------

Now if you create a view as below:
#############
create or replace view test_v
(ITEM_NO, ITEM_TYPE, ITEM_NO_SCO,ITEM_TYPE_SCO)
as
SELECT T.ITEM_NO, T.ITEM_TYPE, CONNECT_BY_ROOT T.ITEM_NO_SCO,T.ITEM_TYPE_SCO
FROM
(
SELECT NVL(C.ITEM_NO_CHILD, I.ITEM_NO) ITEM_NO, NVL(C.ITEM_TYPE_CHILD, I.ITEM_TYPE) ITEM_TYPE, I.ITEM_TYPE_SCO, I.ITEM_NO_SCO
FROM isct I,
icont C
WHERE I.ITEM_NO = C.ITEM_NO(+)
AND I.ITEM_TYPE = C.ITEM_TYPE(+)
) T
CONNECT BY PRIOR T.ITEM_NO = T.ITEM_NO_SCO AND PRIOR T.ITEM_TYPE = T.ITEM_TYPE_SCO;
----
##################

Then run the below query:
##########

select * from test_v where item_no_sco = '00245905'
###########
--
The output is got within a second. But the real scenario is that both table icont and isct have millions of rows. In that case even this small query of the view takes 10minutes.

How do I give the START WITH in the view to make it faster i.e as shown below.
even with millions of rows the output of this comes in seconds:
SELECT T.ITEM_NO, T.ITEM_TYPE, CONNECT_BY_ROOT T.ITEM_NO_SCO,T.ITEM_TYPE_SCO
FROM
(
SELECT NVL(C.ITEM_NO_CHILD, I.ITEM_NO) ITEM_NO, NVL(C.ITEM_TYPE_CHILD, I.ITEM_TYPE) ITEM_TYPE, I.ITEM_TYPE_SCO, I.ITEM_NO_SCO
FROM isct I,
icont C
WHERE I.ITEM_NO = C.ITEM_NO(+)
AND I.ITEM_TYPE = C.ITEM_TYPE(+)
) T
START WITH T.ITEM_NO_SCO = '00245905'
CONNECT BY PRIOR T.ITEM_NO = T.ITEM_NO_SCO AND PRIOR T.ITEM_TYPE = T.ITEM_TYPE_SCO;
########
---

I dont want to use a parametrized view or external variable as then I cannot join with another table for multiple values.

Hope the question is clear. Please excuse the extra columns as I took the script from production and I wanted to give a real life example.

Regards,
Vikram

I have around 180 tables in my schema for which I am performing data migration. I would like to load the parent tables first and then the child tables. What is the query that will walk through the schema and list the order of migration. Of course, some tables act as both parent and child tables.

I am looking for a hierarchical query to do this.

The purpose for the query i trying to churn out is:

If there is child/nested child record for the given ID, get the MAX timestamp for each child record. then get the MIN timestamp for the MAX timestamp of child record

BUT i am stuck at getting the child record.

The subquery below will go thru almost all the records in the table, which is something i want to avoid as the original table has alot of records. Is it possible to make it scan thru less records with the ultimate parent id provided?

p/s please forgive me if the create table and insert statement has error, as i don't have the privilege to test the statement in my company.

Query

SELECT A.WIPID
FROM (
    SELECT T.* ,
           SUBSTR(
               (SELECT MAX(TO_CHAR(LEVEL,'fm00000')||SYSID)
                  FROM TBLTRANS A2
                  WHERE A2.WIPID = 'AAAAAAAA01'
                     start with A2.SYSID = T.PARENTTXN
                   connect by prior A2.PARENTTXN = A2.SYSID and prior A2.PARENTTXN <> prior A2.SYSID), 6 )  ultimate_pid
      from TBLTRANS T
where T.activity = 'Merge' 
          ) A, TBLTRANS   B
    where b.SYSID = a.ultimate_pid
    and b.activity = 'Merge'

CREATE SQL

create table tblTrans
(SYSID    VARCHAR2(45),
TXNSEQUENCE  NUMBER(38),
ACTIVITY  VARCHAR2(40),
WIPID   VARCHAR2(40),
PARENTTXN  VARCHAR2(45),
TXNTIMESTAMP  CHAR(18),
INSTEP   VARCHAR2(51)
)
/

INSERT INTO TBLTRANS VALUES ('00000.00000.00001', 20000041, 'Split', 'AAAAAAAA01', '00000.00000.00000', '20110402 103348000', '')
/
INSERT INTO TBLTRANS VALUES ('00000.00000.00002', 20000042, 'Split', 'AAAAAAAA02', '00000.00000.00001', '20110402 103348000', '')
/
INSERT INTO TBLTRANS VALUES ('00000.00000.00003', 20000043, 'Merge', 'AAAAAAAA01', '00000.00000.00000', '20110404 141427000', '')
/
INSERT INTO TBLTRANS VALUES ('00000.00000.00004', 20000044, 'Merge', 'AAAAAAAA02', '00000.00000.00003', '20110404 141427000', '')
/
INSERT INTO TBLTRANS VALUES ('00000.00000.00005', 20000045, 'Merge', 'AAAAAAAA03', '00000.00000.00003', '20110404 141427000', '')
/
INSERT INTO TBLTRANS VALUES ('00000.00000.00006', 20000045, 'Merge', 'AAAAAAAA03', '00000.00000.00000', '20110404 101025000', '')
/
INSERT INTO TBLTRANS VALUES ('00000.00000.00007', 20000046, 'Merge', 'AAAAAAAA04', '00000.00000.00006', '20110404 101025000', '')
/
INSERT INTO TBLTRANS VALUES ('00000.00000.00008', 20000047, 'Merge', 'AAAAAAAA05', '00000.00000.00000', '20110405 015031000', '')
/
INSERT INTO TBLTRANS VALUES ('00000.00000.00009', 20000048, 'Merge', 'AAAAAAAA06', '00000.00000.00008', '20110405 015031000', '')
/
INSERT INTO TBLTRANS VALUES ('00000.00000.00010', 20000049, 'Out', 'AAAAAAAA01', '00000.00000.00000', '20110405 040000000', 'Step099')
/
INSERT INTO TBLTRANS VALUES ('00000.00000.00011', 20000050, 'Out', 'AAAAAAAA02', '00000.00000.00000', '20110405 050000000', 'Step099')
/
INSERT INTO TBLTRANS VALUES ('00000.00000.00012', 20000051, 'Out', 'AAAAAAAA03', '00000.00000.00000', '20110405 060000000', 'Step099')
/
INSERT INTO TBLTRANS VALUES ('00000.00000.00013', 20000052, 'Out', 'AAAAAAAA01', '00000.00000.00000', '20110405 070000000', 'Step100')
/
INSERT INTO TBLTRANS VALUES ('00000.00000.00014', 20000053, 'Out', 'AAAAAAAA02', '00000.00000.00000', '20110405 080000000', 'Step100')
/

To clarify, most of the records in the table have Merge activity. For the inner subquery(the FROM part), the records that doesn't fall under the ultimate parent id for WIPID = 'AAAAAAAA01' but have Merge activity will be returned as well with NULL as the ultimate parent id.
So, I have put in extra criteria as in the TO part to limit the set of data processed.

FROM

SELECT T.* , SUBSTR(
 (SELECT MAX(TO_CHAR(LEVEL,'fm00000')||SYSID)
 FROM TBLTRANS A2
 WHERE A2.WIPID = 'AAAAAAAA01'
 start with A2.SYSID = T.PARENTTXN
 connect by prior A2.PARENTTXN = A2.SYSID and prior A2.PARENTTXN <> prior 
A2.SYSID), 6 )  ultimate_pid
 from TBLTRANS T
where T.activity = 'Merge'

TO

SELECT T.* , SUBSTR(
 (SELECT MAX(TO_CHAR(LEVEL,'fm00000')||SYSID)
 FROM TBLTRANS A2
 WHERE A2.WIPID = 'AAAAAAAA01'
 start with A2.SYSID = T.PARENTTXN
 connect by prior A2.PARENTTXN = A2.SYSID and prior A2.PARENTTXN <> prior 
A2.SYSID), 6 )  ultimate_pid
 from TBLTRANS T
where T.activity = 'Merge'and T.WIPID LIKE 'AAAAAAAA%'

The question is if i do not have an extra column to indicate similarities, is there anything i can do to enhance the query?

e.g. : previous query if don't have WIPID LIKE 'AAAAAAA%' will return

SYSID WIPID ACTIVITY Ultimate_pid
-------------------------------------------
1 AAAAAAA01 Merge <null>
2 AAAAAAA02 Merge 2
19 BBBBBBB01 Merge <null>

ideal query will return

SYSID WIPID ACTIVITY Ultimate_pid
-------------------------------------------
1 AAAAAAA01 Merge <null>
2 AAAAAAA02 Merge 2


QUERY

SELECT A.WIPID
FROM ( <b>SELECT T.* , SUBSTR(
 (SELECT MAX(TO_CHAR(LEVEL,'fm00000')||SYSID)
 FROM TBLTRANS A2
 WHERE A2.WIPID = 'AAAAAAAA01'
 start with A2.SYSID = T.PARENTTXN
 connect by prior A2.PARENTTXN = A2.SYSID and prior A2.PARENTTXN <> prior 
A2.SYSID), 6 )  ultimate_pid
 from TBLTRANS T
where T.activity = 'Merge'</b> 
<i>and T.WIPID LIKE 'AAAAAAAA%'</i>
) A, TBLTRANS   B
where b.SYSID = a.ultimate_pid
and b.activity = 'Merge'

Assalam-O-Elikum

Friends I want to show my user access form in hiererichal tree this is my database design which is consist of 4 tables
set_system, set_modules, set_applications, set_forms, emp_access
my parent table is emp_access
and i want to show all the forms with respect to emp_access
if any field modules or forms field is null then its mean
user has access on all forms or modules
or when fields are not null then its mean
user has access only those forms or report where
forms or report define..

CREATE TABLE EMP_ACCESS
EMP_ID NUMBER,
SYS_ID NUMBER,
APP_ID NUMBER,
MOD_ID NUMBER,
FRM_ID NUMBER,
EMP_VIEW CHAR(1),
EMP_INS CHAR(1),
EMP_UP CHAR(1),
EMP_DEL CHAR(1);
INSERT INTO EMP_ACCCESS
(EMP_ID, SYS_ID, EMP_VIEW, EMP_INS, EMP_UP, EMP_DEL)
VALUES
(85, 1, Y, Y, Y, Y);

Create table set_sys
ID NUMBER NOT NULL,
NAME VARCHAR2(40);
INSERT INTO SET_SYS
VALUES
(1, ‘HUMAN RESOURCE MANAGEMENT SYSTEM’);

CREATE TABLE SET_APPLICATIONS
(ID NUMBER NOT NULL,
NAME VARCHAR2(20),
ABRV VARCHAR2(6),
SYS_ID NUMBER);
INSERT INTO SET_APPLICATIONS
VALUES
(4, ‘ADMINISTRATOR’, ‘ADMN’, 1);
INSERT INTO SET_APPLICATIONS
VALUES
(1, ‘PAYROLL’, ‘PAY’, 1);
INSERT INTO SET_APPLICATIONS
VALUES
(2, ‘PERSONNEL’, ‘PER’, 1);
INSERT INTO SET_APPLICATIONS
VALUES
(3, ‘TIMEOFFICE’, ‘TOF’, 1);
CREATE TABLE SET_MODULES
(ID NUMBER NOT NULL,
NAME VARCHAR2(40),
ABRV VARCHAR2(6),
APP_ID NUMBER);
INSERT INTO SET_MODULES
VALUES
(1, ‘FORM FOR PROCESS SALARY’, ‘SPR’, 1);
INSERT INTO SET_MODULES
VALUES
(2, ‘FORM FOR ALLOWANCE’, ‘ALL’, 1);
INSERT INTO SET_MODULES
VALUES
(4, ‘FORM FOR SET INCOME TAX’, ‘ITX’, 1);
INSERT INTO SET_MODULES
VALUES
(5,’ MANUAL EDITING’, MED’, 1);
INSERT INTO SET_MODULES
VALUES
(6, ‘CONVERT DATA TO EXCEL’, ‘CDE’, 1);
INSERT INTO SET_MODULES
VALUES
(13, ‘PERSONNEL MODULES’, ‘PER’, 2);
INSERT INTO SET_MODULES
VALUES
(14, ‘EMPLOYEE INCREMENT / PROMOTION’, ‘EIP’, 2);
INSERT INTO SET_MODULES
VALUES
(15, ‘HIERERICHAL TREE’, ‘HIR’, 2);
INSERT INTO SET_MODULES
VALUES
(16, ‘EXPORT TO EXCEL PER’, ‘ETE’, 2);
INSERT INTO SET_MODULES
VALUES
(17, ’ROSTER (LEAVE, SHIFTS, WEEK OFF)’,’ EMA’, 3);
INSERT INTO SET_MODULES
VALUES
(18, ‘EMPLOYEES MONTHLY ATTENDANCE’, ‘EMS’, 3);
INSERT INTO SET_MODULES
VALUES
(20, ‘REGULAR / DAILY ATTENDANCE’, ‘RAT’, 3);
INSERT INTO SET_MODULES
VALUES
(21, ‘UPLOAD DATA INTO DATABASE’, ‘AOD’, 3);
INSERT INTO SET_MODULES
VALUES
(22, ‘SET COMPANY POLICY’, ‘STD’, 3);
CREATE TABLE SET_FORMS
(ID NUMBER NOT NULL,
NAME VARCHAR2(20),
ABRV VARCHAR2(6),
TYPE CHAR(1),
F_VIEW CHAR(1),
F_INSERT CHAR(1),
F_UPDATE CHAR(1),
F_DELETE CHAR(1),
MOD_ID NUMBER,
PROG_DESC VARCHAR2(200));
/*HERE TYPE R MEANS REPORT
AND TYPE F MEANS FORM*/
INSERT INTO SET_FORMS
(ID, NAME, ABRV, TYPE, MOD_ID)
VALUES
(341, 'FRMADJUST_ATTN_TAX', 'TAXADV','F' ,1 );
INSERT INTO SET_FORMS
(ID, NAME, ABRV, TYPE, MOD_ID)
VALUES
INSERT INTO SET_FORMS
(ID, NAME, ABRV, TYPE, MOD_ID)
VALUES
(241, 'REP_SAL_DTL_2', 'RSDTL2', 'R',1 );
INSERT INTO SET_FORMS
(ID, NAME, ABRV, TYPE, MOD_ID)
VALUES
(186, 'REP_ACC1', 'RVCHER', 'R' ,1 );
INSERT INTO SET_FORMS
(ID, NAME, ABRV, TYPE, MOD_ID)
VALUES
(2, 'PAYROLL', 'PAY','F' ,1 );
INSERT INTO SET_FORMS
(ID, NAME, ABRV, TYPE, MOD_ID)
VALUES
(183, 'EMP_ON_PAYROLL', 'RCUEMP' , 'R' ,1 );
INSERT INTO SET_FORMS
(ID, NAME, ABRV, TYPE, MOD_ID)
VALUES
(188, 'REP_SAL_SUM', 'RSLSUM' , 'R' ,1 );
INSERT INTO SET_FORMS
(ID, NAME, ABRV, TYPE, MOD_ID)
VALUES
(3, 'FRMADVANCE', 'ALL' , 'F' ,2 );
INSERT INTO SET_FORMS
(ID, NAME, ABRV, TYPE, MOD_ID)
VALUES
(282, 'REPALLOWANCE', 'REPALL' , 'R' ,2 );
INSERT INTO SET_FORMS
(ID, NAME, ABRV, TYPE, MOD_ID)
VALUES
(5, 'INCOME_TAX', 'ITX' , 'F' ,4 );
INSERT INTO SET_FORMS
(ID, NAME, ABRV, TYPE, MOD_ID)
VALUES
(7, 'FRMMANEDIT', 'MEDIT' , 'F' ,5 );
INSERT INTO SET_FORMS
(ID, NAME, ABRV, TYPE, MOD_ID)
VALUES
(8, 'FRMEXCELPROCESS', 'ETE' , 'F' ,6 );
INSERT INTO SET_FORMS
(ID, NAME, ABRV, TYPE, MOD_ID)
VALUES
(321, 'REP_ANVIS', 'REPANV' , 'R' ,13 );
INSERT INTO SET_FORMS
(ID, NAME, ABRV, TYPE, MOD_ID)
VALUES
(43, 'PERSONNEL', 'PER' , 'F' ,13 );
INSERT INTO SET_FORMS
(ID, NAME, ABRV, TYPE, MOD_ID)
VALUES
(301, 'IND_DETAIL', 'REPIND' , 'R' ,13 );
INSERT INTO SET_FORMS
(ID, NAME, ABRV, TYPE, MOD_ID)
VALUES
(181, 'REP_DOC', 'REPDOC' , 'R' ,13 );
INSERT INTO SET_FORMS
(ID, NAME, ABRV, TYPE, MOD_ID)
VALUES
(184, 'REP_PERSONNEL', 'RDCEMP' , 'R' , 13 );
INSERT INTO SET_FORMS
(ID, NAME, ABRV, TYPE, MOD_ID)
VALUES
(48, 'EMP_DESGN', 'EDG' , 'F' ,14 );
INSERT INTO SET_FORMS
(ID, NAME, ABRV, TYPE, MOD_ID)
VALUES
(361, 'REPINCREMENT', 'REPINC' , 'R' ,14 );
INSERT INTO SET_FORMS
(ID, NAME, ABRV, TYPE, MOD_ID)
VALUES
(53, 'HIERERICHAL', 'HIR' , 'F' ,15 );
INSERT INTO SET_FORMS
(ID, NAME, ABRV, TYPE, MOD_ID)
VALUES
(58, 'EXPTOEXCEL', 'ETE' , 'F' ,16 );
INSERT INTO SET_FORMS
(ID, NAME, ABRV, TYPE, MOD_ID)
VALUES
(102, 'FRMLEVSUS', 'LEV' , 'F' ,17 );
INSERT INTO SET_FORMS
(ID, NAME, ABRV, TYPE, MOD_ID)
VALUES
(101, 'FRMEMPSHIFTS', 'FES' , 'F' ,17 );
INSERT INTO SET_FORMS
(ID, NAME, ABRV, TYPE, MOD_ID)
VALUES
(207, 'EMP_PERF_HRS', 'REPH1' , 'R' ,18 );
INSERT INTO SET_FORMS
(ID, NAME, ABRV, TYPE, MOD_ID)
VALUES
(146, 'MNTH_ATT', 'MNATTN' , 'F' ,18 );
INSERT INTO SET_FORMS
(ID, NAME, ABRV, TYPE, MOD_ID)
VALUES
(206, 'REP_ATT_MONTH', 'RMAABR' , 'R' ,18 );
INSERT INTO SET_FORMS
(ID, NAME, ABRV, TYPE, MOD_ID)
VALUES
(208, 'EMP_PERF_HRS_2', 'REPH2' , 'R' ,18 );
INSERT INTO SET_FORMS
(ID, NAME, ABRV, TYPE, MOD_ID)
VALUES
(205, 'REP_EMP_DTL', 'REMATN' , 'R' ,18 );
INSERT INTO SET_FORMS
(ID, NAME, ABRV, TYPE, MOD_ID)
VALUES
(144, 'TIME_ATTM', 'DATTN' , 'F' ,20 );
INSERT INTO SET_FORMS
(ID, NAME, ABRV, TYPE, MOD_ID)
VALUES
(261, 'REP_EMPTIMEWISE', 'RESHTW' , 'R' ,20 );
INSERT INTO SET_FORMS
(ID, NAME, ABRV, TYPE, MOD_ID)
VALUES
(281, 'FRMADJUSTATTN', 'FADATN' , 'F' ,20 );
INSERT INTO SET_FORMS
(ID, NAME, ABRV, TYPE, MOD_ID)
VALUES
(209, 'REP_EMPSHFT', 'RRATTN' , 'R' ,20 );
INSERT INTO SET_FORMS
(ID, NAME, ABRV, TYPE, MOD_ID)
VALUES
(161, 'FRMTMP', 'UPLDAT' , 'F' ,21 );
INSERT INTO SET_FORMS
(ID, NAME, ABRV, TYPE, MOD_ID)
VALUES
(145, 'SET_SETUP', 'SSET' , 'F' ,22 );
INSERT INTO SET_FORMS
(ID, NAME, ABRV, TYPE, MOD_ID)
VALUES
(143, 'FRMEVALUATION', 'FRMEV' , 'F' ,41 );
INSERT INTO SET_FORMS
(ID, NAME, ABRV, TYPE, MOD_ID)
VALUES
(141, 'FRMADVANCE', 'FRMADV' , 'F' ,61 );
INSERT INTO SET_FORMS
(ID, NAME, ABRV, TYPE, MOD_ID)
VALUES
(221, 'CMP_DEPT', 'CCMPDP' , 'F' ,81 );
INSERT INTO SET_FORMS
(ID, NAME, ABRV, TYPE, MOD_ID)
VALUES
INSERT INTO SET_FORMS
(ID, NAME, ABRV, TYPE, MOD_ID)
VALUES
(222, 'FRM_ACCESS', 'ADDFRM' , 'F' ,81 );
INSERT INTO SET_FORMS
(ID, NAME, ABRV, TYPE, MOD_ID)
VALUES
INSERT INTO SET_FORMS
(ID, NAME, ABRV, TYPE, MOD_ID)
VALUES
(224, 'FRM_EMP_ACCESS', 'FEACCS' , 'F',82 );
INSERT INTO SET_FORMS
(ID, NAME, ABRV, TYPE, MOD_ID)
VALUES
(225, 'FRM_ACCESS', 'ADDFRM' , 'F', 82 );
INSERT INTO SET_FORMS
(ID, NAME, ABRV, TYPE, MOD_ID)
VALUES
(227, 'FRMSET_DEGREE', 'ADDDEG' , 'F', 83 );
INSERT INTO SET_FORMS
(ID, NAME, ABRV, TYPE, MOD_ID)
VALUES
INSERT INTO SET_FORMS
(ID, NAME, ABRV, TYPE, MOD_ID)
VALUES
(226, 'FRMOBS', 'ADDOBS' ,'F', 83 );

Thanks & Regards
Syed Adeel Ali

Hello,

I need to drop tables and recreate them. However, each of the tables are interdependent to others. Is there a script to disable relationship constraints (even if the depth is 2 or more levels)?

The purpose of my request is:
Our data modeler releases new version of DDL script which may have a very minor change like change in column order of an index. I could't figure out easily which index is changed. My plan is to install all those DDLs to my temporary schema, figure out the difference between my temporary schema and the real schema and apply the difference (pretty tedious task). When a another release of DDL is released, I need to clean up my temporary schema and install DDL again.

Thanks. Well, the modeler says ERWin doesn't have the option of generating the delta. So he is giving me the entire DDL script.

I thought about recreating the schema, but in UAT and eventually in Production database, it would be a issue to recreate a user (often). On development box, that is what I am currently doing.

Thanks,

CREATE TABLE TEST
(LEVELS NUMBER,
NAME VARCHAR2(65),
ID NUMBER)

INSERT INTO TEST VALUES (1, 'A' , 1);
INSERT INTO TEST VALUES (2, 'B' , 12);
INSERT INTO TEST VALUES (3, 'C' , 123);
INSERT INTO TEST VALUES (4, 'D' , 1234);
INSERT INTO TEST VALUES (4, 'E' , 1235);
INSERT INTO TEST VALUES (4, 'F' , 1236);
INSERT INTO TEST VALUES (3, 'G' , 1237);
INSERT INTO TEST VALUES (4, 'H' , 1238);
INSERT INTO TEST VALUES (4, 'I' , 1239);
INSERT INTO TEST VALUES (1, 'J' , 2001);
INSERT INTO TEST VALUES (2, 'K' , 2002);
INSERT INTO TEST VALUES (3, 'L' , 2003);
INSERT INTO TEST VALUES (4, 'M' , 2004);
INSERT INTO TEST VALUES (4, 'N' , 2005);
INSERT INTO TEST VALUES (1, 'O' , 2100);
INSERT INTO TEST VALUES (2, 'P' , 2101);
INSERT INTO TEST VALUES (3, 'Q' , 2102);
INSERT INTO TEST VALUES (3, 'R' , 2103);


select * from test

I WANT
A/B/C/D,E,F
A/B/G/H,I
J/K/L/M,N
O/P/Q,R

IS IT POSSIBLE

OR CAN I GET THIS :

A/B/C/D
A/B/C/E
A/B/C/F
A/B/G/H
A/B/G/I
J/K/L/M
J/K/L/N
O/P/Q
O/P/R

Please let me know if this is possible . I tried some of your methods But, nothing works or may be I haven't tried enough.
Thanks in advance.

Sorry I gave select * instead of the table data.

Here it is :

NUMBER NAME ID
1 A 1
2 B 12
3 C 123
4 D 1234
4 E 1235
4 F 1236
3 G 1237
4 H 1238
4 I 1239
1 J 2001
2 K 2002
3 L 2003
4 M 2004
4 N 2005
1 O 2100
2 P 2101
3 Q 2102
3 R 2103

CREATE TABLE TESTING
( "ID" NUMBER,
"PARENT_ID" NUMBER,
"NAME" VARCHAR2(50)
) ;



Insert into TESTING(ID,PARENT_ID,NAME) values (1,0,'A');
Insert into TESTING(ID,PARENT_ID,NAME) values (100,1,'B');
Insert into TESTING(ID,PARENT_ID,NAME) values (200,100,'C');
Insert into TESTING(ID,PARENT_ID,NAME) values (201,200,'D');
Insert into TESTING(ID,PARENT_ID,NAME) values (202,200,'E');
Insert into TESTING(ID,PARENT_ID,NAME) values (300,100,'G');
Insert into TESTING(ID,PARENT_ID,NAME) values (301,300,'H');
Insert into TESTING(ID,PARENT_ID,NAME) values (302,300,'I');
Insert into TESTING(ID,PARENT_ID,NAME) values (2,0,'J');
Insert into TESTING(ID,PARENT_ID,NAME) values (2000,2,'K');
Insert into TESTING(ID,PARENT_ID,NAME) values (3000,2000,'L');
Insert into TESTING(ID,PARENT_ID,NAME) values (3001,3000,'M');
Insert into TESTING(ID,PARENT_ID,NAME) values (3002,3000,'N');
Insert into TESTING(ID,PARENT_ID,NAME) values (3,0,'O');
Insert into TESTING(ID,PARENT_ID,NAME) values (30,3,'P');
Insert into TESTING(ID,PARENT_ID,NAME) values (31,30,'Q');
Insert into TESTING(ID,PARENT_ID,NAME) values (32,30,'R');

there is a unique index on ID Column which is a primary key


ALTER TABLE "TESTING" MODIFY ("ID" NOT NULL ENABLE);

Alter Table "TESTING" Modify ("NAME" Not Null Enable);

SELECT LEVEL,NAME,ID FROM TESTING START WITH PARENT_ID = 0 CONNECT BY PRIOR ID = PARENT_ID;


What I want is :


PARENT CHILDREN

A/B/C D,E,F
A/B/G H,I
J/K/L M,N
O/P Q,R


or

PARENT CHILD1 CHILD2 CHILD3

A/B/C D E F
A/B/G H I NULL
J/K/L M N NULL
O/P Q R NULL


Is it possible..... I am asking it in the right way... Please do let me know

Thanks in advance

I am trying to paste an image But its not allowing me

This is what the actual structure is

A (has child B)
|
B (has children C & G )
|
C has children (D,E,F)
&
G has children (H, I)

J (has child K)
|
K (has child L)
|
L (has children M and N)

O (Has Child P)
|
P has children (Q,R)


Thats how I have the data.......
May be I am not asking it right..... I'll try my best to convey this in a better way...

A (has child B)
|
B (has children C & G )
|
C has children (D,E,F)
&
G has children (H, I)

A (has child B)
|
B (has children C & G )
|
C has children (D,E,F) & F has child X and Y
&
G has children (H, I)

Insert into TESTING(ID,PARENT_ID,NAME) values (203,200,'F');

Ok. I'll try my best to explain :

There are these set of users who have permissions to few pages
There is this Table TABLE1 which has ID, Permission Id and Name
There is another Table TABLE2 which has RID and Permission Id (Foreign Key -> TABLE1)

So, TABLE1 would have a particular set of permissions for a particular user role

To simplify it C will have these 3 children D,E,F

For defining a new role I need the A/B/C as Header and D,E,F individually so as to select and add individual role.

I hope it makes sense..... If not I'll try a bit more....

I thought giving the end query would be simpler but Its not...I get it now....

Thanks a lot for your patience.....I appreciate it.....

Hi Tom,

1.CONNECT BY
2.RECURSIVE SQL -WITH clause


which is faster performance wise when I can use both the approaches to solve a problem ?
(OR) Does it depend ?

Hi Tom,

T1 is a table with approx 1M rows and the given query is running like a dog. The requirement is to identify if a leaf node exists for t3 matching the input variable v2.

In terms of performance, which one is better and why? Will the functionality be any different? Will the second query visit only the subset of matching rows rather than trawling the entire table?

1)

SELECT c1
FROM   t1
WHERE  c2 = :v1
AND    LEVEL > 1 -- Is this good practice?
START WITH t3 = :v2
CONNECT BY PRIOR t3 = t4;

or

2)

SELECT c1
FROM   t1
START WITH t3 = :v2
CONNECT BY PRIOR t3 = t4
AND    c2 = :v1
AND    LEVEL > 1

Is there any merit in adding indexes on t3 & t4?

Thanks

scott%ORA11GR2> select rpad('*', 2*level, '*')|| ename nm
  2  from emp
  3  start with mgr is null
  4  connect by prior empno = mgr and level > 1;

NM
-------------------------------------------------------------------------------
**KING
****JONES
******SCOTT
********ADAMS
******FORD
********SMITH
****BLAKE
******ALLEN
******WARD
******MARTIN
******TURNER
******JAMES
****CLARK
******MILLER

14 rows selected.

scott%ORA11GR2> select rpad('*', 2*level, '*')|| ename nm
  2  from emp
  3  where level > 1
  4  start with mgr is null
  5  connect by prior empno = mgr and level > 1;

NM
-------------------------------------------------------------------------------
****JONES
******SCOTT
********ADAMS
******FORD
********SMITH
****BLAKE
******ALLEN
******WARD
******MARTIN
******TURNER
******JAMES
****CLARK
******MILLER

13 rows selected.

The requirement is to identify if a leaf node exists for t3 matching the input variable v2 - Connect_by_isleaf is what you needed if you are on Oracle 10G database.

rajesh@ORA11GR2> variable x number;
rajesh@ORA11GR2>
rajesh@ORA11GR2> exec :x := 7839;

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.09
rajesh@ORA11GR2>
rajesh@ORA11GR2> select emp.*,level
  2  from emp
  3  where connect_by_isleaf = 1
  4  start with empno = :x
  5  connect by prior empno = mgr
  6  /

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO      LEVEL
---------- ---------- --------- ---------- --------- ---------- ---------- ---------- ----------
      7876 ADAMS      CLERK           7788 12-JAN-83       1100                    20          4
      7369 SMITH      CLERK           7902 17-DEC-80        800          2         20          4
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30          3
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30          3
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30          3
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30          3
      7900 JAMES      CLERK           7698 03-DEC-81        950                    30          3
      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10          3

8 rows selected.

Elapsed: 00:00:00.48
rajesh@ORA11GR2>
rajesh@ORA11GR2>

You're right about greyhounds, they can do up to 45mph. I should had said a "dog with three legs".

An excerpt from "OCA Oracle Database SQL Certified Expert Exam Guide (Exam 1Z0-047)" reads:

"It’s theoretically possible to use the WHERE clause to omit rows that will collectively represent an entire branch. But that’s not the same thing as identifying a branch and excluding it as a branch. CONNECT BY is the clause that can identify a branch and exclude it in its entirety.".

Stats from the first query:

FILTER
  CONNECT BY WITH FILTERING
    TABLE ACCESS BY INDEX ROWID COLLECTIONS
      INDEX UNIQUE SCAN PK_COLLECTIONS
    NESTED LOOPS
      CONNECT BY PUMP
      TABLE ACCESS BY INDEX ROWID COLLECTIONS
        INDEX RANGE SCAN COLLECTIONS_PARENT_ID_I

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        3      0.05       0.04          2          0         10           0
Execute      3      0.00       0.00          0          0          0           0
Fetch        6    187.09     430.74     370579    2011798   19705257           9
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       12    187.14     430.79     370581    2011798   19705267           9

Stats from the second query:

FILTER
  CONNECT BY WITH FILTERING
    TABLE ACCESS BY INDEX ROWID COLLECTIONS
      INDEX UNIQUE SCAN PK_COLLECTIONS
    NESTED LOOPS
      CONNECT BY PUMP
      TABLE ACCESS BY INDEX ROWID COLLECTIONS
        INDEX RANGE SCAN COLLECTIONS_PARENT_ID_I

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        5      0.03       0.02          2          0          4           0
Execute      5      0.00       0.00          0          0          0           0
Fetch       10      0.17       1.32        532        740          0          15
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       20      0.20       1.35        534        740          4          15

The execution paths are exactly the same, but the number of fetches is enormously different.

Please corroborate the findings if you get a chance.

Thanks


PS: Rajeshwaran Jeyabal

I have two input parameters, one decides the "start with" filter, and the other decides the "look for" filter. So I presume it boils down to the same question: where should the "look for" predicate be supplied: in the where clause or the connect by clause? Nevertheless it is a very useful hint. Will try to put it to some use.

Hi Tom,
My question is very simple. But am not able to find the answer at all in any communities.

My table is
CREATE TABLE TARGET_SUBTARGET
(
TARGET_ID NUMBER(5),
SUB_TARGET_ID NUMBER(5)
);

insert into target_subtarget values ( 1,2);
insert into target_subtarget values ( 1,3);
insert into target_subtarget values ( 2,4);
insert into target_subtarget values ( 2,5);
insert into target_subtarget values (3,6);
insert into target_subtarget values(3,7);



Now the table looks like this

SQL> select * from target_subtarget;

TARGET_ID SUB_TARGET_ID
---------- -------------
1 2
1 3
2 4
2 5
3 6
3 7

6 rows selected.

Now i need to write a querey which can give me a result like this.

TARGET_ID SUB_TARGET_ID
---------- -------------
1 2
1 3
1 4
1 5
1 6
1 7
2 4
2 5
3 6
3 7

The records in bold are inherited records. Can you please help me out to wirte a simple querey?

ops$tkyte%ORA11GR2> select target_id, connect_by_root( target_id ) , sub_target_id
  2    from target_subtarget
  3  connect by prior sub_target_id = target_id
  4  order by 2, 3
  5  /

 TARGET_ID CONNECT_BY_ROOT(TARGET_ID) SUB_TARGET_ID
---------- -------------------------- -------------
         1                          1             2
         1                          1             3
         2                          1             4
         2                          1             5
         3                          1             6
         3                          1             7
         2                          2             4
         2                          2             5
         3                          3             6
         3                          3             7

10 rows selected.

Hi Tom,
My question is very simple. But am not able to find the answer at all in any communities.

Please ignore the above question and add some more to it.

My table is
CREATE TABLE TARGET_SUBTARGET
(
TARGET_ID NUMBER(5),
SUB_TARGET_ID NUMBER(5)
);

insert into target_subtarget values ( 1,2);
insert into target_subtarget values ( 1,3);
insert into target_subtarget values ( 2,4);
insert into target_subtarget values ( 2,5);
insert into target_subtarget values (3,6);
insert into target_subtarget values(3,7);
insert into target_subtarget values(4,8);
insert into target_subtarget values(5,9);

Now the table looks like this,

TARGET_ID SUB_TARGET_ID
---------- -------------
1 3
1 2
2 4
2 5
3 6
3 7
4 8
5 9

Now i need to write a querey which can give me a result like this.

TARGET_ID SUB_TARGET_ID
---------- -------------
1 2
1 3
1 4
1 5
1 6
1 7
1 8
1 92 4
2 5
3 6
3 7

This may help you http://www.orafaq.com/forum/mv/msg/95011/582946/150834/#msg_582946

Hi Tom,
this may be the new question but looks similer.I am beginer to sql and one of the senior person asked me to write query for below requirement.

Q) employee name/no and the departments he worked should come in one row/column by seperatin with coma(,). like below

Result
========
Scott,10,20,30
SAM,10,30

I said that it can be done using pl/sql,is there any possibility to get this result with sql alone.

Thanks In advace,

ops$tkyte%ORA11GR2> select ename, ltrim(max(sys_connect_by_path( deptno, ',' )),',') scbp
  2    from (select ename, deptno, row_number() over (partition by ename order by deptno)rn
  3            from (select distinct job ename, deptno from scott.emp)
  4             )
  5   start with rn = 1
  6  connect by prior rn = rn-1 and prior ename = ename
  7   group by ename
  8  /

ENAME     SCBP
--------- ----------------------------------------
CLERK     10,20,30
SALESMAN  30
PRESIDENT 10
MANAGER   10,20,30
ANALYST   20

Several SQL approaches regarding so-called String Aggregation can be found here:
http://www.oracle-base.com/articles/misc/string-aggregation-techniques.php

Hi Tom,

Most of examples out there build a hierarchy of the same types of entities (managers and employees are both 'people').

I have tables that looks like so:

Departments:
ID   NAME   PURCH_CODE   MGR_ID
1    IT     84723        800
2    SALES  47343        900
...

Employees:
ID   NAME   START_DATE   REVIEW_DATE
100  Bob    20150101     20160303
200  Pam    20160404     20160505
300  Sam    20141212     20160707
400  Jim    20140909     20160202
...

Dept-Grade-Emp map
GRADE_LEVEL   DEPT_ID   EMP_ID
A12           1         100
A12           1         200
A12           2         300
A12           1         400
B17           2         500
...

The user will always select 1 grade level and wants to see the department and employee data presented like so (ordered by department number and then employee IDs under that):

|Entity|Name  | ID    |Type |
| 1    | IT   | 84723 | DEP |
|  100 | Bob  | 100   | EMP |
|  200 | Pam  | 200   | EMP |
|  400 | Jim  | 400   | EMP |
| 2    | SALES| 47343 | DEP |
|  300 | Sam  | 300   | EMP |
...

That's how the business want the result to be displayed. I've tried going at it in a few ways, but am failing. The connection isn't employee-to-employee by manager, but across 2 different entities--it's a hierarchy of departments with employees under it (and joins to other table to pick up other attributes).

Do you have any ideas on how to do it? To make the matters worse, this needs to run on 2 systems (for slightly different data sets), only 1 of which is Oracle (11g). Is there a way to do this without 'connect by'?

Many thanks as always!

SQL> create table d ( id int, name varchar2(10), p int );

Table created.

SQL> create table e ( id int, name varchar2(10) );

Table created.

SQL> create table ed ( grade varchar2(10),did int, eid int );

Table created.

SQL>
SQL> insert into d values (1 ,   'IT', 12345);

1 row created.

SQL> insert into d values (2 ,   'SALES', 56789);

1 row created.

SQL>
SQL> insert into e values (100,  'Bob' );

1 row created.

SQL> insert into e values (200,  'Pam' );

1 row created.

SQL> insert into e values (300,  'Sam' );

1 row created.

SQL> insert into e values (400,  'Jim'  );

1 row created.

SQL>
SQL>
SQL> insert into ed values ('A12'    ,       1    ,     100 );

1 row created.

SQL> insert into ed values ('A12'    ,       1    ,     200);

1 row created.

SQL> insert into ed values ('A12'    ,       2    ,     300);

1 row created.

SQL> insert into ed values ('A12'    ,       1    ,     400);

1 row created.

SQL> insert into ed values ('B17'    ,       2    ,     500);

1 row created.

SQL>
SQL>
SQL> select
  2    decode(typ,'EMP','  ')||eid entity,
  3    name,
  4    id,
  5    typ
  6  from
  7  (
  8  select ed.did, ed.eid, e.name, e.id, 'EMP' typ
  9  from  e,ed
 10  where e.id = ed.eid
 11  and   ed.grade = 'A12'
 12  union all
 13  select d.id, d.id, d.name, d.p, 'DEP' typ
 14  from d
 15  order by 1,2
 16  );

ENTITY                                     NAME                                             ID TYP
------------------------------------------ ---------------------------------------- ---------- ---
1                                          IT                                            12345 DEP
  100                                      Bob                                             100 EMP
  200                                      Pam                                             200 EMP
  400                                      Jim                                             400 EMP
2                                          SALES                                         56789 DEP
  300                                      Sam                                             300 EMP

6 rows selected.

SQL>

For the scenario just above, I used the following for the carcass of it:

with result (id, dep, grade, itemtype, seq) as
(
       select distinct(dept_id) as id, dept_id as dep, grade_id as grade, 'DEP' as itemtype, 0 as seq
       from [map table] where grade_id = 'A17' 
             union all
       select emp_id as id, dept_id as dep, grade_id as grade, 'EMP' as itemtype,
                row_number() over (partition by dep_id order by emp_id) as seq
       from [map table] where grade_id = 'A17'
)
select (case when itemtype = 'DEP' then id when itemtype = 'EMP' then concat('         ',id) end) id, itemtype, seq from result
order by dep, seq;

This results in

ID      ITEMTYPE   SEQ
1       DEP        0
   100  EMP        1
   200  EMP        2
   400  EMP        3
2       DEP        0
   300  EMP        1

When I try to join the second query in the union to to the emp table to get employee attributes, all emp_ids in the result set above turn to nulls...

I'm also still unsure if this is the best way for doing this to begin with...

Sorry Tom, ignore the bit about nulls IDs, that was user error :)

Hi Tom,
Thanks for your answer above. I am trying to turn this query on its head for a different report, but not getting the right results because of non-uniqueness issues.

Departments:
ID   NAME   PURCH_CODE   DESCRIPTION         STAFF_COUNT
1    BA     PCODE11      Business Analysis   10
2    SALES  PCODE12      Sales Department    20
3    DEV    PCODE11      Development         30
4    QA     PCODE12      Quality Assurance   40
5    ARCH   PCODE12      Architecture        50
...
*NOTE*: Purchase codes are not unique to departments.

Employees:
ID   NAME   START_DATE   REVIEW_DATE
100  Bob    20150101     20160303
200  Pam    20160404     20160505
300  Sam    20141212     20160707
400  Jim    20140909     20160202
...

There is an 'infractions' table.  Each infraction has a globally unique id.  
Each record has an employee, an infraction type, and department ID (under which the emp was at the time, 
so an emp can have records spanning multiple depts)

Disciplinary Events
EVENT UID   EMP_ID  DEP_ID   INFR_TYPE
ABC123      100     1        EXX
DEF234      100     1        EXX
GHI211      100     2        EXX
JKL567      100     1        NNN
MNO890      100     3        NNN
PQR111      100     4        NNN
STU345      200     4        NNN
UVW897      200     4        NNN
XYZ010      200     5        NNN
...

A bunch of these will get reviewed on some day and notes will be added. We can assume that all records in the example above are for the same day, so that's what the query will be working on.

The business want a report where, for a given date, the following hierarchy is returned:

* employee (id, name, infraction type, dept purchase code)
  - department (id, name, staff count)
  - department (id, name, staff count)

In other words, group data by employee, infraction type and their dept purchase code (so show the 'parent' line with emp.employee_id/name, disciplinary.infraction_type, dept.purchase_code) and, for each employee, show their departments (from infraction table) and dept name/staff counts.

For the example above, we would expect the data to look as follows:

| ID  | NAME | INFR| PUR_CODE| STAFFCT|
| 100 | BOB  | EXX | PCODE11 |        |
|   1 | BA   |     |         | 10     |
| 100 | BOB  | EXX | PCODE12 |        |
|   2 | SALES|     |         | 20     |
| 100 | BOB  | NNN | PCODE11 |        |
|   1 | BA   |     |         | 10     |
|   3 | DEV  |     |         | 30     |
| 100 | BOB  | NNN | PCODE12 |        |
|   4 | QA   |     |         | 40     |
| 200 | PAM  | NNN | PCODE12 |        |
|   4 | BA   |     |         | 10     |
|   5 | ARCH |     |         | 50     |

The problem is the same departments have to show up multiple times in the result set... In the department part of 'union all', if I push all departments through, then I get duplicates. For example emp=1 has 3 EXX infractions, 2 of which are in dept=1, but I only want a distinct row per department. But if I push through distinct set of departments, then dept=1 doesn't show up for the same employee later on where infraction is NNN...

Could you help? Not sure where I am going wrong...

Thank you as always.

CREATE TABLE dep (ID int, NAME varchar2(5), PURCH_CODE varchar2(7), DESCRIPTION varchar2(17), STAFF_COUNT int)
;
    
INSERT INTO dep VALUES (1, 'BA', 'PCODE11', 'Business Analysis', 10);
INSERT INTO dep VALUES (2, 'SALES', 'PCODE12', 'Sales Department', 20);
INSERT INTO dep VALUES (3, 'DEV', 'PCODE11', 'Development', 30);
INSERT INTO dep VALUES (4, 'QA', 'PCODE12', 'Quality Assurance', 40);
INSERT INTO dep VALUES (5, 'ARCH', 'PCODE12', 'Architecture', 50);

CREATE TABLE emp (ID int, NAME varchar2(3), START_DATE int, REVIEW_DATE int);
    
INSERT INTO emp VALUES (100, 'Bob', 20150101, 20160303);
INSERT INTO emp VALUES (200, 'Pam', 20160404, 20160505);
INSERT INTO emp VALUES (300, 'Sam', 20141212, 20160707);
INSERT INTO emp VALUES (400, 'Jim', 20140909, 20160202);

CREATE TABLE inf (EVENT_UID varchar2(6), EMP_ID int, DEP_ID int, INFR_TYPE varchar2(3));
    
INSERT INTO inf VALUES ('ABC123', 100, 1, 'EXX');
INSERT INTO inf VALUES ('DEF234', 100, 1, 'EXX');
INSERT INTO inf VALUES ('GHI211', 100, 2, 'EXX');
INSERT INTO inf VALUES ('JKL567', 100, 1, 'NNN');
INSERT INTO inf VALUES ('MNO890', 100, 3, 'NNN');
INSERT INTO inf VALUES ('PQR111', 100, 4, 'NNN');
INSERT INTO inf VALUES ('STU345', 200, 4, 'NNN');
INSERT INTO inf VALUES ('UVW897', 200, 4, 'NNN');
INSERT INTO inf VALUES ('XYZ010', 200, 5, 'NNN');

select id, name, infr, pcode, ct, rnd from (
select row_number() over (partition by rk, v order by rk) rne,
    row_number() over (partition by rk, v, name order by rk) rnd,
    s.*
from (
select emp.id eid, emp.name ename, inf.infr_type, null e_count,
    d.id did, d.name dname, d.purch_code, d.staff_count, 
    null dcode, null dtype,
    rank() over (order by emp.id, inf.infr_type) rk
from   emp
join   inf
on     emp.id = inf.emp_id
join   dep d
on     d.id = inf.dep_id
)
unpivot ((id, name, infr, pcode, ct) for v in (
  (eid, ename, infr_type, purch_code, e_count) as 'E',
  (did, dname, dtype, dcode, staff_count) as 'D'
)
) s
)
where  case when v = 'E' then 1 else rne end = rne
and    case when v = 'D' then 1 else rnd end = rnd
order by rk, v desc;

ID   NAME   INFR  PCODE    CT  RND  
100  Bob    EXX   PCODE11      1    
2    SALES                 20  1    
1    BA                    10  1    
100  Bob    NNN   PCODE12      1    
4    QA                    40  1    
1    BA                    10  1    
3    DEV                   30  1    
200  Pam    NNN   PCODE12      1    
5    ARCH                  50  1    
4    QA                    40  1

I have a view and table EG_OFFICE_ORGANIZATION_UNITS_V and EG_OFFICE_POSITIONS_T

CREATE OR REPLACE FORCE EDITIONABLE VIEW "EG_OFFICE_ORGANIZATION_UNITS_V
("ORG_UNIT_ID", "ORG_UNIT_NAME", "ORG_UNIT_SHORTNAME", "PARENT_ORGUNIT_ID",
"ENTITY_TYPE_ID", "ENTITY_NAME", "HIERARCHY") AS
select EOU.ORG_UNIT_ID,
EOU.ORG_UNIT_NAME,
EOU.ORG_UNIT_SHORTNAME,
EOU.PARENT_ORGUNIT_ID,
EOU.ENTITY_TYPE_ID,
EET.ENTITY_NAME,
EET.HIERARCHY
from EG_OFFICE_ORGANIZATION_UNITS_T EOU,
EG_OFFICE_ENTITY_TYPES_T EET
where EOU.ENTITY_TYPE_ID = EET.ENTITY_TYPE_ID
and EOU.STATUS = 'Y'
order by EET.HIERARCHY, EOU.ORG_UNIT_NAME

with this data in it:

insert into EG_OFFICE_ORGANIZATION_UNITS_V values
('5','Govt.of Tamil Nadu','GOTN','-','1','Govt. of Tamil Nadu','10');
insert into EG_OFFICE_ORGANIZATION_UNITS_V values
('6','Chief Secretary','CS','5','5','Secretariat','20');
insert into EG_OFFICE_ORGANIZATION_UNITS_V values
('7','Rural and Pachayat Raj','RD','6','2','Department','30');
insert into EG_OFFICE_ORGANIZATION_UNITS_V values
('12','Information Technology and Digital Services','IT&DS','6','2','Department','30');
insert into EG_OFFICE_ORGANIZATION_UNITS_V values
('17','Electronics Corporation of Tamilnadu','ELCOT','12','3','Directorate','40');
insert into EG_OFFICE_ORGANIZATION_UNITS_V values
('16','Tamil Arasu Cable Operator ','TACO','12','3','Directorate','40');
insert into EG_OFFICE_ORGANIZATION_UNITS_V values
('31','Directorate of e-Governance','DOEG','14','7','Wing','50');


CREATE OR REPLACE EG_OFFICE_POSITIONS_T TABLE(POSITION_ID,POSITION_NAME,ORG_ID,ORGUNIT_ID);

with this data in it:

insert into EG_OFFICE_POSITIONS_T values ('15','Manager','1','6');
insert into EG_OFFICE_POSITIONS_T values ('12','General Manager','1','7');
insert into EG_OFFICE_POSITIONS_T values ('13','Assistant Manager','1','7');
insert into EG_OFFICE_POSITIONS_T values ('15','Principal Secretary','1','13');
insert into EG_OFFICE_POSITIONS_T values ('2','Director-TNeGA','1','15');
insert into EG_OFFICE_POSITIONS_T values ('16','Test Admin','1','16');

OUTPUT :
I need Hierarchical tree structure for these EG_OFFICE_ORGANIZATION_UNITS_V view and EG_OFFICE_POSITIONS_T table with connect by prior.
If I click ORG_UNIT_NAME it will come POSITION_NAME under ORG_UNIT_NAME like tree structure..
eg for Tree:
Govt.of Tamil Nadu
|
Chief Secretary
|--> Manager
Rural and Pachayat Raj
|--> General Manager

Here is my tried code :
SELECT
CASE
WHEN connect_by_isleaf = 1 THEN 0
WHEN level = 1 THEN 1
ELSE -1
END AS status,
level,
org."ORG_UNIT_NAME" AS title,
NULL AS icon,
-- org."ORG_UNIT_ID" AS value,
pos."POSITION_ID" AS VALue, -- Include position name
NULL AS tooltip,
NULL AS link
FROM
"#OWNER#"."EG_OFFICE_ORGANIZATION_UNITS_V" org
LEFT JOIN
"#OWNER#"."EG_OFFICE_POSITIONS_V" pos
ON
org."ORG_UNIT_ID" = pos."ORGUNIT_ID" -- Adjust the join condition based on your schema
START WITH
org."PARENT_ORGUNIT_ID" IS NULL
CONNECT BY PRIOR org."ORG_UNIT_ID" = org."PARENT_ORGUNIT_ID"
ORDER SIBLINGS BY org."ORG_UNIT_NAME";

Govt.of Tamil Nadu
|
Chief Secretary
|--> Manager
Rural and Pachayat Raj
|--> General Manager