Skip to Main Content

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Karma.

Asked: June 09, 2003 - 9:34 pm UTC

Last updated: September 08, 2016 - 4:41 pm UTC

Version: 8.1.7

Viewed 10K+ times! This question is

You Asked

Hi:
I have hierarchical query where i would like to know all parents, grand parents, their parents etc. i.e. as connect by value is changing i would like to know. I know in 9i we have sys_connect_by_path but i need that functionality in 8.1.7.
Is this possible ?

One approach i was thinking is to use object types but i am not too sure.

Thanks

Karma

and Tom said...

you would have to write a user defined function to do it.


ops$tkyte@ORA817DEV> create or replace function scbp( p_empno in number ) return varchar2
2 as
3 l_data varchar2(4000);
4 begin
5 for x in ( select ename
6 from emp
7 start with empno = p_empno
8 connect by prior mgr = empno )
9 loop
10 l_data := l_data || '/' || x.ename;
11 end loop;
12 return l_data;
13 end;
14 /

Function created.

ops$tkyte@ORA817DEV>
ops$tkyte@ORA817DEV> column ename format a15
ops$tkyte@ORA817DEV> column scbp format a40
ops$tkyte@ORA817DEV>
ops$tkyte@ORA817DEV> select rpad( '*', 2*level, '*' ) || ename ename, scbp( empno ) scbp
2 from emp
3 start with mgr is null
4 connect by prior empno = mgr
5 /

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

14 rows selected.


Rating

  (100 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

A reader, June 10, 2003 - 10:13 am UTC


hierarchial query

A reader, June 10, 2003 - 11:10 am UTC

Excellent

8.1.7 sys_connect_by_path

A reader, June 10, 2003 - 12:53 pm UTC

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

Tom Kyte
June 10, 2003 - 12:56 pm UTC

i would strongly discourage that.

not to "get you to upgrade", but because as with ALL _ parameters, there can be serious and unintentional side effects..

"just say no"

Excellent

A reader, June 10, 2003 - 1:01 pm UTC


Hierarchical Query

A reader, June 14, 2004 - 7:02 pm UTC

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

Tom Kyte
June 15, 2004 - 2:59 pm UTC

parents of 4 and 7 seem to be 3 and 6??

sorry, did not follow this one at all.

Hierarchical Query

A reader, June 16, 2004 - 5:26 am UTC

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

Tom Kyte
June 16, 2004 - 12:41 pm UTC

hate to be a stickler, but -- create table.... insert into table......

Hierarchical Query

A reader, June 17, 2004 - 6:04 am UTC

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 

Tom Kyte
June 17, 2004 - 10:13 am UTC

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
 

Hierarchical Query

A reader, June 17, 2004 - 11:34 am UTC

Thanks Tom - This is exactly what I wanted.

is this possible with just SQL at all

j., September 23, 2004 - 6:23 pm UTC

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?

Tom Kyte
September 24, 2004 - 10:09 am UTC

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
 

Should '1' be a good guy?

Gabe, September 24, 2004 - 10:31 am UTC

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.


Tom Kyte
September 24, 2004 - 11:40 am UTC

he wanted to nuke any "tree" with "3" in it.

...
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?
.....

we weren't given "1", we had "3" to work with.

compressing number of rows using Hierarchical query

Anand Kothapeta, November 09, 2005 - 11:11 am UTC

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.

Tom Kyte
November 11, 2005 - 10:08 am UTC

I'd be using analytics for this - similar to the way I created groups of unknown cardinality by looking back a row here:

</code> https://www.oracle.com/technetwork/issue-archive/2014/14-mar/o24asktom-2147206.html <code>
see analytics to the rescue.

I'd demonstrate, but I couldn't find the create table and insert intos for this example.... :)

compressing number of rows using Hierarchical query

Anand Kothapeta, November 10, 2005 - 8:31 am UTC

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

Tom Kyte
November 11, 2005 - 11:47 am UTC

see above, analytics would be my approach to collapse "adjacent" rows into a single row.

To "Anand Kothapeta from Hartford,CT"

Logan Palanisamy, November 11, 2005 - 10:44 pm UTC

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.

 

Hierarchical query or one more table?

Dmytro, June 06, 2006 - 3:17 am UTC

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

Multiple records within a hierarchy

A reader, October 10, 2006 - 11:58 am UTC

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



How to find data causing ORA-01436: CONNECT BY loop in user data

