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.

case statement

bnjutkJun 16 2009 — edited Nov 6 2010
hi
I have data like this in the table

date value
1-MAY-09 20
2-MAY-09 30
3-MAY-09 40
4-MAY-09 50
5-MAY-09 60
1-JUN-09 70
2-JUN-09 80
3-JUN-09 90
4-JUN-09 10
5-JUN-09 60

i want to have like this with a query

date value value_last_month
1-MAY-09 20
2-MAY-09 30
3-MAY-09 40
4-MAY-09 50
5-MAY-09 60
1-JUN-09 70 20
2-JUN-09 80 30
3-JUN-09 90 40
4-JUN-09 10 50
5-JUN-09 60 60

i mean in third column i want to get the value of same date lastmonth
can any one help me out?


thanks
balaji

Edited by: chinnu on Jun 16, 2009 2:51 AM

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 Jul 15 2009
Added on Jun 16 2009
14 comments
1,751 views