Forum Stats

  • 3,734,036 Users
  • 2,246,863 Discussions
  • 7,857,008 Comments

Discussions

How to show a row has been "removed" from a table

RichardSquires
RichardSquires Member Posts: 393
edited November 2010 in SQL & PL/SQL
We maintain rows in a table with a version number which will determine the current set of accounts for a version. I need to be able to show the changes between the versions - when a row was ADDED, REMOVED or there was NO CHANGE. I can work out ADDED and NO_CHANGE without any problem, but I'm not sure how I can determine that a row has been REMOVED when it no longer exists in the next version.

I have provided an example piece of SQL below:
with 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 a.*,
       nvl(lead(acct) over (partition by acct order by vers desc),'NULL') ld,
       case when lead(acct) over (partition by acct order by vers desc) is null then
               'ADDED'
           when lead(acct) over (partition by acct order by vers desc) = acct then
               'NO_CHANGE'
           else
               'REMOVED'
           end add_remove
from w_acct1 a
order by vers,acct
Which gives me the following result:

ACCT VERS LD ADD_REMOVE
A1 0 NULL NEW
A2 0 NULL NEW
A3 0 NULL NEW
A1 1 A1 NO_CHANGE
A2 1 A2 NO_CHANGE
A1 2 A1 NO_CHANGE
A4 2 NULL NEW

The result I want is:

ACCT VERS LD ADD_REMOVE
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

Note the REMOVED rows associated with the version even though they don't exist in the dataset for that version number.

Can this be done with analytic functions or some other cool Oracle feature I'm missing?

Regards

Richard

Best Answer

  • Aketi Jyuuzou
    Aketi Jyuuzou Member Posts: 1,072 Bronze Badge
    edited November 2010 Accepted 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      

Answers

  • BluShadow
    BluShadow Member, Moderator Posts: 40,986 Red Diamond
    edited June 2009
    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>
    BluShadow
  • Aketi Jyuuzou
    Aketi Jyuuzou Member Posts: 1,072 Bronze Badge
    edited November 2010 Accepted 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      
  • fac586
    fac586 Senior Technical Architect Member Posts: 19,672 Black Diamond
    or some other cool Oracle feature I'm missing?
    Workspace Manager?
This discussion has been closed.