Shivdeep Modi, March 06, 2007 - 7:10 pm UTC

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
Tom Kyte
March 07, 2007 - 10:06 am UTC

Well, in 10g, you can NOCYCLE:

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


In 9i, you can use single row fetching:

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





How to find data causing ORA-01436: CONNECT BY loop in user data

Shivdeep Modi, March 08, 2007 - 5:25 am UTC

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

Hierarchical Queries

Hemal, April 20, 2007 - 11:34 am UTC

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








Tom Kyte
April 20, 2007 - 1:48 pm UTC

you'll have to wait for MS SQL SERVER 2020 (hindsight :)

you'll need to sort of figure out how to do a connect by in sqlserver, procedurally I think will be their answer.

Substitute for NOCYCLE in 9i

Roy, June 15, 2007 - 2:32 am UTC

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.
Tom Kyte
June 15, 2007 - 7:46 am UTC

not without a full understanding of your data model.

Is there anything you can put into your connect by to detect that you are entering a cycle - intimate knowledge of your data and how it relates to itself would be required (eg: you need to answer this, can you logically look at a row and say "stop here")

Roy, June 15, 2007 - 2:37 am UTC

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.

Substitute for NOCYCLE in 9i

Roy, June 15, 2007 - 11:41 am UTC

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.
Tom Kyte
June 15, 2007 - 12:02 pm UTC

connect by ...... and subadv_count > 0;


that'll stop connecting when subadv_count is not greater than zero

Substitute for NOCYCLE in 9i

Roy, June 15, 2007 - 12:17 pm UTC


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 Kyte
June 15, 2007 - 12:32 pm UTC

ok, connect by prior whatever > 0;

yes.

put together an example that is failing (create table, insert intos) and it'll likely become obvious what else is missing

Substitute for NOCYCLE in 9i

Roy, June 15, 2007 - 12:26 pm UTC

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 Kyte
June 15, 2007 - 12:33 pm UTC

now, use your knowledge of your data and your knowledge of connect by and figure out "what condition makes us stop"

Vow you are Back

A reader, June 20, 2007 - 3:43 am UTC

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 .......

Substitute for NOCYCLE in 9i

Roy, June 20, 2007 - 3:43 am UTC

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.

how to store .pdf and .txt files in oracle database

Rishipal, June 20, 2007 - 7:34 am UTC

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........

Uncontrolled chain reaction with CONNECT BY

Roy, June 22, 2007 - 10:08 am UTC

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!!
Tom Kyte
June 22, 2007 - 5:04 pm UTC

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.

CONNECT BY

Roy, June 22, 2007 - 12:31 pm UTC

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.
Tom Kyte
June 22, 2007 - 5:12 pm UTC

that does not work, see above

CONNECT BY

Roy, June 23, 2007 - 4:47 am UTC

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.
Tom Kyte
June 23, 2007 - 9:05 am UTC

I learned everything I know from others - just keep hanging out in the online communities..

A reader, July 25, 2007 - 6:25 am UTC

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 Kyte
July 26, 2007 - 9:11 am UTC

no create
no inserts
NO LOOK

Tuning Hierarchical Query

Chris, August 30, 2007 - 10:48 am UTC

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?
Tom Kyte
September 04, 2007 - 5:23 pm UTC

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

Helena Marková, September 05, 2007 - 2:47 am UTC


union and hierarchical queries

Michal Pravda, January 04, 2008 - 1:57 am UTC

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?
Tom Kyte
January 04, 2008 - 11:46 am UTC

could not reproduce in 8i, 9i or 10g...

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

even stranger

Michal Pravda, January 07, 2008 - 3:29 am UTC

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.
Tom Kyte
January 07, 2008 - 8:06 am UTC

that is not a cut and paste

I would suggest setting autotrace on and posting the

a) CUT AND PASTE from splplus
b) with the query plans

sqlplus cut&paste

Michal Pravda, January 11, 2008 - 2:55 am UTC

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.

Tom Kyte
January 11, 2008 - 7:42 am UTC

I filed

Bug No: 6743574

Hierarchical Tree

Dulal, February 04, 2008 - 11:27 pm UTC

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






Tom Kyte
February 05, 2008 - 6:59 am UTC

I haven't touched forms since 1995... please try otn.oracle.com -> discussion forums.

Hierarchical Tree

Dulal, February 06, 2008 - 1:13 am UTC

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

