Skip to Main Content

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!

gather records in compound trigger and process them

TelluriumDBAJul 10 2019 — edited Jul 10 2019

i have the following table. when a record is being inserted, i need to make sure the latest record of a given batch is updated as isLatest = 1. the update also should update the previous isLatest to 0 as well.

tableA

(

batchNumber number(10),

runNumber number(10),

serialNumber  number(10),

isLatest number(1)

)

so i write a compound trigger

i need the trigger to be as follows:

after each row is

begin

<gather records which were inserted  to objectA>

end

after statement is

select a.batchNumber, a.runNumber, a.serialNumber from tableA a inner join objectA o on a.batchNumber = o.batchNumber and a.isLatest = 0;

the above will be collected to an object (objectB).

now, i need to filterout the latest records. so i do this :

select batchNumber, runNumber, serialumber, row_number() over( partition by batchNumber order by runNumver desc, serialNumber desc) as LatestRank from objectB

the above will be collected to objectC

now, object C has to be updated with the TableA

merge into TableA ta using (select * from objectC where LatestRank <>1) R

on (ta.batchNumber = r.batchNumber and ta.runNumber = r.runNumber and ta.serialNumber = r.serialNumber)

when matched then

update set a.isLatest = 0;

===================================

i am confused as what method to use for the "objects". i tried both ref cursor and table type but no luck. Appreciate any help in this regard.

Comments

MichaelS
One way could be
SQL>  select eno, rno, 
      nvl((select xmlagg(xmlelement(x, eno) order by eno).extract('X[1]/text()').getnumberval() from student s2 where s2.rno=s.rno and s2.eno != s.eno),0) r0,
      nvl((select xmlagg(xmlelement(x, eno) order by eno).extract('X[2]/text()').getnumberval() from student s2 where s2.rno=s.rno and s2.eno != s.eno),0) r1,
      nvl((select xmlagg(xmlelement(x, eno) order by eno).extract('X[3]/text()').getnumberval() from student s2 where s2.rno=s.rno and s2.eno != s.eno),0) r2,
      nvl((select xmlagg(xmlelement(x, eno) order by eno).extract('X[4]/text()').getnumberval() from student s2 where s2.rno=s.rno and s2.eno != s.eno),0) r3
  from student s

ENO        RNO           R0         R1         R2         R3
---------- ----- ---------- ---------- ---------- ----------
9352       A0000       6712       9478          0          0
15401      A0006      12410          0          0          0
9478       A0000       6712       9352          0          0
6712       A0000       9352       9478          0          0
12410      A0006      15401          0          0          0
9477       A0001          0          0          0          0

6 rows selected.
Girish Sharma

Thanks Sir.

SQL> select eno, rno,
  2        nvl((select xmlagg(xmlelement(x, eno) order by eno).extract('X[1]/text()').getnumberval() from student s2 where s2.rno=s.rno and s2.eno != s.eno),0) r0,
  3        nvl((select xmlagg(xmlelement(x, eno) order by eno).extract('X[2]/text()').getnumberval() from student s2 where s2.rno=s.rno and s2.eno != s.eno),0) r1,
  4        nvl((select xmlagg(xmlelement(x, eno) order by eno).extract('X[3]/text()').getnumberval() from student s2 where s2.rno=s.rno and s2.eno != s.eno),0) r2,
  5        nvl((select xmlagg(xmlelement(x, eno) order by eno).extract('X[4]/text()').getnumberval() from student s2 where s2.rno=s.rno and s2.eno != s.eno),0) r3
  6    from student s
  7  /
      nvl((select xmlagg(xmlelement(x, eno) order by eno).extract('X[1]/text()').getnumberval() from student s2 where s2.rno=s.rno and s2.eno != s.eno),0) r0,
                                            *
ERROR at line 2:
ORA-00907: missing right parenthesis

Please guide me; since i am not aware of XML query.

Thanks & Kind Regards
Girish Sharma

MichaelS
must be due to 9.2.0.1.0 not supporting order by in xmlagg.

