SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

join child departments with its parents

User_76ZL9Feb 7 2014 — edited Feb 7 2014

CREATE TABLE b(

ID VARCHAR2(50 BYTE),

PARENT_ID VARCHAR2(50 BYTE),

NAME NVARCHAR2(200)

);

insert into b values ('401.1.1','401.1','a');

insert into b values ( '402.1.1','402.1','d');

insert into b values ('402.1.1.1','402.1','e');

insert into b values ('400','1','asc');

insert into b values( '401','400','dep1');

insert into b values ( '402','400','dep2');

insert into b values('403','400','dep3');

insert into b values('401.1','401','A');

insert into b values('401.2','401','B');

insert into b values('401.3','401','C');

insert into b values('402.1','402','D');

insert into b values('402.2','402','E');

NOW I WANT MY RESULT WOULD BE LIKE THIS

Header 1	Header 2	Header 3	Header 4	Header 5	Header 6	Header 7	Header 8	Header 9	Header 10	Header 11	Header 12	Header 13	Header 14	Header 14
400	1	asc
400	1	asc	401	400	dep1
400	1	asc	402	400	dep2
400	1	asc	403	400	dep3
400	1	asc	401	400	dep1	401.1	401	A
400	1	asc	401	400	dep1	401.2	401	B
400	1	asc	401	400	dep1	401.3	401	C
400	1	asc	402	400	dep2	401.1	402	D
400	1	asc	402	400	dep2	401.2	402	E
400	1	asc	401	400	dep1	401.1	401	A	401.1.1	401.1	a
400	1	asc	402	400	dep2	401.1	402	D	402.1.1	402.1	d
400	1	asc	402	400	dep2	401.1	402	D	402.1.1	402.1	d	402.1.1.1	402.1.1	e

i use self join and union all bu retrieve this information ,but in other case my hiearchy tree has more than 10 sub department then my script is not correct ,please give solution not only for 4 subdepartment like this ,but nearly 10 ten subdepartment,because in other offices as i said i have 10 subdepartment ,here i only give example

This post has been answered by Frank Kulash on Feb 7 2014

Jump to Answer

mathguy

What happened when you tried? It's pretty easy to write a very small test case, which will tell you if they are compatible or not.

BEDE

Test and see.

For instance:

create table zz as

select to_char(sysdate,'mmss')+level n1, ora_hash( to_char(sysdate,'mmss')+level) c1

from dual

connect by level<1000

;

select *

from zz

order by n1

fetch first 10 rows only

for update

;

ORA-02014: cannot select FOR UPDATE from view with DISTINCT, GROUP BY, etc.

This I have tested in Live SQL.

So, you may well fetch the rowid and lock the rows as fetched.

I think of something like:

declare

type tab_rid is table of varchar2(100);

tb_rid tab_rid;

v_rid varchar2(100);

begin

tb_rid:=tab_rid();

for r in (

select t.rowid rid, t.*

from zz t

order by n1

fetch first 10 rows only

)

loop

begin

select rowidtochar(t.rowid) into v_rid

from zz t

where t.rowid=r.rid

for update nowait;

tb_rid.extend;

tb_rid(tb_rid.count):=v_rid;

exception

when others then null; --- what to do if one row can't be locked? suppose leave it for some other time...

end;

end loop;

--- do whatever with the locked rows

---

commit;

end;

Jonathan Lewis

As Bede points out - Oracle does not allow "for update" with this basic query as "fetch first" is implemented through a view over an analytic function.

However the following is an approach that will work in pure SQL - fetch the rowids of the rows you want in an inline non-mergeable view - and, if necessary, force a nested loop joinback by rowid:

select

from

where

t1.rowid in (

select

/*+ no_merge */

t1a.rowid

from

t1 t1a

order by

t1a.n1

fetch

first 10 rows only

)

;

Regards

Jonathan Lewis

Sanjib Saha

Thanks Jonathan, but this will not lock the desire row / rows.

Paulzip

Sanjib Saha wrote:

Thanks Jonathan, but this will not lock the desire row / rows.

cursor curSomething is

select

from

where

t1.rowid in (

select

/*+ no_merge */

t1a.rowid

from

t1 t1a

order by

t1a.n1

fetch

first 10 rows only

)

for update;

-- Do stuff with cursor.

Jonathan Lewis

Sanjib Saha wrote:

Thanks Jonathan, but this will not lock the desire row / rows.

As PaulZip points out, my example does need the FOR UPDATE clause if you want to lock the rows.

Regards

Jonathan Lewis

1 - 6

Locked Post

New comments cannot be posted to this locked post.

Locked on Mar 7 2014

Added on Feb 7 2014

3 comments

419 views

SQL & PL/SQL

join child departments with its parents

Comments

Post Details