Pls help.
Tom Kyte
February 06, 2008 - 7:51 am UTC

umm,

given that I rather don't know what output you need for the forms tree widget - you would be best served by finding that out first - no?

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.


A reader, February 25, 2008 - 5:20 am UTC


Connect By Prior

Bhushan, March 27, 2008 - 9:13 pm UTC

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
Tom Kyte
March 30, 2008 - 9:12 am UTC

... 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.
....

Let us hope so! Because you changed:

connect by (PRIOR X)=MGR

into

connect by (PRIOR MGR)=X

by just moving X and MGR, you didn't move the "PRIOR" which is a modifier to the next attribute in the query....

The query:

SELECT x,y,mgr,LEVEL FROM (SELECT x,y,mgr FROM counter )
START WITH mgr=0 CONNECT BY PRIOR x=mgr
ORDER BY mgr


starts by finding all rows such that "mgr=0", of which there is a single record.
ops$tkyte%ORA11GR1> select * from counter where mgr=0;

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


It then applies the connect by TO EACH RECORD RETURNED BY THE START WITH. So, once this start with record is "output", it becomes the "prior" record. So, we take "X" from this prior record

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

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



and use that value to retrieve the next level of records by MGR:

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

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


so, that start with record is connected to these two records. Now, they each in turn become the "prior" record, so we'll take X=2 to find any MGR=2 records and output them (whence they themselves become the prior record and so on)

resulting in the hierarchy:

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.


one level=1 (start with)
connected to the two records such that their MGR = level 1's X=1 value.


If you leave off the start with, that is like saying "start with 1=1, EVERY record". So every record is a level one record (and maybe someone elses level 2, 3, 4, whatever....)

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.

Good explanation

A reader, March 31, 2008 - 12:48 am UTC


Tom Kyte
March 31, 2008 - 9:27 am UTC

Howard Rogers decided to bag the site, it is gone.

Superb Explanation!!!

Bhushan, March 31, 2008 - 10:26 pm UTC

Can it get better guys!!!

Cheers to you Tom.

--Bhushan

Need help on avoiding FTS for using connect by prior

lava, April 07, 2008 - 4:17 pm UTC

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'


Tom Kyte
April 09, 2008 - 9:25 am UTC

ahh, the irrational fear of a full scan. It is so scary that even when it doesn't happen - people are fearful of it.

tkprof that

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)



see how it does NO WORK, nothing. It never happened....

don't tune a plan, tune a question.

Not fearing the full tablescan after reading your books and your examples

A reader, April 10, 2008 - 3:31 pm UTC

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 Kyte
April 10, 2008 - 3:43 pm UTC

tkprof please.

Tkprof report on the same sql statement.

lava, April 10, 2008 - 3:44 pm UTC

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'

Tom Kyte
April 10, 2008 - 3:52 pm UTC

create table and create index statements ...

but in general, looks OK so far - you get a ton of initial rows to start with, so using an index might well not make any sense at all.

2 sorts (disk)


you might have insufficient PGA allocated - the amount of data isn't that large.

you should trace with wait events so as to see what you are waiting on mostly.

Duplicated Children

Michael, April 23, 2008 - 9:23 am UTC

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.


Tom Kyte
April 28, 2008 - 9:23 am UTC

given that only you understand your data model, only you can answer your question.


I mean, come on... a little explanation of how you are storing your data would be called for.

no clue what the meaning of "main_id" is - but it must be important, since you "where" on it.


and you do know that:

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


means

a) find all rows with seq=1
b) build the entire hierarchy by connecting parent to prior seq
c) THEN keep only rows where main_id = 480228, quarter=1 and year = 2008.


and you do know that storing things as "year" and "quarter" is not a good thing, you should have a single attribute "the_quarter" that has a DATE in it.

More info

Michael, May 01, 2008 - 10:26 am UTC

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
Tom Kyte
May 01, 2008 - 11:49 am UTC

... When I try moving the where to the connect, I
get the same result: duplicated....

you keep saying duplicated - but you see, your queries return exactly what you code them to return - instead of showing us queries that do not work - be explicit, tell us what you mean - maybe by showing us your EXPECTED output with lots of detail as to why this is what you expect.



why would you connect by a constant main_id like that?

Mohammed Khaliq, May 12, 2008 - 1:49 am UTC