Another modified version which avoids order by in xmlagg is
SQL>  select t2.column_value.extract('X/text()').getnumberval() eno, 
       rno,
       nvl(t.x.extract('X[text() != ' || t2.column_value.extract('X/text()') || '][1]/text()').getnumberval(),0) r0,
       nvl(t.x.extract('X[text() != ' || t2.column_value.extract('X/text()') || '][2]/text()').getnumberval(),0) r1,
       nvl(t.x.extract('X[text() != ' || t2.column_value.extract('X/text()') || '][3]/text()').getnumberval(),0) r2,
       nvl(t.x.extract('X[text() != ' || t2.column_value.extract('X/text()') || '][4]/text()').getnumberval(),0) r3                   
  from (select rno, xmlagg(xmlelement(x,eno)) x 
          from (select rno, eno from student order by eno)
         group by rno) t, 
       table(xmlsequence(t.x.extract('X'))) t2

       ENO RNO           R0         R1         R2         R3
---------- ----- ---------- ---------- ---------- ----------
      6712 A0000       9352       9478          0          0
      9352 A0000       6712       9478          0          0
      9478 A0000       6712       9352          0          0
      9477 A0001          0          0          0          0
     12410 A0006      15401          0          0          0
     15401 A0006      12410          0          0          0

6 rows selected.
Message was edited by:
michaels
Girish Sharma
So many thanks...!
Its all due to your hardworking and experience.

Kind Regards
Girish Sharma
Nicolas Gasparotto

Just an other way, maybe more understandable :

SQL> select a_eno eno,
  2         rno,
  3         nvl(max(decode(rn,1,b_eno)),0) as r0,
  4         nvl(max(decode(rn,2,b_eno)),0) as r1,
  5         nvl(max(decode(rn,3,b_eno)),0) as r2,
  6         nvl(max(decode(rn,4,b_eno)),0) as r3
  7  from   (select a.eno a_eno, a.rno, b.eno b_eno, dense_rank() over (partition by a.rno,a.eno order by b.eno) rn
  8          from   student a, student b
  9          where  a.rno=b.rno
 10          and    a.eno!=b.eno)
 11  group by a_eno,rno
 12  order by rno,eno;

ENO        RNO   R0         R1         R2         R3
---------- ----- ---------- ---------- ---------- ----------
6712       A0000 9352       9478       0          0
9352       A0000 6712       9478       0          0
9478       A0000 6712       9352       0          0
12410      A0006 15401      0          0          0
15401      A0006 12410      0          0          0

SQL>

Nicolas.

Doesn't work, please see next post.
Message was edited by:
N. Gasparotto

Nicolas Gasparotto

Small correction when a eno-rno is alone.
Now that should work :

SQL> select a_eno eno,
  2         rno,
  3         nvl(max(decode(rn,1,b_eno)),0) as r0,
  4         nvl(max(decode(rn,2,b_eno)),0) as r1,
  5         nvl(max(decode(rn,3,b_eno)),0) as r2,
  6         nvl(max(decode(rn,4,b_eno)),0) as r3
  7  from   (select a.eno a_eno, a.rno, b.eno b_eno, decode(a.eno,b.eno,null,row_number() over (partition by a.rno,a.eno order by decode(a.eno,b.eno,null,b.eno))) rn
  8          from   student a, student b
  9          where  a.rno=b.rno)
 10  group by a_eno,rno
 11  order by rno,eno;

ENO        RNO   R0         R1         R2         R3
---------- ----- ---------- ---------- ---------- ----------
6712       A0000 9352       9478       0          0
9352       A0000 6712       9478       0          0
9478       A0000 6712       9352       0          0
9477       A0001 0          0          0          0
12410      A0006 15401      0          0          0
15401      A0006 12410      0          0          0

6 rows selected.

Nicolas.

Message was edited by:
N. Gasparotto

Girish Sharma
Sir,
So many thanks...!

Kind Regards
Girish

Girish Sharma
Nicolas Gasparotto
ok, you see the modifications.

