Desired features are in 9i
Pichaimani, July 19, 2001 - 9:42 pm UTC
Thanks a lot if all these features had been available earlier itself I could have eliminated the need for
runtime tables(which are populated and deleted.)
Creating hierarchial tree over a view
Shatha, February 26, 2002 - 2:34 am UTC
The question "Creating hierarchial tree over a view " was answered using Oracle9i and didn't answer if it could be done using Oracle8.
February 26, 2002 - 9:01 am UTC
re-read the very first sentence:
This is a feature of 9i. Prior to that, you quite simply cannot connect by a
join.
I answered it.
Creating hierarchial tree over a view
Harvey, February 26, 2002 - 3:28 pm UTC
A question about functions lpad and rpad in this case. I tried lpad and rpad here, and they give the same output. Could you tell why?
Thanks!
Harvey
February 26, 2002 - 4:43 pm UTC
because
lpad( '*', 32, '*' ) = rpad( '*', 32, '*' )
thats why.... it doesn't matter if you put 31 *'s on the LEFT or the RIGHT of a single *, you get 32 *'s
;)
Creating hierarchial tree over a view
viswanath, February 27, 2002 - 1:30 pm UTC
Problem Description:
====================
The following error occurs when the user executes a select statement that
contains a connect by clause:
ORA-1437: Cannot have join with connect by statement.
Problem Explanation:
====================
The connect by clause is used in a tree-structured query, where the returned
records are organized by their relationship with one another.
Solution Description:
====================
The select statement is attempting a tree-structured query on a view which is,
in turn, based upon a join. There cannot be a join across the CONNECT BY. The
hierarchical query can only organize rows of a single table. This is a SQL
limitation.
Workaround: Create a view with the connect by, then join with another table.
Example:
The following view is based upon Example III p.3-364 SQL Oracle 7 Server
SQL*Language Reference Manual. It uses the EMP table as the hierarchical
query source, then is followed by a select statement that joins it with the
DEPT table. This join would work with any number of tree-structured views.
SQL>
Create view ORG as
SELECT
LPAD(' ',2*(LEVEL -1)) || ename Org_chart, empno, mgr, job, deptno
FROM emp
START WITH
job = 'PRESIDENT'
CONNECT BY PRIOR empno=mgr; /* Retrieve the record only
/* if the prior empno value
/* (in the previous returned row)
/* had the same value as this
/* row's mgr number
Use the following join condition to get the second table's information.
SQL>
SELECT org_chart, empno, mgr, job, o.deptno, dname FROM org O, dept D WHERE
O.deptno = D.deptno;
Solution Explanation:
====================
The error occurs because the user is attempting to execute a select statement
on a view which is the product of a join of multiple tables. The select
statement fails because the CONNECT BY cannot integrate two separate tables.
This is a SQL limitation.
Workaround:
By joining after creating a view with a CONNECT BY clause, no error results.
However, only one table can be hierarchically organized. The user can create
several tree structured views separately and then join them together with a
select statement.
February 27, 2002 - 3:48 pm UTC
That SOLUTION is no SOLUTION.
If you do that -- the optimizer is 100% free to totally destroy your hierarchy! You are concerned with keeping the hierarchy right? Well, there is NOTHING that says the order of the hierarchy in the view will be preserved. the author of that note was mistaken in their belief.
Here is a simple proof showing this technique is flawed. DO NOT use this approach in your applications as some day you will in fact get the WRONG answer (well, you'll think it is wrong, it'll be correct, just not what you expected)
ops$tkyte@ORA817DEV.US.ORACLE.COM> create table t1 ( id int, pid int, fk int );
Table created.
ops$tkyte@ORA817DEV.US.ORACLE.COM> create table t2 ( pk int );
Table created.
ops$tkyte@ORA817DEV.US.ORACLE.COM>
ops$tkyte@ORA817DEV.US.ORACLE.COM> insert into t1 values ( 1, null, 4 );
1 row created.
ops$tkyte@ORA817DEV.US.ORACLE.COM> insert into t1 values ( 2, 1, 3 );
1 row created.
ops$tkyte@ORA817DEV.US.ORACLE.COM> insert into t1 values ( 3, 1, 2 );
1 row created.
ops$tkyte@ORA817DEV.US.ORACLE.COM> insert into t1 values ( 4, 2, 1 );
1 row created.
ops$tkyte@ORA817DEV.US.ORACLE.COM>
ops$tkyte@ORA817DEV.US.ORACLE.COM> insert into t2 values ( 1 );
1 row created.
ops$tkyte@ORA817DEV.US.ORACLE.COM> insert into t2 values ( 2 );
1 row created.
ops$tkyte@ORA817DEV.US.ORACLE.COM> insert into t2 values ( 3 );
1 row created.
ops$tkyte@ORA817DEV.US.ORACLE.COM> insert into t2 values ( 4 );
1 row created.
ops$tkyte@ORA817DEV.US.ORACLE.COM>
ops$tkyte@ORA817DEV.US.ORACLE.COM>
ops$tkyte@ORA817DEV.US.ORACLE.COM> column idstr format a20
ops$tkyte@ORA817DEV.US.ORACLE.COM> create or replace view v
2 as
3 select rpad( '*', level*2, '*' ) || '-' || id idstr, pid, fk, level lvl
4 from t1
5 connect by prior id = pid
6 start with pid is null
7 /
View created.
ops$tkyte@ORA817DEV.US.ORACLE.COM>
ops$tkyte@ORA817DEV.US.ORACLE.COM> select * from v;
IDSTR PID FK LVL
-------------------- ---------- ---------- ----------
**-1 4 1
****-2 1 3 2
******-4 2 1 3
****-3 1 2 2
ops$tkyte@ORA817DEV.US.ORACLE.COM>
ops$tkyte@ORA817DEV.US.ORACLE.COM> select * from v, t2 where v.fk = t2.pk;
IDSTR PID FK LVL PK
-------------------- ---------- ---------- ---------- ----------
******-4 2 1 3 1
****-3 1 2 2 2
****-2 1 3 2 3
**-1 4 1 4
See, select from the view and the hierarchy is there. join the view and bamm -- there it goes -- the hierarchy is totally lost.
Pls try this
LCoppola, February 27, 2002 - 5:16 pm UTC
--Include RowNum w/in the view creation
--and order by of the select/join
create table t1 ( id int, pid int, fk int );
create table t2 ( pk int );
insert into t1 values ( 1, null, 4 );
insert into t1 values ( 2, 1,3 );
insert into t1 values ( 3, 1,2 );
insert into t1 values ( 4, 2,1 );
insert into t2 values ( 1 );
insert into t2 values ( 2 );
insert into t2 values ( 3 );
insert into t2 values ( 4 );
create or replace view v
as
select rownum rnum,rpad( '*', level*2, '*' ) || '-' || id idstr, pid, fk, level lvl
from t1
connect by prior id = pid
start with pid is null
column idstr format a20
column rnum format a5
select * from v;
select * from v, t2 where v.fk = t2.pk order by rnum;
Table created.
Table created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
RNUM IDSTR PID FK LVL
---------- -------------------- --------- --------- ---------
########## **-1 4 1
########## ****-2 1 3 2
########## ******-4 2 1 3
########## ****-3 1 2 2
RNUM IDSTR PID FK LVL PK
---------- -------------------- --------- --------- --------- ---------
########## **-1 4 1 4
########## ****-2 1 3 2 3
########## ******-4 2 1 3 1
########## ****-3 1 2 2 2
February 28, 2002 - 9:15 am UTC
sure, now -- do it on a sizable result set.....
Including rownum in the view forces the ENTIRE view to be materialized and then joined to. Works great on a piddling amount of data, works really really slow on normal sized result sets.
Addt'l Comments
LCoppola, February 28, 2002 - 10:59 am UTC
Yes, I agree with your reply.
I created a materialized view using a similar setup (embedding rownum w/in materialized view) and then joining materialized view and other tables in other queries as needed
Addt'l Info
LCoppola, February 28, 2002 - 11:01 am UTC
Pls note that my materialized view's result set contained less than 150 rows.
how about this?
Alex Rakhalski, July 18, 2002 - 8:21 am UTC
CREATE OR REPLACE VIEW v (
idstr,
pid,
fk,
lvl )
AS
SELECT
RPAD( '*', LEVEL*2, '*' ) || '-' || id idstr
, pid
, fk
, LEVEL lvl
FROM t1
CONNECT BY PRIOR id = pid
START WITH pid IS NULL/
(view WITHOUT ROWNUM)
SELECT /*+ ORDERED*/ *
FROM v, t2
WHERE v.fk = t2.pk
July 18, 2002 - 8:54 am UTC
what about it? there is no order by, the data will come out as we see fit -- subject to change whenever we feel like it.
You want an ordered hierarchy? In 8i, better do it procedurally. In 9i, order by siblings.
Everything else is like a house of cards built on quicksand (and an earthquake is coming)
don't understand
Alex Rakhalski, July 18, 2002 - 10:02 am UTC
Tom, you means new release of Oracle server and so on? (Because this approach not documented nor supported and so on )
July 18, 2002 - 10:39 am UTC
No, I mean because you seem to think that your query above will return data in some specific order.
It won't.
It doesn't have to.
It can return the data however it feels.
It might work in some cases, not others.
It might work one way one day, another the next.
It is unreliable.
What about this?
Michael, July 22, 2002 - 7:33 am UTC
In 8.1.7:
select level, ename, dname
from emp, dept
where emp.deptno = dept.deptno
start with mgr is null connect by prior empno = mgr;
ORA-01437: cannot have join with CONNECT BY
Now with the join in the select:
select level, ename,
(select dname
from dept
where dept.deptno = emp.deptno) dname
from emp
start with mgr is null connect by prior empno = mgr;
LEVEL ENAME DNAME
---------- ---------- --------------
1 KING ACCOUNTING
2 JONES RESEARCH
3 SCOTT RESEARCH
4 ADAMS RESEARCH
3 FORD RESEARCH
4 SMITH RESEARCH
2 BLAKE SALES
3 ALLEN SALES
3 WARD SALES
3 MARTIN SALES
3 TURNER SALES
3 JAMES SALES
2 CLARK ACCOUNTING
3 MILLER ACCOUNTING
Perhaps with helps in some cases.
July 22, 2002 - 9:53 am UTC
absolutely. if all you need is dependent data that works great.
If you need the join to filter the data, that'll not work tho.
This hierarchical query isn't working
ht, August 15, 2003 - 7:13 pm UTC
Tom,
I'm trying to query a user and title table to display a hierarchy. Can you help me figure out what I'm missing?
Thank you.
Oracle9i Enterprise Edition Release 9.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.3.0 - Production
Enter password:
Connected.
>
>drop table tuser;
Table dropped.
>create table tuser
2 (
3 id number,
4 name varchar2(10),
5 reportsto_id number,
6 title_id number
7 );
Table created.
>
>insert into tuser values(1,'user1',null,1);
1 row created.
>insert into tuser values(2,'user2',1,2);
1 row created.
>insert into tuser values(3,'user3',2,3);
1 row created.
>insert into tuser values(4,'user4',3,4);
1 row created.
>insert into tuser values(5,'user5',3,4);
1 row created.
>
>drop table ttitle;
Table dropped.
>create table ttitle
2 (
3 id number,
4 name varchar2(10)
5 );
Table created.
>
>insert into ttitle values(1,'VP');
1 row created.
>insert into ttitle values(2,'Dir');
1 row created.
>insert into ttitle values(3,'Mgr');
1 row created.
>insert into ttitle values(4,'DBA');
1 row created.
>
>select
2 u.id,
3 lpad('x',7 *(level-1),'x')||u.name,
4 level,
5 level-1,
6 t.name title,
7 t.id title_id,
8 u.reportsto_id
9 from
10 tuser u,
11 ttitle t
12 where
13 u.title_id=t.id
14 start with u.reportsto_id = null
15 connect by prior u.id=u.reportsto_id
16 order siblings by u.name;
no rows selected
August 15, 2003 - 8:30 pm UTC
this demo will help:
ops$tkyte@ORA920> create table t ( x int );
Table created.
ops$tkyte@ORA920>
ops$tkyte@ORA920> insert into t values(NULL);
1 row created.
ops$tkyte@ORA920> insert into t values(1);
1 row created.
ops$tkyte@ORA920> commit;
Commit complete.
ops$tkyte@ORA920>
ops$tkyte@ORA920>
ops$tkyte@ORA920> select * from t where x = null;
no rows selected
ops$tkyte@ORA920> select * from t where x <> null;
no rows selected
ops$tkyte@ORA920> select * from t where x IS NULL;
X
----------
ops$tkyte@ORA920> select * from t where x IS NOT NULL;
X
----------
1
ops$tkyte@ORA920>
<b>nothing but nothing is equal to null.
nothing but nothing is NOT equal to null.
things "are null"
things "are not null"</b>
try starting with "reportsto_id is null"
ht, August 15, 2003 - 7:18 pm UTC
Figured it out, changed the "=null" to "is null".
Hierarchical query not working.
ht, August 16, 2003 - 12:06 am UTC
Tom,
I know I'm missing something obvious here but I'm not able to figure out why my "lpad" doesn't display correctly. If I remove the ttitle part of the query, everything works fine.
Thanks,
ht
SQL*Plus: Release 9.2.0.3.0 - Production on Fri Aug 15 21:03:10 2003
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.3.0 - Production
Enter password:
Connected.
>drop table tname;
Table dropped.
>drop table ttitle;
Table dropped.
>
>create table ttitle
2 (
3 id number constraint pk_ttitle primary key disable,
4 name varchar2(50) constraint uq_ttitle1 unique
5 )
6 tablespace users
7 storage
8 (
9 initial 1m
10 next 1m
11 maxextents unlimited
12 pctincrease 0
13 );
Table created.
>
>alter table ttitle enable constraint pk_ttitle using index
2 tablespace indx
3 storage
4 (
5 initial 100k
6 next 100k
7 maxextents unlimited
8 pctincrease 0
9 );
Table altered.
>
>drop sequence ttitleseq;
Sequence dropped.
>
>create sequence ttitleseq start with 1 increment by 1;
Sequence created.
>-------------------------------------------------------------------
>insert into ttitle (id,name)
2 values (ttitleseq.nextval,'title1');
1 row created.
>insert into ttitle (id,name)
2 values (ttitleseq.nextval,'title2');
1 row created.
>insert into ttitle (id,name)
2 values (ttitleseq.nextval,'title3');
1 row created.
>-------------------------------------------------------------------
>
>create table tname
2 (
3 id number constraint pk_tname primary key disable,
4 username varchar2(25),
5 reportsto_id number default 1,
6 title_id constraint fk_ttitle references ttitle(id)
7 )
8 tablespace users
9 storage
10 (
11 initial 1m
12 next 1m
13 maxextents unlimited
14 pctincrease 0
15 );
Table created.
>
>--alter table tname modify title_id default 1;
>
>alter table tname enable constraint pk_tname using index
2 tablespace indx
3 storage
4 (
5 initial 100k
6 next 100k
7 maxextents unlimited
8 pctincrease 0
9 );
Table altered.
>
>drop sequence tnameseq;
Sequence dropped.
>
>create sequence tnameseq start with 1 increment by 1;
Sequence created.
>-------------------------------------------------------------------
>insert into tname (id,username,title_id,reportsto_id) values (tnameseq.nextval,'user1',1,null);
1 row created.
>insert into tname (id,username,title_id,reportsto_id) values (tnameseq.nextval,'user2',2,1);
1 row created.
>insert into tname (id,username,title_id,reportsto_id) values (tnameseq.nextval,'user3',3,2);
1 row created.
>-------------------------------------------------------------------
> select
2 n.id,
3 lpad('.',7 *(level-1),'.')||n.username username,
4 level,
5 t.name title
6 from
7 tname n,
8 ttitle t
9 where
10 n.title_id=t.id(+)
11 start with n.reportsto_id is null
12 connect by prior n.id=n.reportsto_id
13 order siblings by username ;
ID
----------
USERNAME
--------------------------------------------------------------------------------
LEVEL
----------
TITLE
--------------------------------------------------------------------------------
1
user1
1
title1
2
user2
2
title2
3
user3
3
title3
3 rows selected.
August 16, 2003 - 8:42 am UTC
use a different alias, it was a bug. try ||n.username uname
Hierarchy query still doesn't work with another alias.
ht, August 16, 2003 - 10:04 am UTC
Hi Tom,
I used another column alias but the same results are returned.
Thanks,
ht
>-------------------------------------------------------------------
> select
2 n.id,
3 lpad('.',7 *(level-1),'.')||n.username xxxname,
4 level,
5 t.name title
6 from
7 tname n,
8 ttitle t
9 where
10 n.title_id=t.id(+)
11 start with n.reportsto_id is null
12 connect by prior n.id=n.reportsto_id
13 order siblings by xxxname;
ID
----------
XXXNAME
--------------------------------------------------------------------------------
LEVEL
----------
TITLE
--------------------------------------------------------------------------------
1
user1
1
title1
2
user2
2
title2
3
user3
3
title3
August 16, 2003 - 10:53 am UTC
i meant just the correlation name -- not the order by siblings clause -- that is the problem. consider:
ops$tkyte@ORA920> select n.id,
2 lpad('.',7 *(level-1),'.')||n.username username,
3 lpad('.',7 *(level-1),'.')||n.username xxxname,
4 level,
5 t.name title
6 from tname n,
7 ttitle t
8 where n.title_id=t.id(+)
9 start with n.reportsto_id is null
10 connect by prior n.id=n.reportsto_id
11 order siblings by username;
ID USERNAME XXXNAME LEVEL TITLE
--- --------------- ------------------------- ----- ------
1 user1 user1 1 title1
2 user2 .......user2 2 title2
3 user3 ..............user3 3 title3
order siblings by username
select xxxname
that is really what you want anyway.
You want to order by username (not with the lpads, that would screw up the sort!) but show xxxname. If it did what you asked -- it would be giving you data sorted not as you expected!
Hierarchical query works without a join.
ht, August 16, 2003 - 10:21 am UTC
Tom,
The query works if 1 table is queried, not 2. Is there something preventing me from querying 2 tables in a hierarchical query? Below, I created table tname2 and added the title column.
Thanks for your help with this. It's been stumping me for a couple days.
ht
>select * from tname2;
ID USERNAME REPORTSTO_ID TITLE_ID TITLE
---------- ---------- ------------ ---------- ----------
1 user1 1 title1
2 user2 1 2 title2
3 user3 2 3 title3
3 rows selected.
>select
2 n.id,
3 lpad('.',7 *(level-1),'.')||n.username xxxname,
4 level,
5 n.title title
6 from
7 tname2 n
8 start with n.reportsto_id is null
9 connect by prior n.id=n.reportsto_id
10 order siblings by xxxname;
ID XXXNAME LEVEL TITLE
---------- -------------------- ---------- ----------
1 user1 1 title1
2 .......user2 2 title2
3 ..............user3 3 title3
3 rows selected.
>select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.3.0 - 64bit Production
PL/SQL Release 9.2.0.3.0 - Production
CORE 9.2.0.3.0 Production
TNS for Solaris: Version 9.2.0.3.0 - Production
NLSRTL Version 9.2.0.3.0 - Production
5 rows selected.
Even using a view on 2 tables doesn't fix the hierarchical query.
ht, August 16, 2003 - 1:50 pm UTC
Tom,
Even creating a view on the 2 tables doesn't seem to allow for a formatted display. Do you have any ideas on what's causing this problem? I won't be able to move the user's title info into the user table so I'm looking for some sort of workaround.
Thanks,
ht
>create view tview1 as
2 select n.id id,n.username username,n.reportsto_id reportsto_id,t.name title
3 from ttitle t, tname n
4 where n.title_id=t.id;
View created.
> select
2 id,
3 lpad('.',7 *(level-1),'.')||username xxxname,
4 level,
5 title
6 from
7 tview1
8 start with reportsto_id is null
9 connect by prior id=reportsto_id
10 order siblings by xxxname;
ID
----------
XXXNAME
--------------------------------------------------------------------------------
LEVEL
----------
TITLE
--------------------------------------------------------------------------------
1
user1
1
title1
2
user2
2
title2
3
user3
3
title3
August 16, 2003 - 1:59 pm UTC
please -- read the above -- you do NOT want to order by xxxname, you want to order by username!!!!!
Thanks.
ht, August 16, 2003 - 2:30 pm UTC
Tom,
Thanks, as usual, you fixed the problem. The strange thing is that I still can't figure out why I encountered this error. Maybe it's because of the data I populated in the tables? In other words, using the column alias worked fine in schema1.1 but, when I built schema1.2, it broke.
I had this code in both schemas:
... lpad('.',7 *(level-1),'.')||p.fname||' '||p.lname personname,...
...order by siblings personname...
By using ...order by siblings p.fname,p.lname... in both schemas, the results are as I expect.
Thanks again and have a good weekend.
ht
didn't refresh my page
ht, August 16, 2003 - 2:33 pm UTC
Tom,
I just read your reply, I should refresh my page before submitting future questions.
Thanks for the explanation.
ht
Prior to 9i
Jagjeet Singh, August 21, 2003 - 8:02 am UTC
hello sir ..
I was trying to do the same code in ORACLE 8.1.5 ..
Please tell me ..whether this is the right way or not ?
SQL>
SQL> Create or replace function Tree (
2 p_ename varchar2,
3 p_mgr number,
4 p_level number
5 )
6 return varchar2
7 as
8 v_ret varchar2(80) ;
9 v_ename varchar2(30);
10 v_empno number;
11 v_mgr number;
12 begin
13 ---
14 If p_level = 1 then
15 V_ret := p_ename ;
16 Else
17 Select ename,empno,mgr into v_ename,v_empno,v_mgr from emp where empno = p_mgr ;
18 V_ret := tree(v_ename||'/'||p_ename,v_mgr,p_level-1);
19 End if;
20 ---
21 return v_ret;
22 end;
23 /
Function created.
SQL> col ename for a30
SQL> col tree for a30
SQL>
SQL>
SQL> select level,rpad(' ',(level*3)+2,' ')||ename ename,tree(ename,mgr,level) Tree from emp
2 connect by prior empno=mgr
3 start with ename = 'KING'
4 /
LEVEL ENAME TREE
---------- ------------------------------ ------------------------------
1 KING KING
2 JONES KING/JONES
3 SCOTT KING/JONES/SCOTT
4 ADAMS KING/JONES/SCOTT/ADAMS
3 FORD KING/JONES/FORD
4 SMITH KING/JONES/FORD/SMITH
2 BLAKE KING/BLAKE
3 ALLEN KING/BLAKE/ALLEN
3 WARD KING/BLAKE/WARD
3 MARTIN KING/BLAKE/MARTIN
3 TURNER KING/BLAKE/TURNER
3 JAMES KING/BLAKE/JAMES
2 CLARK KING/CLARK
3 MILLER KING/CLARK/MILLER
14 rows selected.
SQL>
SQL>
SQL> select level,rpad(' ',(level*3)+2,' ')||ename ename,tree(ename,mgr,level) Tree from emp
2 connect by prior empno=mgr
3 start with ename = '&Ename';
Enter value for ename:JONES
LEVEL ENAME TREE
---------- ------------------------------ ------------------------------
1 JONES JONES
2 SCOTT JONES/SCOTT
3 ADAMS JONES/SCOTT/ADAMS
2 FORD JONES/FORD
3 SMITH JONES/FORD/SMITH
5 rows selected.
SQL> /
Enter value for ename: BLAKE
LEVEL ENAME TREE
---------- ------------------------------ ------------------------------
1 BLAKE BLAKE
2 ALLEN BLAKE/ALLEN
2 WARD BLAKE/WARD
2 MARTIN BLAKE/MARTIN
2 TURNER BLAKE/TURNER
2 JAMES BLAKE/JAMES
6 rows selected.
August 21, 2003 - 6:15 pm UTC
ops$tkyte@ORA920> create or replace function scbp( p_ename in varchar2 ) return varchar2
2 is
3 l_return varchar2(4000);
4 begin
5 for x in ( select ename
6 from emp
7 start with ename = p_ename
8 connect by prior mgr = empno )
9 loop
10 l_return := x.ename || '/' || l_return;
11 end loop;
12
13 return rtrim(l_return,'/');
14 end;
15 /
Function created.
ops$tkyte@ORA920>
ops$tkyte@ORA920> column tree format a30
ops$tkyte@ORA920> column ename format a20
ops$tkyte@ORA920>
ops$tkyte@ORA920> select level,rpad(' ',(level*3)+2,' ')||ename ename,scbp(ename) tree
2 from emp
3 connect by prior empno=mgr
4 start with ename = 'KING'
5 /
LEVEL ENAME TREE
---------- -------------------- ------------------------------
1 KING KING
2 JONES KING/JONES
3 SCOTT KING/JONES/SCOTT
4 ADAMS KING/JONES/SCOTT/ADAMS
3 FORD KING/JONES/FORD
4 SMITH KING/JONES/FORD/SMITH
2 BLAKE KING/BLAKE
3 ALLEN KING/BLAKE/ALLEN
3 WARD KING/BLAKE/WARD
3 MARTIN KING/BLAKE/MARTIN
3 TURNER KING/BLAKE/TURNER
3 JAMES KING/BLAKE/JAMES
2 CLARK KING/CLARK
3 MILLER KING/CLARK/MILLER
14 rows selected.
would be my approach.
Connect by with
Pooja, September 08, 2003 - 9:51 pm UTC
Hi Tom,
I've my child id and parent id in separate tables. What are my options prior to 9i if I need to get hierarchial structure starting from a parent?
I cannot avoid the joins as they are in two different tables and I need to join at least 4 tables to join child and parent ids.
Could you please suggest what would be the better approach in this case?
Thanks
Pooja
September 09, 2003 - 11:26 am UTC
how can you have them in separate tables?
Solution for 8i developers?
Scott Wesley, November 21, 2003 - 2:18 am UTC
G'day Tom
Do you see any issues with this approach?
15:17:40 SCOTT@devdb> create or replace view v
15:17:40 2 as
15:17:40 3 select rownum aha, rpad( '*', level*2, '*' ) || '-' || id idstr, pid, fk, level lvl
15:17:40 4 from t1
15:17:40 5 connect by prior id = pid
15:17:40 6 start with pid is null;
View created.
real: 47
15:17:40 SCOTT@devdb> select * from v;
AHA IDSTR PID FK LVL
---------- -------------------- ---------- ---------- ----------
1 **-1 4 1
2 ****-2 1 3 2
3 ******-4 2 1 3
4 ****-3 1 2 2
4 rows selected.
real: 16
15:17:40 SCOTT@devdb> select * from v, t2 where v.fk = t2.pk ;
AHA IDSTR PID FK LVL PK
---------- -------------------- ---------- ---------- ---------- ----------
3 ******-4 2 1 3 1
4 ****-3 1 2 2 2
2 ****-2 1 3 2 3
1 **-1 4 1 4
4 rows selected.
real: 15
15:17:48 SCOTT@devdb> i order by aha
15:17:54 SCOTT@devdb> /
AHA IDSTR PID FK LVL PK
---------- -------------------- ---------- ---------- ---------- ----------
1 **-1 4 1 4
2 ****-2 1 3 2 3
3 ******-4 2 1 3 1
4 ****-3 1 2 2 2
4 rows selected.
Thanks
November 21, 2003 - 4:47 pm UTC
that works -- as long as you always need the entire hierarchy.
scalar subqueries work dandy too.
scott@ORA920LAP> l
1 select rpad( '*',level*2,'*') || ename empname,
2 (select dname from dept where dept.deptno = emp.deptno)dname
3 from emp
4 start with mgr is null
5* connect by prior empno = mgr
scott@ORA920LAP> /
EMPNAME DNAME
--------------- --------------
**KING ACCOUNTING
****JONES RESEARCH
******SCOTT RESEARCH
********ADAMS RESEARCH
******FORD RESEARCH
********SMITH RESEARCH
****BLAKE SALES
******ALLEN SALES
******WARD SALES
******MARTIN SALES
******TURNER SALES
******JAMES SALES
****CLARK ACCOUNTING
******MILLER ACCOUNTING
14 rows selected.
Join makes hierarchical query slow
AKS, February 05, 2004 - 9:20 am UTC
Hi TOM,
I wrote a hierarchical query on joining the two tables that makes it very-very slow.
If i execute the join query that is much faster than hierarchical query.
My question is how the CONNECT BY works over a JOIN?
Here is my tables-
SQL> select count(1) from T1;
COUNT(1)
---------
2405037
SQL> select count(1) from T2;
COUNT(1)
---------
123070
select t1.col1 ,t2.col2
from t1 ,t2
where t1.id = t2.id
connect by prior t2.id = t2.id_parent
start with t2.id = 380054;
It goes very slow. How to improve it?
February 05, 2004 - 9:40 am UTC
umm, you cannot in any way shape or form compare an apple to a toaster oven and that is what you are trying to do here.
2.4 million records
vs
123k
You give no indication of how long "long" is or how fast you think it "should be".
counting 2.4 million records via an index lookup like you are doing could be "slow", sure.
you might consider:
select (select col1 from t1 where t1.id = t2.id), t2.col2
from t2
connect by prior id = id_parent
start with id = 380054;
an index on t2(id_parent,id,col2) and one on t2(id) would benefit this query.
CONNECT BY thread
Bob, February 18, 2004 - 7:43 pm UTC
Tom, as usual your examples and illumination of how Oracle works is dead on. However, there seems to be a dollop of backlash with those who don't quite get it. This may make it difficult for them to learn and does make it more difficult for me to enjoy the thread.
D'ya think you could lighten up a bit on the newbies?
February 19, 2004 - 7:26 am UTC
where was I harsh? and who was the newbie i was harsh on in this thread? (serious question -- i've reread this and don't see it)
what abou inverse
A reader, May 02, 2004 - 11:29 am UTC
if you have lost your mgr field emp table, but you have:
create table t ( x varchar2(50) );
insert into t values ( 'KING' );
insert into t values ( 'KING/JONES' );
insert into t values ( 'KING/JONES/SCOTT' );
insert into t values ( 'KING/JONES/SCOTT/ADAMS' );
insert into t values ( 'KING/JONES/FORD');
insert into t values ( 'KING/JONES/FORD/SMITH');
insert into t values ( 'KING/BLAKE' );
insert into t values ( 'KING/BLAKE/ALLEN');
insert into t values ( 'KING/BLAKE/WARD');
insert into t values ( 'KING/BLAKE/MARTIN');
insert into t values ( 'KING/BLAKE/TURNER');
insert into t values ( 'KING/BLAKE/JAMES');
insert into t values ( 'KING/CLARK');
insert into t values ( 'KING/CLARK/MILLER');
What would be your method to populate mgr back?
May 02, 2004 - 4:24 pm UTC
ops$tkyte@ORA9IR2> select x, mgr_name, (select empno from scott.emp where ename = mgr_name) mgr
2 from (
3 select x, substr( x, beg+1, end-beg-1 ) mgr_name
4 from (
5 select '/'||x x, instr( '/'||x, '/', -1, 1 ) end, instr( '/'||x, '/', -1, 2 ) beg
6 from t
7 )
8 )
9 /
X MGR_NAME MGR
-------------------------- -------------------------- ----------
/KING
/KING/JONES KING 7839
/KING/JONES/SCOTT JONES 7566
/KING/JONES/SCOTT/ADAMS SCOTT 7788
/KING/JONES/FORD JONES 7566
/KING/JONES/FORD/SMITH FORD 7902
/KING/BLAKE KING 7839
/KING/BLAKE/ALLEN BLAKE 7698
/KING/BLAKE/WARD BLAKE 7698
/KING/BLAKE/MARTIN BLAKE 7698
/KING/BLAKE/TURNER BLAKE 7698
/KING/BLAKE/JAMES BLAKE 7698
/KING/CLARK KING 7839
/KING/CLARK/MILLER CLARK 7782
14 rows selected.
beg - great!
A reader, May 02, 2004 - 5:18 pm UTC
Need to filter out records which don't have child nodes
Arun Gupta, August 24, 2004 - 7:08 pm UTC
Tom,
In a hierarchical query output, I want to leave out the root node records which do not have any child nodes. If there are child nodes present, then the complete hierarchical tree is required with "order siblings by..." clause. I did two rewrites but failed miserably when the number of records in the table were around 60,000.
SQL> drop table t1;
SQL> create table t1 (id number(5), id_parent number(5), code_type varchar2(4));
SQL> insert into t1 values (1, null, 'STD');
SQL> insert into t1 values (2, null, 'STD');
SQL> insert into t1 values (3, null, 'STD');
SQL> insert into t1 values (4, 3, 'STD');
SQL> insert into t1 values (5, 4, 'STD');
SQL> insert into t1 values (6, 3, 'STD');
SQL> insert into t1 values (7, 4, 'STD');
SQL> commit;
Commit complete.
SQL> select * from t1
2 start with id_parent is null
3 connect by prior id = id_parent
4 order siblings by id;
ID ID_PARENT CODE
---------- ---------- ----
1 STD
2 STD
3 STD
4 3 STD
5 4 STD
7 4 STD
6 3 STD
7 rows selected.
SQL>
In the example above, I want the output to show like:
ID ID_PARENT CODE
---------- ---------- ----
3 STD
4 3 STD
5 4 STD
7 4 STD
6 3 STD
The records with id =1 and 2 should be suppressed since they do not have child nodes.
Thanks
August 24, 2004 - 7:53 pm UTC
ops$tkyte@ORA9IR2> select id, id_parent, code_type
2 from t1
3 where ( level > 1
4 or
5 (level = 1 and exists (select null from t1 t2 where id_parent = t1.id)) )
6 start with ( id_parent is null )
7 connect by prior id = id_parent
8 order siblings by id;
ID ID_PARENT CODE
---------- ---------- ----
3 STD
4 3 STD
5 4 STD
7 4 STD
6 3 STD
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select id, id_parent, code_type
2 from t1
3 start with ( id_parent is null
4 and
5 exists (select null from t1 t2 where id_parent = t1.id))
6 connect by prior id = id_parent
7 order siblings by id;
ID ID_PARENT CODE
---------- ---------- ----
3 STD
4 3 STD
5 4 STD
7 4 STD
6 3 STD
Arun Gupta, August 27, 2004 - 5:06 pm UTC
The query we had written to fulfill the requirement ran in half hour. Your solution worked in under 2 seconds. My application team was delighted. Please accept our thanks for the solution.
Traversing a tree
A reader, September 01, 2004 - 5:40 pm UTC
I have a view that contains 3 things
emp1_no, role, emp2_no
emp1_no can be connected to emp2_no using a set of 4 "roles"
This creates a hierarchy.
How can I traverse/display this hierarchy in 9iR2?
Thanks
September 01, 2004 - 8:48 pm UTC
no idea, since I don't have an example to work with -- nor do I understand your concept of 4 "roles"....
How about this -- tomorrow I have to talk to someone about their IDP for the current FY, we'll use the RTS to see what they've been working on. So -- what does that mean, what will we use and how could we use it, and what is the duration of the current FY?
see it is all about context, and here I have none :)
that sentence makes sense to me, but probably means "not too much" to you.
Storing the hierarchy or calculating at run-time
A reader, September 01, 2004 - 7:10 pm UTC
Another question regarding my (emp1,role,emp2) hierarchy. The app folks want to maintain this as a hierarchy so it is easy to add/remove nodes from the tree. But the users of this data (reports) would like to see "given emp1, show me all the emps that are related to emp1 upto N levels in the hierarchy"
Would you recommend "flattening" this hierarchy as a (emp1,emp2) set of rows? Or traversing this hierarchy at run-time for each query needing this answer?
Thanks
September 01, 2004 - 8:58 pm UTC
"insufficient data" see above.
A reader, September 01, 2004 - 9:24 pm UTC
My fault, I should have explained better. Here goes
create table t (emp1_no int,role varchar2(1),emp2_no int);
insert into t values (1,'A',2);
insert into t values (1,'A',3);
insert into t values (2,'A',3);
insert into t values (3,'A',4);
insert into t values (4,'A',5);
insert into t values (5,'A',6);
insert into t values (4,'A',6);
There are some non-A rows there as well, but I am interested only in the A rows
I want to generate output that lists the full hierarchy i.e.
1,2
1,3
1,4 (via 3)
1,5 (via 3,4)
1,6 (via 3,4,5 or via 3,4)
2,3
2,4 (via 3)
3,4
3,5 (via 4)
4,5
4,6
How can I do this? (I dont really care about the 'via' part, just that emp1_no and emp2_no are ancestors/succesors of each other (upto a depth of 4 only)
Given this, can you also answer my other question? Apps would like to maintain this as a hierarchy, but reporting would like to know, given a emp1_no, what are all the emp2_no's related to it? Does it make sense to calculate this at run-time or flatten out the hierarchy and store it in another table? This view is going to be joined to zillion other things so that has to taken into account when calculating this at run-time
Thanks
September 02, 2004 - 7:36 am UTC
I don't agree with your sample output -- sometimes you complete the hierarchy, sometimes you don't.
you follow 1's all of the way down, but skip some 2's, 3's, 4's and ignore 5 alltogether.
ops$tkyte@ORA9IR2> select distinct substr( scbp, 1, instr(scbp,',')-1 ) emp1_no, emp2_no
2 from (
3 select emp1_no, emp2_no,
4 ltrim(sys_connect_by_path(emp1_no,','),',')||',' scbp
5 from t
6 connect by prior emp2_no = emp1_no
7 )
8 /
EMP1_NO EMP2_NO
---------- ----------
1 2
1 3
1 4
1 5
1 6
2 3
2 4
2 5
2 6
3 4
3 5
3 6
4 5
4 6
5 6
15 rows selected.
A reader, September 02, 2004 - 9:08 am UTC
Yes, my sample output was not correct, but you get the idea. I want to show "for each emp1_no, all the successor emp2_no's"
1. Why do you need the DISTINCT? Is there any way to avoid it?
2. How can I limit this to a depth of 4 only?
3. Can you comment on my other question regarding doing this at run-time vs. pre-calculating it and storing it?
Thanks
September 02, 2004 - 9:49 am UTC
1) remove it and see what happens. there will necessarily be duplicates
1 goes to 3 directly, however, when we traverse the tree we see 1 -> 2 -> 3. we will necessarily get both and then we de dup them out.
2) ops$tkyte@ORA9IR2> select distinct substr( scbp, 1, instr(scbp,',')-1 ) emp1_no, emp2_no
2 from (
3 select emp1_no, emp2_no,
4 ltrim(sys_connect_by_path(emp1_no,','),',')||',' scbp
5 from t
6 connect by prior emp2_no = emp1_no and level <= 4
7 )
8 order by 1, 2
9 /
EMP1_NO EMP2_NO
---------- ----------
1 2
1 3
1 4
1 5
1 6
2 3
2 4
2 5
2 6
3 4
3 5
3 6
4 5
4 6
5 6
15 rows selected.
3) depends entirely on your needs doesn't it? if people query this a million times during the day AND the data is static -- seems like a materialized view would make sense.
if the data changes frequently AND people need to query the most current data THEN no, it would not make sense perhaps.
A reader, September 02, 2004 - 11:05 am UTC
01436, 00000, "CONNECT BY loop in user data"
A reader, September 02, 2004 - 12:14 pm UTC
Suppose I have
drop table t;
create table t(emp1_no int,emp2_no int);
insert into t values (1,2);
insert into t values (1,3);
insert into t values (2,4);
insert into t values (3,4);
insert into t values (4,5);
insert into t values (5,6);
insert into t values (5,7);
-- causes the loop
insert into t values (6,1);
Your query above throws the ORA-1436 error.
How can this be avoided? Yes, ideally, there shouldnt be a loop in the data, but there is. What are my options? Is there a way to just "skip" this offending link and continue tree traversal?
Or better yet, how can I prevent this sort of loop from happening in the first place?
Thanks
September 02, 2004 - 1:24 pm UTC
in 10g, you can "skip it" - you can have it report back the node that would cause a connect by loop instead of erring out.
in all versions, you would prevent it by
a) serializing modifications at some level on this table in a stored procedure
b) doing the modification
c) selecting out the affected data with a connect by to see if you have a loop
Scalability?
A reader, September 02, 2004 - 12:17 pm UTC
"if the data changes frequently AND people need to query the most current data THEN no, it would not make sense perhaps"
the data does change frequently and people do need to query the most current.
i guess my question is really, how scalable is this connect by/sys_connect_by_path stuff? Does it scale to 1000 rows? 10000? 1 million?
Would indexes help to speed it up? How?
Thanks
September 02, 2004 - 1:42 pm UTC
one word for you:
benchmark
connect by can be very very very scalable given the right questions, given the proper indexes.
but as with anything -- it depends on your USE of it.
slow
A reader, September 02, 2004 - 2:30 pm UTC
Please review the following tkprof
********************************************************************************
select * from (
select
substr(path,1,instr(path,'/')-1) left_name,
right_name from (
select left_last_name||', '||left_first_name left_name,right_last_name||', '||right_first_name right_name
,ltrim(sys_connect_by_path(left_last_name||', '||left_first_name,'/'),'/')||'/' path
from employee_employee
where role in ('XX','YY')
connect by prior right_emp_No=left_emp_no and prior left_emp_no != right_emp_No and level<=3)
) where left_name='Smith, John'
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.08 0.06 0 9 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 15.49 15.18 0 306 0 22
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 15.57 15.24 0 315 0 22
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 1235
Rows Row Source Operation
------- ---------------------------------------------------
22 VIEW (cr=306 r=0 w=0 time=15182985 us)
148389 FILTER (cr=306 r=0 w=0 time=13992161 us)
193879 CONNECT BY WITH FILTERING (cr=306 r=0 w=0 time=13709588 us)
1627 COUNT (cr=153 r=0 w=0 time=41852 us)
1627 HASH JOIN (cr=153 r=0 w=0 time=40595 us)
2028 TABLE ACCESS FULL EMP (cr=66 r=0 w=0 time=3646 us)
1627 HASH JOIN (cr=87 r=0 w=0 time=25973 us)
1627 HASH JOIN (cr=21 r=0 w=0 time=11675 us)
1627 INDEX FULL SCAN EMP_EMP_OPTN#EMP_EMP_O_UK (cr=9 r=0 w=0 time=1561 us)(object id 237295)
1959 INDEX FULL SCAN OPTN#OPTN_ID_CD_VAL_UN (cr=12 r=0 w=0 time=1698 us)(object id 202749)
2028 TABLE ACCESS FULL EMP (cr=66 r=0 w=0 time=3178 us)
13759 HASH JOIN (cr=153 r=0 w=0 time=79858 us)
1627 CONNECT BY PUMP (cr=0 r=0 w=0 time=1868 us)
1627 HASH JOIN (cr=153 r=0 w=0 time=42413 us)
2028 TABLE ACCESS FULL EMP (cr=66 r=0 w=0 time=2776 us)
1627 HASH JOIN (cr=87 r=0 w=0 time=29213 us)
1627 HASH JOIN (cr=21 r=0 w=0 time=11006 us)
1627 INDEX FULL SCAN EMP_EMP_OPTN#EMP_EMP_O_UK (cr=9 r=0 w=0 time=1402 us)(object id 237295)
1959 INDEX FULL SCAN OPTN#OPTN_ID_CD_VAL_UN (cr=12 r=0 w=0 time=1615 us)(object id 202749)
2028 TABLE ACCESS FULL EMP (cr=66 r=0 w=0 time=3383 us)
********************************************************************************
This is the autotrace for this
22 rows selected.
Elapsed: 00:00:13.74
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=28 Card=1540 Bytes=3118500)
1 0 VIEW (Cost=28 Card=1540 Bytes=3118500)
2 1 FILTER
3 2 CONNECT BY (WITH FILTERING)
4 3 COUNT
5 4 HASH JOIN (Cost=28 Card=1540 Bytes=137060)
6 5 TABLE ACCESS (FULL) OF 'EMP' (Cost=11 Card=2025 Bytes=54675)
7 5 HASH JOIN (Cost=16 Card=1540 Bytes=95480)
8 7 HASH JOIN (Cost=4 Card=1540 Bytes=53900)
9 8 INDEX (FULL SCAN) OF 'EMP_EMP_OPTN#EMP_EMP_O_UK' (UNIQUE) (Cost=9 Card=1540 Bytes=21
560)
10 8 INDEX (FULL SCAN) OF 'OPTN#OPTN_ID_CD_VAL_UN' (UNIQUE) (Cost=10 Card=1939 Bytes=4071
9)
11 7 TABLE ACCESS (FULL) OF 'EMP' (Cost=11 Card=2025 Bytes=54675)
12 3 HASH JOIN
13 12 CONNECT BY PUMP
14 12 COUNT
15 14 HASH JOIN (Cost=28 Card=1540 Bytes=137060)
16 15 TABLE ACCESS (FULL) OF 'EMP' (Cost=11 Card=2025 Bytes=54675)
17 15 HASH JOIN (Cost=16 Card=1540 Bytes=95480)
18 17 HASH JOIN (Cost=4 Card=1540 Bytes=53900)
19 18 INDEX (FULL SCAN) OF 'EMP_EMP_OPTN#EMP_EMP_O_UK' (UNIQUE) (Cost=9 Card=1540 Bytes=
21560)
20 18 INDEX (FULL SCAN) OF 'OPTN#OPTN_ID_CD_VAL_UN' (UNIQUE) (Cost=10 Card=1939 Bytes=40
719)
21 17 TABLE ACCESS (FULL) OF 'EMP' (Cost=11 Card=2025 Bytes=54675)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
306 consistent gets
0 physical reads
0 redo size
1228 bytes sent via SQL*Net to client
663 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
3 sorts (memory)
0 sorts (disk)
22 rows processed
Why would 306 LIO take such a long time?
How can I speed this up?
Thanks
September 02, 2004 - 3:02 pm UTC
do you have the where clause in the right place there?
did you mean to "start with" john smith?
start with
A reader, September 02, 2004 - 3:05 pm UTC
If I put a start with inside my query instead of filtering on it outside the queyry, it is very fast.
Is there a way I can expose the 'start with' column in a view and have that passed in at run-time?
Also, the query with 'start with', although faster has twice the LIOs than the other one.
See
Earlier query:
148389 rows selected.
Elapsed: 00:00:29.32
Statistics
----------------------------------------------------------
47 recursive calls
0 db block gets
322 consistent gets
0 physical reads
0 redo size
4837526 bytes sent via SQL*Net to client
109464 bytes received via SQL*Net from client
9894 SQL*Net roundtrips to/from client
5 sorts (memory)
0 sorts (disk)
148389 rows processed
After adding start with:
22 rows selected.
Elapsed: 00:00:01.08
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
612 consistent gets
0 physical reads
0 redo size
1228 bytes sent via SQL*Net to client
663 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
4 sorts (memory)
0 sorts (disk)
22 rows processed
Why would more LIO (612 vs 322) take less time? I am confused.
Thanks for any help
September 02, 2004 - 3:24 pm UTC
Is there a way I can expose the 'start with' column in a view and have that
passed in at run-time?
No, not unless you use what I term a "parameterized view".
the end user would have to pop their values into an application context and you use that in your view. search for that term on this site for details.
Look at the rows synthesized by the first query -- it read the stuff into temp and once in temp (which in your case was in ram) it need not do an LIO from the buffer cache to get it again but it made up TONS of rows.
The second one did less in temp, but used the buffer cache "more" instead and since it produced so fewer rows (had less branches to build) it would run faster (consume lots less cpu)
Parametrized view
A reader, September 02, 2004 - 4:02 pm UTC
"the end user would have to pop their values into an application context and you use that in your view. search for that term on this site for details"
I read about it but the user of this construct is these insanely inflexible BI tools like Cognos, etc. They cannot (I am told) do things like set application context and stuff?
Is there a way to wrap the view in a (pipelined?) table functions and pass in the 'start with' parameter that way?
Thanks
September 02, 2004 - 4:28 pm UTC
can cognos:
select * from TABLE( cast(your_function(:bind_variable) as yourType ) )
?
if so, yes, a pipelined function would work fine
Sigh...
A reader, September 02, 2004 - 4:37 pm UTC
"select * from TABLE( cast(your_function(:bind_variable) as yourType ) )"
Sigh, you would think so, but the only thing it seems to be able to do is import Oracle tables/views into its own "catalog" and apply filters (where clauses) on them.
So, it would need to be a view v such that
select * from v where start_with=:bind_variable
really does
select * from TABLE( cast(your_function(:bind_variable) as yourType ) )
under the covers? How can this be done?
Thanks
September 02, 2004 - 9:26 pm UTC
No, there is no
"intercept query, parse it, turn where clause somehow into a parameter list, and invoke function" capability yet....
sorry -- generic tools, that are database independent, mean one thing
o moderate to poor performance on one vendors database
o really bad performance on the rest.
Preventing loops in user data
A reader, September 02, 2004 - 4:41 pm UTC
"a) serializing modifications at some level on this table in a stored procedure
b) doing the modification
c) selecting out the affected data with a connect by to see if you have a loop"
Given data like
create table t(emp1_no int,emp2_no int);
insert into t values (1,2);
insert into t values (1,3);
insert into t values (2,4);
insert into t values (3,4);
insert into t values (4,5);
insert into t values (5,6);
insert into t values (5,7);
I would create a SP to serialize all additions to this table.
Now when SP is called to insert (6,1) which would cause a loop, I would do the following before the (6,1) row is inserted
select stragg(path) from (
select emp1_no,emp2_no
,ltrim(sys_connect_by_path(emp1_no,'/'),'/')||'/' path
from t
start with emp1_no=1
connect by prior emp2_no=emp1_no)
where emp2_no=6
output a error message saying that adding (6,1) would cause a loop because (1,6) is already connected via the paths in stragg(path) above
Thanks a lot, this has been very useful. You are the best!
September 02, 2004 - 9:29 pm UTC
you have to either
a) issue savepoint, insert the row and see if it causes a connect by loop and then rollback to savepoint
or
b) union all this data selected from dual with the table - do the connect by and error out if a loop.
a) would be more "efficient"
Preventing loop
A reader, September 03, 2004 - 7:15 am UTC
But thats what was trying to illustrate.
Using your technique, I dont need to actuallt insert the new row to detect the loop. I know that (1,6) are already related using your queyr, the new row is (6,1), thats it I know the incoming row is going to cause a loop.
No need to actually insert the row, check for loop and rollback to savepoint, right?
September 03, 2004 - 11:53 am UTC
You can either
a) insert
b) select get error or not
or
a) run query
b) write code to process outputs <<--- go for it, up to you
c) report error
to me, that "this would be a connect by error" is sufficient, if you want "more" go for it, write the code.
Preventing loop
A reader, September 03, 2004 - 7:17 am UTC
Also, using my way (with the stragg above), I can actually tell the user *why* the loop is happening, not just a unhelpful message saying 'loop detected'
Extending
A reader, September 03, 2004 - 12:04 pm UTC
drop table t;
create table t(emp1_no int,emp1_name varchar2(100),emp2_no int,emp2_name varchar2(100));
insert into t values (1,'One,2,'Two');
insert into t values (1,'One',3'Three');
insert into t values (2,'Two',4,'Four');
insert into t values (3,'Three',4,'Four');
insert into t values (4,'Four',5,'Five');
insert into t values (5,'Five',6,'Six');
insert into t values (5,'Five',7,'Seven');
1. Similar to your earlier query showing just the emp1_no/emp2_no that are related, how can I extend this to show the "names" of emp1 and emp2? i.e. show the name of each emp next to their id in the output
2. Your query relies on the new sys_connect_by_path feature in 9i. Just curious, is there a way to do this without that feature?
Thanks
September 03, 2004 - 1:34 pm UTC
would you really have the "names there" (not normalized, they would be in another table in real life i think)
but in this case, just
2) ops$tkyte@ORA9IR2> select distinct substr( scbp, 1, instr(scbp,',')-1 )
emp1_no, emp2_no, emp2_name
2 from (
3 select emp1_no, emp2_no, emp2_name,
4 ltrim(sys_connect_by_path(emp1_no ||' '||emp1_name,','),',')||',' scbp
5 from t
6 connect by prior emp2_no = emp1_no and level <= 4
7 )
8 order by 1, 2
9 /
add the emp1 name to the sys_connect by path and emp2 name to the select list.
we could probably use a scalar subquery in 8i
Real life
A reader, September 03, 2004 - 2:15 pm UTC
"would you really have the "names there" (not normalized, they would be in another table in real life i think)"
You are right, what I have in real life is a many-many relationship between 2 emps and a emp_emp association table containing just (emp1_no,emp2_no). I have a view on top of this table that joins "emp a, emp b, emp_emp c" and I am doing all this connect by stuff on that view.
So, in real life, I need many more attributes than just the name of the employee.
ltrim(sys_connect_by_path(emp1_no ||' '||emp1_name,','),',')||','
Stuffing them all in the sys_connect_by_path will quickly get hairy especially since they are all delimited and I need to parse them back out in the final query.
Any other method now that I explained my real life scenario? (sorry if I oversimplified earlier!)
Thanks
September 03, 2004 - 2:23 pm UTC
problem is -- in this example -- it would break the distincting going on?
there are ways around it, but in this example -- doesn't make sense?
I would use the association object (with just key1/key2) and at the TOP MOST level, scalar subqueries or a join back to the keyed into tables (eg: the view would be getting totally in the way of the hierarchical queries performance)
dbms_advanced_rewrite
A reader, December 14, 2004 - 11:21 pm UTC
"No, there is no
"intercept query, parse it, turn where clause somehow into a parameter list, and invoke function" capability yet"
Does the new dbms_advanced_rewrite package in 10g offer this functionality?
Or is it simply the old stored outlines feature in a more user-friendly form? i.e. does it do simple text matching or can it change the nature of the query like above, handle bind variables, parameters, etc?
Thanks
December 15, 2004 - 1:27 pm UTC
ops$tkyte@ORA10G> !oerr ora 30353
30353, 00000, "expression not supported for query rewrite"
// *Cause: The select clause referenced UID, USER, ROWNUM, SYSDATE,
// CURRENT_TIMESTAMP, MAXVALUE, a sequence number, a bind variable,
// correlation variable, a set result,a trigger return variable, a
// parallel table queue column, collection iterator, etc.
//
// *Action: Remove the offending expression or disable the REWRITE option on
// the materialized view.
not yet.
Tree insert
Ramakrishnan.A, February 08, 2005 - 2:37 am UTC
Hello Tom,
I want to write a procedure
which take input as 'KING/JONES/SCOTT/ADAMS'
and insert 4 rows given below.
Is there is any way we can do this in single SQL stm ? than using loop & splitting.
MyInsert ( 'KING/JONES/SCOTT/ADAMS' );
LEVEL ENAME MGR
---------- ---------------------- ------------------------------
1 KING Null
2 JONES 1
3 SCOTT 2
4 ADAMS 3
Thanks
Ramakrishnan.A
February 08, 2005 - 6:40 am UTC
search this site for str2tbl.
hierarchy query on m:n self-related table
A learner, February 11, 2005 - 7:43 pm UTC
I have the following problem: there are two tables
used in the report, JOB and TASK related as 1:N.
job_num, open_dte, close_dte JOB table
task_code, job_num TASK table
Jobs are dynamically changed by adding a new record into
job table and keeping old records in the same table
(there are two date fields, open_dte and close_dte.
When close_dte is set to a non-null value that means
the record was split or merged. Merging means there can
be many parents in JOB table, each contributing it's
corresponding TASK links to the newly created JOB record.
Splitting means that some links from TASK to JOB table are
reassigned to newly created JOB record, i.e. there can be
many children in JOB table).
How should query look like in order to present history of
a job change, e.g. starting with a job ID 12345 and presenting
all splits and/or mergers in hierarchical way, sorted by date?
I tried using start with/connect by syntax but this only works on
tree structures while in this case JOB table is like a
graph (m:n associations)
here is the example of data:
JOB table
job_id open closed splitfrom mergedto
=================================================
AAAA 1/1/90 1/1/00
BBBB 1/1/80 1/1/00
CCCC 1/1/00 1/1/01 AAAA EEEE
DDDD 1/1/00 1/1/01 BBBB EEEE
EEEE 1/1/01
FFFF 1/1/01 CCCC
TASK table
job_id task_code modified_on
===================================
AAAA A1 1/1/90
AAAA A2 1/1/91
AAAA A3 1/1/92
BBBB B1 1/1/90
BBBB B2 1/1/90
CCCC A1 1/1/00
CCCC A2 1/1/00
DDDD B1 1/1/00
DDDD B2 1/1/00
EEEE A2 1/1/01
EEEE B1 1/1/01
EEEE B2 1/1/01
FFFF A1 1/1/01
The query should consider each job_id as a continuum,
i.e. whatever information is presented about a job_id
(e.g. number of tasks for job EEEE in different
periods) it must take into account previous job IDs from which
this job_id was derived (in case of EEEE, these previous job
ids are CCCC and DDDD (because of merge operation), AAAA (CCCC
split from) and BBBB (DDDD split from)
An example for job_id EEEE could be:
JobHistory Trend
===========================================================
AAAA/ 6 **before split***
AAAA/CCCC 3 **after split***before merge
BBBB/ 5 **before split***
BBBB/DDDD 7 **after split***before merge
[AAAA/CCCC,BBBB/DDDD]/EEEE 10 ***after merge***
February 12, 2005 - 12:15 pm UTC
maybe a new question some day -- didn't fully understand everything.
Clarification
A learner, February 14, 2005 - 2:23 am UTC
In order to use hierarchical query I need to start with an JOB_ID. I can do that by using SPLIT_FROM field of the given JOB_ID because it gives me direct link with predecessors.
But the problem is that I cannot use the field MERGE_INTO in the given JOB_ID because it is not present (if it was present it would probably be called MERGED_FROM and not MERGED_INTO). I.e. I have to search all records in order to find those that point to current (given) JOB_ID (those that were merged into it), and then for each of these search all records for each to find all those that point to it, etc. etc.
So I was wondering if there is a way to write START WITH CONNECT BY statement in such a way to find all of the leaf records that all were "starters" in merging operations that resulted in the given JOB_ID record? Or to rephrase: to find all possible leafs/paths of merging trees which have given JOB_ID node as the the root.
Graphically this can be presented as two kind of directed trees, one having leafs at the top and roots at the bottom where pointers (MERGED_INTO fields) are from children to parent nodes, and the other trees having roots at the top and leafs at the bottom where pointers (SPLIT_FROM fields) are also from children toward parents. Therefore, a given JOB_ID can be, at the same time, the root of the merging tree and the leaf of the splitting tree. For the spliting tree I can write START WITH CONNECT BY query because I have direct pointer SPLIT_FROM, but for the merging tree I have to find all the leafs and paths based on given root node (JOB_ID).
Hope this clarifies and isolates the problem: finding all leafs and paths (e.g. AAAA/CCCC/EEEE) given the root, where all pointers are toward the root and not from the root?
Many thanks for looking into this problem!
February 14, 2005 - 8:35 am UTC
(you didn't see to have read the above? I'm actually not looking at this here, too big, too new, when I'm taking a question -- which is restricted by the fact that I look at these things -- I'll look at it there)
removing duplicated datas in an Hierarchy records
Chandru.M, June 06, 2005 - 9:54 am UTC
hi tom,
I am really sorry for posting my question here.I need to display an Hierarchical result set for that i need an single query.
My table is some thing similar to this table.
create table test
(
id number primary key,
data varchar2(2),
prev_id number
)
/
insert into test(id,data) values(1,'A')
/
insert into test(id,data,prev_id) values(2,'B',1)
/
insert into test(id,data,prev_id) values(3,'B',2)
/
insert into test(id,data,prev_id) values(4,'C',3)
/
insert into test(id,data,prev_id) values(5,'B',4)
/
select * from test tt start with tt.id = (select 5 from dual)
connect by tt.id= prior tt.prev_id
/
--this subquery is needed
the output is
ID DA PREV_ID
---------- -- ----------
5 B 4
4 C 3
3 B 2
2 B 1
1 A
but my actual o/p should be like this
ID DA PREV_ID
---------- -- ----------
5 B 4
4 C 3
2 B 1
1 A
i.e. whenever there is an data column duplicated at successive levels i should take only the last occurence.Pls help me.
Thanks in advance
Chandru.M
(INDIA)
June 06, 2005 - 10:51 am UTC
i don't see why "3" is considered duplicate.
probably ?
Joachim Seekopp, June 06, 2005 - 10:59 am UTC
he means probably
SQL> select id,data,prev_id
2 from
3 (select id,data,prev_id,lead(data) over(order by level) le
4 from (
5 select level, tt.* from test tt start with tt.id =5
6 connect by tt.id= prior tt.prev_id
7 ))
8 where data!=le or le is null;
ID DA PREV_ID
---------- -- ----------
5 B 4
4 C 3
2 B 1
1 A
?
Thanks
Chandru.M, June 07, 2005 - 1:53 am UTC
Hai,
Sorry for not being clear and thanks for the followup.Joachim's solution is what i was looking for.
My actual records will be something like this
ID DATA PREV_ID
-- ---- -------
6 D 5
5 D 4
4 C 3
3 B 2
2 B 1
1 A
i have to consider the column DATA.i.e.when the data column is duplicated at successive levels i need to take the first record.
6 D 5
5 D 4
Data column has the value D which is duplicated at successive levels.Here i have to consider the column which has been entered earlier ie i have to consider only the row
5 D 4
Thanks
Chandru.M
One help pls
Binish, June 08, 2005 - 11:49 am UTC
Could you help me in the foll. query:
Table T
Subject Object Relation Group
------- ------ -------- -----
d b component-of A
c a component-of A
b a component-of A
a 1 component-of A
f e component-of A
e 1 component-of A
1 2 component-of B
... and many more records of other groups
I want to perform a query for selecting records of group A only.
I want the result as follows:
Subject
-------
a
b
c
d
e
f
How do I write the query for the same?
Thanks
Binish
June 08, 2005 - 11:56 am UTC
select subject from t where group = 'A' order by subject ???
seems pretty basic?
NO! I am not looking for that.!!!
Binish, June 09, 2005 - 5:11 am UTC
Putting it in a different way, there are lots of trees stored in a table, but I am interested in trees within a subset of the data in the table.
ie. if view V is
select * from T where group = A,
I want to display all the trees in view V.
But the difference here is the roots of trees in V need not have reltaion NULL (as they have that relation with some other row in the base table T).
Let me re-order the data-set as follows:
Subject Object Relation Group
------- ------ -------- -----
d b component-of A
b c component-of A
c a component-of A
x a component-of A
a 1 component-of A
e f component-of A
f 1 component-of A
1 2 component-of B
Now the output I need is:
a
c
b
d
x
f
e
What I tried is without a "start with" clause, but I am getting 19 records!!
This is what I tried:
create table t (subject varchar2(20), object varchar2(20), relation varchar2(20), grp varchar2(20))
insert into t values ('d', 'b','component-of','A')
insert into t values ('b', 'c','component-of','A')
insert into t values ('c', 'a','component-of','A')
insert into t values ('x', 'a','component-of','A')
insert into t values ('a', '1','component-of','A')
insert into t values ('e', 'f','component-of','A')
insert into t values ('f', '1','component-of','A')
insert into t values ('1', '2','component-of','B')
select * from t
create or replace view V as select subject, object, relation from t where grp = 'A'
select * from v
select rpad(' ', 2*level, ' ')||subject, object, level from v connect by object = prior subject
Am I missing the obvious?
Thanks
Binish
June 09, 2005 - 7:19 am UTC
(well, please -- read your question above and tell me how someone who hasn't been staring at the problem with the requirements pounded in their head could figure any of this out.
</code>
http://asktom.oracle.com/Misc/how-to-ask-questions.html <code>
)
to you subject, object, the model, the relations -- they are second nature (it is after all your data, your model). To me and others, just characters on a screen -- we haven't been staring at this for a day or two.
"I want to display all the trees in view V."
to me, that has clear meaning but to you and I it appears to have a very different meaning.
again, select * from v seems to be giving the output you draw on the screen, It is not clear what you are looking for.
Hope this clarifies..
Binish, June 09, 2005 - 7:42 am UTC
But when I said I need heirarchical query for this, so that I can get other features offered by that. Here I want the same info to be displayed but along with the LEVEL and the order in which heirarchical queries displays data (tree after another).
I dont think these can be given by a normal query.
June 09, 2005 - 7:44 am UTC
<quote>
Now the output I need is:
a
c
b
d
x
f
e
</quote>
now, how does LEVEL fit into the picture.
yet another try..
Binish, June 09, 2005 - 8:10 am UTC
But the query I've given has the level field.
Anyways, here is the refined requirement:
Output I need is
Subject Level
------- -----
a 1
c 2
b 3
d 4
x 2
f 1
e 2
I need this kind of output to display a tree of subjects in forms developer.
Hope its clear this time.
June 09, 2005 - 9:24 am UTC
why is d '4'?
why not 3
why not 2
for you see, C is both a child and a root node.. why is c 2, why not 1?
what is the *logic* behind this, not getting it.
c is not a root here!!
Binish, June 09, 2005 - 10:47 am UTC
The trees in view V can be depicted as follows:
a f Level 1
/ \ |
/ \ |
c x e Level 2
|
b Level 3
|
d Level 4
A pre-order traversal of first tree will give the output a, c, b, d, x and that for the second tree will give the output f, e.
So the order I look for also is the same, and heirarchical query gives me the nodes in this order.
Now, as in the figure above the levels of c is 2 and only 2 and that of d is 4. I dont know what is the confusion here!!!
C is not a root node, only a and f are root nodes.
June 09, 2005 - 6:03 pm UTC
The confusion is, you have a picture in your head, you know exactly what you want. but this picture is not obvious. (exactly what I talked about in that blog -- if it were obvious, you'd be done, it would be trivial)
Look:
1 select rpad(' ', 2*level, ' ')||subject x, object, level from v connect by object
2* = prior subject
tkyte@ORA9IR2W> /
X OBJECT LEVEL
-------------------- -------------------- ----------
a 1 1
c a 2
b c 3
d b 4
x a 2
f 1 1
e f 2
c a 1
b c 2
d b 3
x a 1
d b 1
b c 1
d b 2
e f 1
Ok, C is a root if you start from C, F is a root, X is a root, and so on. They are all roots based on your perspective.
So, are you looking to simply start with "nodes that can never have a parent"
and if so, what in your model can be used to easily figure that out (eg: in a normal hierachy, we would simply start with "start with parent is null", for examle, in the scott.emp table -- start with MGR IS NULL)
What is the problem here?
Mikito Harakiri, June 09, 2005 - 1:21 pm UTC
Select all the nodes of group A, then traverse to the root from each of them, and find the set of roots. Then raverse from the roots to the leaves to get the required tree forest?
June 09, 2005 - 6:32 pm UTC
that is exactly the problem - determining the problem.
(but you saw the output they wanted, if you had an "idea" - feel free the FLESH IT OUT, you know -- give an example, that was tested and seems to work)
What Mikito interpreted is correct..
Binish, June 13, 2005 - 5:40 am UTC
roots = "nodes which can never have any parents".. (i felt this is obvious!!)
I am looking for what Mikito Harakiri has mentioned, but how to write a query for that?
select rpad(' ', 2*level, ' ')||subject, object, level
from v
START WITH (only the nodes which doesn't have any parents in group A.. that is, start with a, f for the data set mentioned above)
connect by object = prior subject ------------------(1)
I want include the traversing stuff Mikito mentioned in the above query, or findout some other way to achieve the same. How to find out root node from any node? And how to include and that in (1) above?
June 13, 2005 - 11:15 am UTC
I did not feel this to be "obvious" obviously (and as I kept saying, you have been looking at this, what is OBVIOUS to you is not so obvious to anyone else, it is your data, your question - we just see characters on the screen).
Normally, there is a very easy way to tell if a node has no parents.
Look at SCOTT.EMP, the cause "start with MGR is NULL" identifies easily all rows without parents.
so, tell us, with your model, what tells you that "these are the ones to start with", it appears you want to start with the set of rows such that "object" is not a "subject"
ops$tkyte@ORA9IR2> select rownum, rpad(' ', 2*level, ' ')||subject x, object, level
2 from v
3 start with object not in (select subject from v)
4 connect by object = prior subject;
ROWNUM X OBJECT LEVEL
---------- --------------- -------------------- ----------
1 a 1 1
2 c a 2
3 b c 3
4 d b 4
5 x a 2
6 f 1 1
7 e f 2
7 rows selected.
perhaps.
This one works!
Binish, June 13, 2005 - 7:54 am UTC
select rpad(' ', 2*level, ' ')||subject, object, level from v
start with subject in (select substr((select max( to_char(level,'fm000009') || ' ' || subject )
from v v2
start with v2.subject = v1.subject
connect by prior object is not null and prior object = subject)
, 8 ) root
from v v1)
connect by object = prior subject
I wrote the above query to get teh answer, but looks big with inner query which in turn uses heirarchical query!!
Is there a better way of writing the same??
June 13, 2005 - 11:35 am UTC
see above.
Thats brilliant!
Binish, June 13, 2005 - 11:52 am UTC
Thats exactly what I was looking for, many thanks.
Why two connect by operations in the plan?
Naresh, April 25, 2006 - 8:30 am UTC
Hello Tom,
In below explain plan, why are there two "CONNECT BY" operations shown? My understanding is that first the outer join between the two tables will be done and on this, the connect by will be performed. So that is only one connect by operation. What is the meaning of "CONNECT BY PUMP" and "CONNECT BY (WITH FILTERING)" ?
1 Select /*+ USE_HASH (c, a) */
2 to_number(trim( '/' from sys_connect_by_path(c.customer_id, '/'))) root_cust_id,
3 c.customer_id, c.ch_node_id c_node, arc_father_id, arc_child_id
4 From customer c, ch_arcs a
5 Where c.ch_node_id(+) = a.arc_father_id
6 start with c.ch_node_id = arc_father_id
7* connect by prior arc_child_id = arc_father_id
SQL> /
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=5 Card=82 Bytes=4264)
1 0 CONNECT BY (WITH FILTERING)
2 1 FILTER
3 2 COUNT
4 3 HASH JOIN (OUTER) (Cost=5 Card=82 Bytes=4264)
5 4 TABLE ACCESS (FULL) OF 'CH_ARCS' (Cost=2 Card=82 Bytes=2132)
6 4 TABLE ACCESS (FULL) OF 'CUSTOMER' (Cost=2 Card=82 Bytes=2132)
7 1 HASH JOIN
8 7 CONNECT BY PUMP
9 7 COUNT
10 9 HASH JOIN (OUTER) (Cost=5 Card=82 Bytes=4264)
11 10 TABLE ACCESS (FULL) OF 'CH_ARCS' (Cost=2 Card=82 Bytes=2132)
12 10 TABLE ACCESS (FULL) OF 'CUSTOMER' (Cost=2 Card=82 Bytes=2132)
connect by pump v/s connect by filter
Naresh, April 28, 2006 - 11:37 am UTC
Tom,
I am hoping you will respond to my previous question from April 25th - please.
Thanks,
Naresh.
April 28, 2006 - 12:52 pm UTC
I don't see every followup, I answer what I see as I have time.
That is just the format of the connect by plan in current releases - there are two bits to it - it is just the way it outputs.
suggest you run it with sql trace and tkprof - you'll get a better feel for what it is doing seeing the rows flowing through each step.
unnecessary outer join ...
Gabe, May 02, 2006 - 3:27 am UTC
In the above from Naresh, an equi-join should be enough
would it not?
How to use Hierarchical query to sum all salaries?
Speed Racer, July 07, 2011 - 9:24 am UTC
I have this structure:
rating|id_chield|id_parent|VALUE
A|1|null|NULL|
A|2|1|NULL
A|3|2|NULL|
A|4|3|NULL|
A|5|3|100|
A|6|3|NULL|
....
B|1|null|NULL|
B|2|1|NULL|
B|3|2|NULL|
B|4|3|50|
B|5|3|NULL|
B|6|3|60|
I need rollup the values for respective parents.
I have in max 4 levels.
July 08, 2011 - 2:22 pm UTC
no create
no inserts
no look
level - 11.2 vs 11.1
A reader, July 19, 2011 - 10:49 am UTC
Hello,
Our development team had written a code using "level" pseudo column in their queries without using connect by clause. They were getting the right ouput. However, when they tried to run the same on 11.2, the query is ignoring the "where" clause. This however, required to set one of the "_" parameter to true on both the versions.
SQL> select job_id,job_title,level from jobs where job_id='AD_VP';
select job_id,job_title,level from jobs where job_id='AD_VP'
*
ERROR at line 1:
ORA-01788: CONNECT BY clause required in this query block
SQL> alter session set "_allow_level_without_connect_by"=true;
Session altered.
SQL> select job_id,job_title,level from jobs where job_id='AD_VP';
JOB_ID JOB_TITLE LEVEL
---------- ----------------------------------- ----------
AD_PRES President 0
AD_VP Administration Vice President 0
AD_ASST Administration Assistant 0
FI_MGR Finance Manager 0
FI_ACCOUNT Accountant 0
AC_MGR Accounting Manager 0
AC_ACCOUNT Public Accountant 0
SA_MAN Sales Manager 0
SA_REP Sales Representative 0
PU_MAN Purchasing Manager 0
PU_CLERK Purchasing Clerk 0
ST_MAN Stock Manager 0
ST_CLERK Stock Clerk 0
SH_CLERK Shipping Clerk 0
IT_PROG Programmer 0
MK_MAN Marketing Manager 0
MK_REP Marketing Representative 0
HR_REP Human Resources Representative 0
PR_REP Public Relations Representative 0
19 rows selected.
In the above output, I was expecting only one row but it displayed all the 19 rows. In 11gR1, the developers say it returns only 1 row. Why is this difference?
Thanks,
July 19, 2011 - 10:55 am UTC
is level a column in your table?
or, did they just want the number 0 to come out?
I don't know what the logic here is - what was the goal of querying level??
I would suggest unsetting the "_"'s and not doing that. Rather, let's fix the bug here. To me the bug is the use of LEVEL.
If it is a column in the table, this is the way to code that:
ops$tkyte%ORA11GR2> select x, level from t;
select x, level from t
*
ERROR at line 1:
ORA-01788: CONNECT BY clause required in this query block
ops$tkyte%ORA11GR2> select x, "LEVEL" from t;
no rows selected
it is was just supposed to be zero, then
ops$tkyte%ORA11GR2> select x, 0 "LEVEL" from t;
no rows selected
would be the approach.
Can you clarify what their actual intent here was?