Hi:
I have hierarchical query where i would like to know all childs of the parent and thier levels(considering 'n' levels of generation)
Tom Kyte
May 12, 2008 - 1:30 pm UTC

well, if you have a hierarchical query, you already have that - don't you.

I mean, that is what the query returns.

Mohammed Khaliq, May 12, 2008 - 1:49 am UTC

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

Stored procedure

Mohammed Khaliq, May 12, 2008 - 2:06 am UTC

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;
/


Tom Kyte
May 12, 2008 - 1:34 pm UTC

sorry, this does not make sense, you just answered your own question here? Or are you saying "I don't know how to write a connect by, I don't actually have the query yet"


by the way,you should just code:
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;
/ 



I see you are a sqlserver developer - take a bit of time to learn plsql and you'll find it to be a little better than the rather rudimentary t-sql you've left behind.

Stored Procedure

Mohammed Khaliq, May 13, 2008 - 1:38 am UTC

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
Tom Kyte
May 13, 2008 - 10:31 am UTC

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


one would need requirements then, would not one?


I have no idea what you are really looking for.

stop using the open/fetch stuff - you'll introduce more bugs than not - AND your code will just run slower. Take what I gave you and start from there (lots of stuff to UNLEARN from sqlserver - tons of bad habits!!)

You ignored my code above entirely, I have no idea what you are really looking for, you need to be a bit more precise specification wise.

Removing Duplicates for Multiple Parents in a Hierarchical Query

Edward Girard, July 25, 2008 - 1:48 pm UTC


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;

Performance of hierarchical query

Dibyendu, August 28, 2008 - 2:28 am UTC

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.

Tom Kyte
August 29, 2008 - 3:28 pm UTC

ugh, that is an ugly one:
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;



non-equi joins.

looking at the card= values, it thinks it is basically going to get "a ton of rows" - but I cannot tell - you have a simple "count" (without a tkprof, this is difficult to look at).

how big would:

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


by itself be? (how big is the hierarchy)

How big would:

SQL> SELECT *
2 FROM T_CASES C, T_USERS TU
4 AND C.CASE_OWNER_USER_ID=TU.USER_ID
5 AND C.STATUS = 'OPEN' and TU.USER_ID='X656629'

be as opposed to just select * from t_users tu where TU.USER_ID='X656629'


that is - should we

a) build heirarchy, and then join
b) join and then build hierarchy

in order to try and determine that, we'd need to know what some of those subresults would look like.

hierachy with many-to-many

A reader, September 08, 2008 - 5:37 pm UTC

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.
Tom Kyte
September 09, 2008 - 7:25 am UTC

I see nothing here to support a hierarchy.

I see a join perhaps, but that is trivial. I don't see any "parent child" columns to have a hierarchy appear from

qustion about nocycle

James Su, September 12, 2008 - 10:45 pm UTC

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.

question about NOCYCLE

James Su, September 17, 2008 - 11:59 am UTC

Dear Tom, I know you are very busy, but could you please take a look at the question above? Thank you!
Tom Kyte
September 17, 2008 - 12:17 pm UTC

the immediate child of a root is treated differently than one further down.

start with A.

a connects to b
   b connects to A and C


we can now detect there is a cycle from b to a.
and looking forward, we can detect there is a cycle from b to c as well.


if you insert another row:
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


that is almost like starting with A, since x -> A

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



but note that B connecting to A isn't there this time, it wasn't an immediate child of the root.

how to calculate accumulative balance

abu abdullah, October 25, 2008 - 9:33 am UTC

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.

Tom Kyte
October 25, 2008 - 6:42 pm UTC

no create
no inserts
no look

how can I do the balance of accounts ?

abu mahmoud, October 26, 2008 - 9:34 am UTC

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.

Tom Kyte
October 26, 2008 - 10:33 am UTC

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.

abu mahmoud, October 27, 2008 - 5:07 am UTC

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.

Tom Kyte
October 27, 2008 - 8:07 am UTC




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  /



the bit in bold just sums up the balanced for the current record and all of it's children - that is all.

Can we get to the PArent Table

Bhushan, November 19, 2008 - 2:49 am UTC

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
Tom Kyte
November 24, 2008 - 10:32 am UTC

but z could have a foreign key to x and the cycle begins.... cycles make this "hard"

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.


In Addition to above COntent

Bhushan, November 21, 2008 - 1:40 am UTC

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
Tom Kyte
November 24, 2008 - 1:40 pm UTC

