Skip to Main Content
Forums

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.

connect_by_isleaf - oracle 9i ?

591942Sep 3 2007 — edited Nov 6 2010
Hi All,

Am using Oracle 9i. And My requrement is to know the element is a leaf or node.
In oracle 10g we have connect_by_isleaf for this purpose.
How can I achieve the same in Oracle 9i.

Please help me...

Comments

BluShadow
You can't know about a row being removed unless you have a record of that row somewhere, either as a copy of your old data (see example below) or you've recorded the information using delete triggers etc.
SQL> ed
Wrote file afiedt.buf

  1  with old_data as (select 1 as id, 'A' as dta from dual union all
  2                     select 2, 'B' from dual union all
  3                     select 3, 'C' from dual)
  4      ,new_data as (select 1 as id, 'A' as dta from dual union all
  5                    select 3, 'X' from dual union all
  6                    select 4, 'Y' from dual)
  7  --
  8      ,ins_upd as (select * from new_data minus select * from old_data)
  9      ,del_upd as (select * from old_data minus select * from new_data)
 10      ,upd as (select id from ins_upd intersect select id from del_upd)
 11      ,ins as (select id from ins_upd minus select id from upd)
 12      ,del as (select id from del_upd minus select id from upd)
 13  --
 14  select 'Inserted' as action, null as old_id, null as old_dta, new_data.id as new_id, new_data.dta as new_dta
 15  from new_data join ins on (ins.id = new_data.id)
 16  union all
 17  select 'Updated', old_data.id, old_data.dta, new_data.id, new_data.dta
 18  from old_data join new_data on (old_data.id = new_data.id)
 19                join upd on (upd.id = new_data.id)
 20  union all
 21  select 'Deleted', old_data.id as old_id, old_data.dta as old_dta, null as new_id, null as new_dta
 22  from old_data join del on (del.id = old_data.id)
 23  union all
 24  select 'No Change' as action, new_data.id as old_id, new_data.dta as old_dta, new_data.id as new_id, new_data.dta as new_dta
 25  from new_data where id not in (select id from ins_upd union all
 26*                                select id from del_upd)
SQL> /

ACTION        OLD_ID O     NEW_ID N
--------- ---------- - ---------- -
Inserted                        4 Y
Updated            3 C          3 X
Deleted            2 B
No Change          1 A          1 A

SQL>
1 person found this helpful
Aketi Jyuuzou
Answer
I used OLAP B-)

My OLAP articles (in Japanese)
http://www.geocities.jp/oraclesqlpuzzle/oow2009-olap-model.html
http://codezine.jp/article/corner/71
create table w_acct1 as
select 'A1' acct, 0 vers from dual union all
select 'A2' acct, 0 vers from dual union all
select 'A3' acct, 0 vers from dual union all
select 'A1' acct, 1 vers from dual union all
select 'A2' acct, 1 vers from dual union all
select 'A1' acct, 2 vers from dual union all
select 'A4' acct, 2 vers from dual;

select acct,vers,
case when appearCnt=1 and preCnt = 0 then 'null'
     when appearCnt=preCnt then 'null'
     else acct end as LD,
case when appearCnt=1 and preCnt = 0 then 'NEW'
     when appearCnt=preCnt then 'REMOVED'
     else 'NO_CHANGE' end as ADD_REMOVE
from (select a.acct,b.vers,
      count(b.acct)
      over(partition by a.acct order by b.vers) as appearCnt,
      count(b.acct)
      over(partition by a.acct
           order by b.vers rows between unbounded preceding
                                    and 1 preceding) as preCnt,
      Lag(b.acct) over(partition by a.acct order by b.vers) as LagVal
        from (select distinct acct from w_acct1) a
        Left Join w_acct1 b
      partition by (b.vers)
          on a.acct = b.acct)
 where appearCnt > preCnt
    or LagVal is not null
order by vers,acct;

AC  VERS  LD    ADD_REMOV
--  ----  ----  ---------
A1     0  null  NEW      
A2     0  null  NEW      
A3     0  null  NEW      
A1     1  A1    NO_CHANGE
A2     1  A2    NO_CHANGE
A3     1  null  REMOVED  
A1     2  A1    NO_CHANGE
A2     2  null  REMOVED  
A4     2  null  NEW
Marked as Answer by RichardSquires · Sep 27 2020
fac586
or some other cool Oracle feature I'm missing?
Workspace Manager?
1 - 3
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Nov 7 2007
Added on Sep 3 2007
2 comments
6,064 views