CREATE TABLE transact (
seq_id NUMBER(10),
q_id NUMBER(10),
a_val VARCHAR2(20),
ref_pa_id NUMBER(10),
seq NUMBER(10)
);
INSERT INTO transact VALUES(11,13,null,992,1);
INSERT INTO transact VALUES(11,13,null,637,2);
INSERT INTO transact VALUES(11,14,'Manual',null,3);
INSERT INTO transact VALUES(11,15,null,083,1);
Above is the table transact
with data for which I need to load the same data into the another table transact_entry
but with some conditions as mentioned below:
Target table :
CREATE TABLE transact_entry (
seq_id NUMBER(10),
ref_id NUMBER(10),
sys_otr VARCHAR2(20),
int_otr VARCHAR2(20)
);
Conditions:
I need to group the data as per the sequence. In the above data, we have total of 4 entries out of which we need to check
seq
column and find the unique. So, unique will come as 3 i.e sequence 1,2,3q_id
are static. So, we need to insert the record into thetransact_entry
table based on theseq_id
i.e 13,14,15Lets start with
seq
1 which has twoq_id
i.e 13 and 15. Then we need to load these records intotransact_entry
table. For 13, we will insert theref_pa_id
oftransact
table into the target table columnref_id
and for 15, we will insert thea_val
oftransact
table into the target tabletransact_entry
columnint_otr
Lets now check for
seq
2. Ifq_id
is 13 then we will insert theref_pa_id
oftransact
table into the target tabletransact_entry
columnref_id
Lets now check for
seq
3. Ifq_id
is 14 then we will insert thea_val
oftransact
table into the target tabletransact_entry
columnsys_otr
Expected output:
-------- -------- --------- ---------
| seq_id | ref_id | sys_otr | int_otr |
-------- -------- --------- ---------
| 11 | 992 | | null |
| 11 | 637 | | |
| 11 | | Manual | |
-------- -------- --------- ---------
Tool Used: SQL Developer(18c)
CodePudding user response:
Looks like you want a conditional aggregation, kind of
select seq_id,
max(case q_id when 13 then ref_pa_id end) refid,
max(case q_id when 15 then a_val end) int_otr,
max(case q_id when 14 then a_val end) sys_otr
from transact
group by seq_id, seq
order by seq_id, seq
CodePudding user response:
declare
type t_v_seq is table of transact.seq%type INDEX BY BINARY_INTEGER;
v_seq t_v_seq;
type t_v_trans is table of transact%rowtype INDEX BY BINARY_INTEGER;
v_trans t_v_trans;
begin
select DISTINCT seq bulk collect into v_seq from transact;
if v_seq.count >0 then
for i in v_seq.first..v_seq.last loop
if v_seq.exists(i) then
select * bulk collect into v_trans from transact where seq=v_seq(i);
if v_trans.count>0 then
for r in v_trans.first..v_trans.last loop
if v_trans.exists(r) then
if v_trans(r).q_id = 13 then
insert into transact_entry (seq_id,ref_id,sys_otr,int_otr)
values (v_trans(r).seq_id , v_trans(r).ref_pa_id,v_trans(r).a_val,null);
elsif v_trans(r).q_id = 14 then
insert into transact_entry (seq_id,ref_id,sys_otr,int_otr)
values (v_trans(r).seq_id ,null,v_trans(r).a_val,null);
elsif v_trans(r).q_id = 15 then
insert into transact_entry (seq_id,ref_id,sys_otr,int_otr)
values (v_trans(r).seq_id ,null,null,v_trans(r).a_val);
end if;
end if;
end loop;
end if;
end if;
end loop;
end if;
delete from transact_entry where ref_id is null and sys_otr is null and int_otr is null;
end;