see the asktom home page - my schedule is there...

heirarchical question

Ian, December 09, 2008 - 1:21 am UTC

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

Tom Kyte
December 09, 2008 - 2:04 pm UTC

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.

Ian, December 09, 2008 - 6:25 pm UTC

Many thanks, obvious when you see it

Craig, December 12, 2008 - 11:33 am UTC

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
Tom Kyte
December 12, 2008 - 4:05 pm UTC

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.


parent & child records

ali, December 14, 2008 - 2:15 pm UTC

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
Tom Kyte
December 29, 2008 - 9:39 am UTC

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.

re: parent & child records

Stew Ashton, December 29, 2008 - 10:11 am UTC


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.
Tom Kyte
December 29, 2008 - 3:42 pm UTC

indeed I did!!!


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.


good catch, thanks!

on hierarchical queries

jvs, May 27, 2009 - 2:34 pm UTC

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.
Tom Kyte
May 27, 2009 - 3:54 pm UTC

see all of the steps with CR=0, you can effectively "drop them from your mind, they didn't happen, they are just there - a side effect, artifact of the plan output - but they never happened"


thanks for the answer but some more help.

jvs, May 28, 2009 - 12:10 am UTC

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
Tom Kyte
May 28, 2009 - 7:18 am UTC

you lost the indentation - however, you know for a fact it was not three times - 7x3 = 21, 21 > 17, 17 is total number of IO's.

the CR= numbers rollup the "tree"

hierarchical query with bind

Lasse, February 06, 2010 - 7:47 am UTC

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?
Tom Kyte
February 09, 2010 - 6:48 pm UTC

... I got a query that works fine using sys_connect_by_path in 8i after setting the "_new_connect_by_enabled=true". ...

do not do that, it was not ready for prime time - that is why it was hidden and not released. You will get "interesting" results from it. So just stop.


Oh wait, you already KNOW THAT.

There cannot be a bug here, the feature does not exist in the ancient software you are using.

hierarchical query with bind continue

Lasse, February 06, 2010 - 11:56 am UTC

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 = ?

Tom Kyte
February 09, 2010 - 6:50 pm UTC

no bug, the feature does not exist in your release. I would strongly encourage you to not use it, you will hit other issues such as "hey, that isn't the right set of rows" as an example.

hierarchical query with bind continue

Lasse, February 10, 2010 - 8:40 am UTC

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 :-)
Tom Kyte
February 15, 2010 - 3:43 pm UTC

it will be a procedural approach since 8i just simply did not have that functionality.

Filter Parent Rows if there are Children?

Richard, March 03, 2010 - 2:29 pm UTC

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.

Tom Kyte
March 04, 2010 - 7:10 am UTC

do you mean like this?

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.


keep anything that is more than level 1 OR is the terminal leaf node?

Re: Filter Parent Rows if there are Children?

Richard, March 03, 2010 - 2:47 pm UTC

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

Valid connect by?

A reader, March 11, 2010 - 7:40 am UTC

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.


Tom Kyte
March 11, 2010 - 8:39 am UTC

it is missing a ) so no, it is not correct in that sense.

But in general, yes, it makes "sense".

You are saying:

select * from slot where slotid in (SET)

and it just happens that SET is built using a hierarchy, perfectly normal and acceptable.


What does it semantically mean? It means "return all rows from slot such that slotid is in this SET. This set is ...." - you fill in the ....


Consider this:

select * from dept
where deptno in
(select deptno from emp
start with ename = :X
connect by prior empno = mgr)


That query semantically means

report on all department such that the deptno is in the set of deptnos for all employees that work for the employee whose name is :x


If you put in KING, it would return deptno 10, 20, 30 (but not 40) since everyone works for KING

If you put in BLAKE, you get a smaller set of deptnos

and so on...

Hans-Peter, March 11, 2010 - 12:35 pm UTC

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)

Tom Kyte
March 12, 2010 - 2:32 pm UTC

sure it is - the table in the subquery (not the 'main' select, that is an ambiguous term) has access to all of the SL.* columns - all of them. They are constant for an execution of the subquery in this case - but they are there nonetheless.

You might agree with me that this is sensible:

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  /


I used dept.deptno in the where clause of this correlated subquery.


Now, there is a huge difference between "syntactically correct" and "semantically correct". The above might be syntactically correct, but unless you tell us the QUESTION it was to answer - we cannot tell you if it is semantically sensible (if it in fact answers that question...)

