Home » SQL & PL/SQL » SQL & PL/SQL » connect by query
connect by query [message #213439] Wed, 10 January 2007 15:27 Go to next message
jinga
Messages: 115
Registered: January 2003
Senior Member
Table 1: looks like

                  c1
                  /\
                 c2 c3
                /    \
               c4
               /
              c6.


so the above structure is in the following table.


tree      cell       parentcell
T1        C6          C4
T1        C4          C2
T1        C2          C1
...


Here cells c2,c4,c6 are connected thru parent child relationship.


Table 2

tree   cell    col1   col2    col3      col4

 t1     c6      a      b      <null>    <null>  
 t1     c4    <null>  <null>    c       <null>
 t1     c2    <null>  <null>  <null>	    d




I would like to get the result set as


tree   cell    col1    col2    col3     col4
T1      C6      a       b       c        d



I would like the result set to have values for all the columns.If some column has null value, then i need to go one level up and find if that column has non null value for its parent and if not i go one level up and find the value for its parent and so on.

Any sql help on this is very much appreciated.
Re: connect by query [message #213453 is a reply to message #213439] Wed, 10 January 2007 17:14 Go to previous messageGo to next message
srinivnp
Messages: 136
Registered: January 2006
Location: stlouis MO USA
Senior Member
curamgph>desc foo1;
Name Null? Type
---------------------------------------- -------- -------------
TREE VARCHAR2(3)
CELL VARCHAR2(3)
PCELL VARCHAR2(3)


begin
insert into foo1 values ('T1','C6','C4');
insert into foo1 values ('T1','C4','C2');
insert into foo1 values ('T1','C3','C1');
insert into foo1 values ('T1','C1',null) ;
end;

===> We need a way a way to identify the top parent .
( Or the lowest child.) I introduced a row to identify the top parent of each tree. If this cannot be done someother method will have to be employed.


curamgph>desc foo2;
Name Null? Type
---------------------------------------- -------- ------------
TREE VARCHAR2(3)
CELL VARCHAR2(3)
COL1 VARCHAR2(3)
COL2 VARCHAR2(3)
COL3 VARCHAR2(3)
COL4 VARCHAR2(3)

begin
insert into foo2 ( tree, cell,c1,c2,c3,c4)
values ('T1','C6','a','b',null,null) ;
insert into foo2 ( tree, cell,c1,c2,c3,c4)
values ('T1','C6',null,null,'c',null) ;
insert into foo2 ( tree, cell,c1,c2,c3,c4)
values ('T1','C6',null,null,null,'d') ;
end;



curamgph>select * from foo1;

TRE CEL PCE
--- --- ---
T1 C6 C4
T1 C4 C2
T1 C2 C1
T1 C1

curamgph>select * from foo2;

TRE CEL COL COL COL COL
--- --- --- --- --- ---
T1 C6 a b
T1 C4 c
T1 C2 d




Quote:
select tree,cell,col1,col2,col3,col4 from
(
select tree,cell,
last_value(col1 ignore nulls)
over (partition by tree,cell order by null) as col1 ,
last_value(col2 ignore nulls)
over (partition by tree,cell order by null) as col2 ,
last_value(col3 ignore nulls)
over (partition by tree,cell order by null) as col3 ,
last_value(col4 ignore nulls)
over (partition by tree,cell order by null) as col4 ,
row_number()
over (partition by tree,cell order by null ) as rn
from (
with qr as (
select tree,cell,pcell
from (
select tree,cell,pcell , connect_by_isleaf leaf
from foo1
start with pcell is null
connect by pcell=prior cell)
where leaf=1
)
select qr.tree,qr.cell
,f2.col1,f2.col2,f2.col3,f2.col4
from qr,foo2 f2
where qr.tree=f2.tree
)
) where rn =1
/




TRE CEL COL COL COL COL
--- --- --- --- --- ---
T1 C6 a b c d



===========

The tree diagram shows C2 as child of C1 . But that was not shown in the data listing.

So this sql would work for a "linear" descendancy chain.

Srini

Re: connect by query [message #213623 is a reply to message #213439] Thu, 11 January 2007 09:27 Go to previous messageGo to next message
jinga
Messages: 115
Registered: January 2003
Senior Member
Srinivas,

Thanks for the query. I am using oracle 9.2. I dont think i can use connect by isleaf.

Re: connect by query [message #213648 is a reply to message #213439] Thu, 11 January 2007 12:58 Go to previous messageGo to next message
srinivnp
Messages: 136
Registered: January 2006
Location: stlouis MO USA
Senior Member
select tree,cell,pcell
from (
select tree,cell,pcell , connect_by_isleaf leaf
from foo1
start with pcell is null
connect by pcell=prior cell)
where leaf=1


This query identifies those rows that are "leafs"

The same can be achieved in 9i as follows.

select x.tree,x.cell,x.pcell from foo1 x
where x.cell not in
(select y.pcell from foo1 y where y.pcell is not null );

now make suitable modifications to the original sql.

Srini
Re: connect by query [message #213667 is a reply to message #213648] Thu, 11 January 2007 15:23 Go to previous messageGo to next message
jinga
Messages: 115
Registered: January 2003
Senior Member
Srinivas,

i figured how to do with out the use of connect by isleaf.

But now i think last_value(col1 ignore nulls) is not something i can use. 9i does not allow ignore nulls clause i think. Could you point to me to how i can ignore null values in 9i.
Re: connect by query [message #213850 is a reply to message #213439] Fri, 12 January 2007 09:04 Go to previous messageGo to next message
srinivnp
Messages: 136
Registered: January 2006
Location: stlouis MO USA
Senior Member

Try this . I did not have access to a 9i DB . I tested this only on a 10g DB.

Also , you will have to put in your version of "leaf query" .

select tree,cell,col1,col2,col3,col4 from
(
select tree,cell,
last_value(nvl(col1 ,chr(63)))
over (partition by tree,cell order by null) as col1 ,
last_value(nvl(col2 ,chr(63)))
over (partition by tree,cell order by null) as col2 ,
last_value(nvl(col3 ,chr(63)))
over (partition by tree,cell order by null) as col3 ,
last_value(nvl(col4 ,chr(63)))
over (partition by tree,cell order by null) as col4 ,
row_number()
over (partition by tree,cell order by null ) as rn
from (
with qr as (
select tree,cell,pcell
from (
select tree,cell,pcell , connect_by_isleaf leaf
from foo1
start with pcell is null
connect by pcell=prior cell)
where leaf=1
)
select qr.tree,qr.cell
,f2.col1,f2.col2,f2.col3,f2.col4
from qr,foo2 f2
where qr.tree=f2.tree
)
) where rn =1
/




chr(63) was picked assuming it will be less than any of the character values you could have as your column values. Else pick a suitable chr(xx) as per your data



Srini
Re: connect by query [message #214468 is a reply to message #213850] Tue, 16 January 2007 10:09 Go to previous messageGo to next message
jinga
Messages: 115
Registered: January 2003
Senior Member
Srinivas,

query seems to be not working for the following test data.


tree   cell     col1     col2      col3     col4
----------------------------------------------------
T1	C6	a	b	  [NULL]   [NULL]
T1	C4	[NULL]	q	   c	   [NULL]
T1	C2	[NULL]	[NULL]	  [NULL]    d



Please see that parent of c6(c4) has the col2 as 'q' and c6's col2 is 'b'. In this case i want col2 value to be 'b'(Because non null value is already there at c6 cell level, i dont want 'q'.It is my mistake that i should have given the correct data when i posted the query.


select tree,cell,col1,col2,col3,col4 from 
(
select tree,cell,
last_value(nvl(col1 ,chr(63))) 
over (partition by tree order by null) as col1 ,
last_value(nvl(col2 ,chr(63))) 
over (partition by tree order by null) as col2 ,
last_value(nvl(col3 ,chr(63))) 
over (partition by tree order by null) as col3 ,
last_value(nvl(col4 ,chr(63))) 
over (partition by tree order by null) as col4 ,
row_number()
over (partition by tree order by null ) as rn 
from (select c.tree, c.cell,col1,col2,col3,col4
      from (Select tree,cell, level as node_level
           from (select distinct tree,cell,pcell
                               from foo1 connect by cell = prior pcell
                               and tree = 'T1'
                               start with tree = 'T1'
                               and cell = 'C6')a
            start with a.PCELL is null 
            connect by a.PCELL = PRIOR a.CELL)a, foo2 c
      where c.tree = a.tree
      and  c.cell = a.cell))
where cell = 'C6'
/


The result should be


tree   cell     col1     col2      col3     col4
----------------------------------------------------
T1	C6	a	  b	    c         d




but the query returns


tree   cell     col1     col2      col3     col4
----------------------------------------------------
T1	C6	a	  q	    c         d




I would always know the tree & lowest level cell(in this case 'T1' & 'C6'). I believe we need to take the last_value based on the node level also.

I would appreciate if you have any ideas that would help me in correcting the above query.

I am just sending the DDL's here.


create  table foo1
(tree  varchar2(3),
 cell  varchar2(3),
 pcell varchar2(3));
 
insert into foo1 values ('T1','C6','C4');
insert into foo1 values ('T1','C4','C2');
insert into foo1 values ('T1','C2','C1');
insert into foo1 values ('T1','C3','C1');
insert into foo1 values ('T1','C1',null) ;

commit;

create table foo2
(TREE VARCHAR2(3),
CELL VARCHAR2(3),
COL1 VARCHAR2(3),
COL2 VARCHAR2(3),
COL3 VARCHAR2(3),
COL4 VARCHAR2(3))

insert into foo2 ( tree, cell,col1,col2,col3,col4)
values ('T1','C6','a','b',null,null) ;
insert into foo2 ( tree, cell,col1,col2,col3,col4)
values ('T1','C4',null,'q','c',null) ;
insert into foo2 ( tree, cell,col1,col2,col3,col4)
values ('T1','C2',null,null,null,'d') ;


commit;


Re: connect by query [message #215518 is a reply to message #213439] Mon, 22 January 2007 12:02 Go to previous messageGo to next message
jinga
Messages: 115
Registered: January 2003
Senior Member
Any one has suggestions for this query that i need.

Thanks,
Anu
Re: connect by query [message #215692 is a reply to message #213439] Tue, 23 January 2007 09:39 Go to previous message
srinivnp
Messages: 136
Registered: January 2006
Location: stlouis MO USA
Senior Member


For 9i , probably you will have to go for PLSQL.

For 10g , yes the first query would work fine.

Srini


Previous Topic: Can we use CLOB for EXECUTE IMMEDIATE?
Next Topic: How to Upload From Client to FTP with PL/SQL Server Pages
Goto Forum:
  


Current Time: Sun Dec 11 02:41:24 CST 2016

Total time taken to generate the page: 0.19005 seconds