Message was edited by:
N. Gasparotto
Aketi Jyuuzou
612816
This thread deals similar question.
MichaelS
Just an other way, maybe more understandable :
Hopefully following is more understandable and also avoids a self join:
select eno,
       rno, 
       decode (r,1,rn5,2,rn4,3,rn3,4,rn2,5,rn1) r0,
       decode (r,1,rn6,2,rn5,3,rn4,4,rn3,5,rn2) r1,
       decode (r,1,rn7,2,rn6,3,rn5,4,rn4,5,rn3) r2,
       decode (r,1,rn8,2,rn7,3,rn6,4,rn5,5,rn4) r3
  from (select eno, rno,
               row_number()  over (partition by rno order by eno) r,
               lag(eno,4,0)   over (partition by rno order by eno) rn1,
               lag(eno,3,0)   over (partition by rno order by eno) rn2,
               lag(eno,2,0)   over (partition by rno order by eno) rn3,
               lag(eno,1,0)   over (partition by rno order by eno) rn4,
               lead(eno,1,0)  over (partition by rno order by eno) rn5,
               lead(eno,2,0)  over (partition by rno order by eno) rn6,
               lead(eno,3,0)  over (partition by rno order by eno) rn7,
               lead(eno,4,0)  over (partition by rno order by eno) rn8
          from student) s

ENO        RNO   R0         R1         R2         R3        
---------- ----- ---------- ---------- ---------- ----------
6712       A0000 9352       9478       0          0         
9352       A0000 6712       9478       0          0         
9478       A0000 6712       9352       0          0         
9477       A0001 0          0          0          0         
12410      A0006 15401      0          0          0         
15401      A0006 12410      0          0          0         

6 rows selected.
french, currently in the Netherlands
in either case not much luck in uefa cup since yesterday :(

made some minor fixes.

Message was edited by:
michaels
Aketi Jyuuzou

@michaels
your solution is excellent.

My complex solution ;-)

select a.eno,a.rno,
case when b.R0 > 0 then Lead(a.eno,abs(b.R0),0) over(partition by a.rno order by a.eno)
     else Lag(a.eno,abs(b.R0),0) over(partition by a.rno order by a.eno) end as R0,
case when b.R1 > 0 then Lead(a.eno,abs(b.R1),0) over(partition by a.rno order by a.eno)
     else Lag(a.eno,abs(b.R1),0) over(partition by a.rno order by a.eno) end as R1,
case when b.R2 > 0 then Lead(a.eno,abs(b.R2),0) over(partition by a.rno order by a.eno)
     else Lag(a.eno,abs(b.R2),0) over(partition by a.rno order by a.eno) end as R2,
case when b.R3 > 0 then Lead(a.eno,abs(b.R3),0) over(partition by a.rno order by a.eno)
     else Lag(a.eno,abs(b.R3),0) over(partition by a.rno order by a.eno) end as R3
  from (select eno,rno,r0,r1,r2,r3,
        Row_Number() over(partition by rno order by eno) as JoinKey
          from student) a Join
       (select 1 as JoinKey,1 as R0,2 as R1,3 as R2,4 as R3 from dual union all
        select 2,-1, 1, 2, 3 from dual union all
        select 3,-2,-1, 1, 2 from dual union all
        select 4,-3,-2,-1, 1 from dual union all
        select 5,-4,-3,-2,-1 from dual) b
 using(JoinKey)
order by a.eno,a.rno;
  ENO  RNO       R0    R1  R2  R3
-----  -----  -----  ----  --  --
 6712  A0000   9352  9478   0   0
 9352  A0000   6712  9478   0   0
 9477  A0001      0     0   0   0
 9478  A0000   6712  9352   0   0
12410  A0006  15401     0   0   0
15401  A0006  12410     0   0   0
Nicolas Gasparotto
Michaels,
Just an other way, maybe more understandable :
Hopefully following is more understandable and also avoids a self join:
Nice shot !
french, currently in the Netherlands
in either case not much luck in uefa cup since yesterday :(
...unlike Germans... so far.

Nicolas.
1 - 12

Post Details

Added on Jul 10 2019
7 comments
498 views