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.

select help

dusooAug 6 2008 — edited Aug 6 2008

Hi,

Could you help me creating select that returns rows based on some conditions. Im not sure if it is possible, or I need to create plsql proc to get those data…
Thanks for any tips..

I need find rows partitioned by schedule_id, where p_lock number has changed from 1 to 0 or 0 to 1, excluding rows where p_lock has not changed.
First row retrieved should be where p_lock = 1;

Data :
unique_id, schedule_id, p_lock
10, 15, 0
13, 15, 0
16, 15, 1
19, 15, 0
21, 15, 0
23, 15, 1
24, 15, 0

So from data above I would like to retrieve rows

unique_id, schedule_id, p_lock
16, 15, 1
19, 15, 0
23, 15, 1
24, 15, 0

select 10 as unique_id,15 as schedule_id,0 as p_lock from dual union all
select 13,15,0 from dual union all
select 16,15,1 from dual union all 
select 19,15,0 from dual union all 
select 22,15,0 from dual union all 
select 23,15,1 from dual union all 
select 24,15,0 from dual

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.

Locked on Sep 3 2008

Added on Aug 6 2008

12 comments

2,318 views

SQL & PL/SQL

select help

Comments

Post Details