Heirarchical Queries in views, Without START WITH

vikram, April 20, 2010 - 8:09 am UTC

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
Tom Kyte
April 20, 2010 - 9:00 am UTC

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

then you won't be doing this. sorry.


Suggest you use a stored procedure that returns a ref cursor instead, in there - you can apply whatever logic you like to construct the appropriate query using bind variables.

A reader, April 28, 2011 - 11:38 pm UTC

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.

Any efficient way to do this? (oracle 9i)

MAX_MIN_CONNECT, May 17, 2011 - 4:05 am UTC

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')
/



Tom Kyte
May 18, 2011 - 9:21 am UTC

he subquery below will go thru almost all the records in the table,

why do you think that? I see a scalar subquery "ULTIMATE_PID" that has a well defined start with and connect by - it won't hit the entire table?


If you get Oracle XE - you too can have a database that you can play with to your hearts content by the way...

Any efficient way to do this? (oracle 9i)

MAX_MIN_CONNECT, May 19, 2011 - 10:58 pm UTC

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'

DISPLAY USER ACCESS FORMS IN TREE

Syed Adeel Ali, July 06, 2011 - 2:12 am UTC

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

cleanup schema but constraints are in between,

A reader, October 30, 2011 - 5:37 pm UTC

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.


Tom Kyte
October 31, 2011 - 11:19 am UTC

why your data modeler wouldn't give you a drop/alter/create script to modify the schema is beyond me - that is beyond whacky. No way that would be acceptable (to me). They need to fully document what these changes are and WHY these changes are - so they have to know what the individual changes are (if they do not, please stop calling them a data modeler - just call them trouble).


Are they giving you the full DDL of the entire schema? that is what it sounds like - just drop and recreate your temporary schema

follow up,

A reader, October 31, 2011 - 11:40 am UTC

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,

Tom Kyte
October 31, 2011 - 12:14 pm UTC

the data modeler needs to use a tool that supports - ooh, i don't know - a business? With more than one person?


You only need to do it in development - in development you generate the ALTER script that you'll use in all of the other environments - you ultimately need what this data modeler should be giving you - a verified, complete, documented and checked into your source code control system - script to implement the change for this new version.

I have a question on Hierarchical Query

Maddy, November 02, 2011 - 4:42 pm UTC

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.

Tom Kyte
November 03, 2011 - 12:54 am UTC

you'll have to tell me how to put the data together something - how would you do it procedurally.

I've seen lots of peoples concept of "hierarchies" modeled over time - but I've not seen one like yours yet, I do not see how it all goes back together.

The 'normal' way is to just have:

create table t ( id number primary key, parentid references t, data .... );


There would never be a level in there (that is implied data, we get the level based on - well - what level you end up on in the hierarchy). And the parentid pointing to an id makes it rather clear how to assemble the data.

Table for above Query

Maddy, November 02, 2011 - 4:45 pm UTC

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
Tom Kyte
November 03, 2011 - 12:59 am UTC

that is not table data.

you would have to provide create tables, inserts into that table. something easy for me to cut and paste.

and a lot of detail that describes how your data actually works because it makes no sense to me. I'm hoping we are not connecting by substr's of what look to be numbers - but I'm afraid we are (that would be about the wrongest way I've ever seen to represent a hierarchy ever)

Actual Table and Scripts for Above

Maddy, November 03, 2011 - 2:09 pm UTC

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

Tom Kyte
November 03, 2011 - 3:11 pm UTC

Ok, so this schema is a BIT DIFFERENT from the other schema (as in, they look nothing remotely like each other at all, not even a teeny tiny bit - so one wonders - what was the other question about ?)

Please describe how you got to your output. Why does A/B/C be a parent of D,E,F. Why isn't A/B a parent of C,D,E,F or why isn't A/B/C/D a parent of E,F instead. What LOGIC drive you to figure out what the parent column of your output is and what goes into the child colum. It looks entirely arbitrary to me.

Maddy, November 03, 2011 - 3:27 pm UTC

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...
Tom Kyte
November 03, 2011 - 3:44 pm UTC

if you need an image to write a specification - you are in the wrong business. We write specifications so we can write code.

So, I'll ask again - what is the specification here.

You wrote:

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


so what? A (child B) - B has child C & G & D & E & F. So why don't we print out A/B -> C, G, D, E, F


Is the rule "print out the hierarchy until you get to a leaf such that it only has ONE child and all of the children of that child have one child"?

What if

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)



What if F has children X and Y - what would the output be then? What if D has child X and Y as well?

What if in addition to that E has child X and Y and Z and M and N

And N has child OO
and M and has children RR and SS?


write a *specfication*, write the rules, write a complete description of what is supposed to happen.

While pictures are worth a thousand words - they also tend to leave out a million other important words. A picture tells the story of a single scene. We need to know the MOVIE.


Missed an Insert Statement

Maddy, November 03, 2011 - 3:28 pm UTC

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

Maddy, November 03, 2011 - 4:09 pm UTC

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.....
Tom Kyte
November 04, 2011 - 1:13 am UTC

you've got to be kidding. How did we get from the review two above, to the one directly above to here? This is *completely and utterly different from the one above* - which in turn has no relationship to the one above it. This is an entirely NEW question.

You have not told me how you know A/B/C is the header and how you know D,E,F are the children.

You have not supplied the real table.

This is as clear as mud in water stirred with a stick.


connect by

A reader, November 29, 2011 - 5:59 pm UTC

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 ?


Tom Kyte
November 30, 2011 - 7:11 am UTC

it always depends.

benchmark it. that is what I would do, that is what I do here all of the time.


In general, they should be typically equivalent. Any differences are probably transient (meaning - if you have a with query that blows away a connect by, or vice versa, I would fully expect to see that change in a version or two (or a dot release or two))

Performance??

Dhruva, December 29, 2011 - 8:05 am UTC

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
Tom Kyte
December 29, 2011 - 11:33 am UTC

and the given query is running like a dog.

define that, last I looked, greyhounds ran really fast.

What is your definition of slow.
What is 1,000,000 rows - is that 1mb or 100mb or 1gb or 1tb?

Your query does not do this:
The requirement is to identify if a leaf node exists for t3 matching the input variable v2.

A leaf node is a node such that it has no child rows. Now, unless your model only goes one level deep - your query does not do that.

How many rows does:


START WITH t3 = :v2


retrieve on average/minimum/maximum?


You cannot just move the "level >1" around, it changes the results:

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.



an example like this demands a create table and inserts to work with.

Performance ??

Rajeshwaran Jeyabal, January 02, 2012 - 10:02 pm UTC

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>

Tom Kyte
January 03, 2012 - 11:25 am UTC

or, we might actually wait for them to explain what they meant.

Found the answer (I think)

Dhruva, January 03, 2012 - 5:23 am UTC

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.

Parent and Grand parents of a record.

chandrapandian, July 01, 2013 - 9:38 am UTC

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?
Tom Kyte
July 01, 2013 - 9:30 pm UTC

describe the logic behind this. what is an "inherited" record - what sort of rules are you following here?

given your data, this would do it, but - your example is so trivial and your rules of logic are unknown so how knows if it'll work in real life:

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.

Parent and Grand parents and N Grand parents of a record

chandrapandian, July 01, 2013 - 11:42 am UTC

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 9
2 4
2 5
3 6
3 7


Amine Sekkai, July 01, 2013 - 9:40 pm UTC

Need same kind of result for me

Durga prasad, July 12, 2013 - 8:11 pm UTC

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,

Tom Kyte
July 16, 2013 - 4:18 pm UTC

search this site for PIVOT.

no create
no inserts
no sql for you from me.


ok, well if we pretend that (select distinct job ename, deptno from scott.emp) is your table - then you can:

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


@Durga prasad

A reader, July 22, 2013 - 1:11 pm UTC

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


Jess, August 31, 2016 - 10:20 am UTC

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!

Chris Saxon
September 01, 2016 - 3:43 am UTC

Maybe like this


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>


Jess, August 31, 2016 - 12:04 pm UTC

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...

Jess, August 31, 2016 - 12:14 pm UTC

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

Jess, September 08, 2016 - 11:07 am UTC

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.



Chris Saxon
September 08, 2016 - 4:41 pm UTC

The data you've supplied doesn't quite seem to match the output you're expecting.

In any case, here's one way to tackle the problem:

- Join all the tables together
- Unpivot the columns, so you get rows for the employee and department columns
- Filter the results so you only have one row / employee infraction code

Because the columns don't fully line up, you'll need to create some fake columns to unpivot by. These will just be null.

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


Chris

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library