Hi Connor, Chris,
Can you please have a look at below table structure.
drop table acct_test;
create table acct_test
(
account_no varchar2(50 char),
country varchar2(50 char),
no_of_txn number(9),
sys_n_status varchar2(1000 char)
);
insert into acct_test values ('ACCT00001','SG',2,'ABC-NEW{2};ABC-PROCESS{3};XYZ:DUMMY{1}');
insert into acct_test values ('ACCT00002','KL',3,'ABC-NEW{3};ABC-PROCESS{2}');
commit;
For every account_no, I need to
1. Break string sys_n_status into rows e.g. ABC-NEW{2};ABC-PROCESS{3};XYZ:DUMMY{1}
ABC NEW
ABC NEW
ABC PROCESS
ABC PROCESS
ABC PROCESS
XYZ DUMMY
2. No multiply #1 rows with no_of_txn and generate IDENTIFIER_STRING per iteration like below:
ACCT_NO COUNTRY SOURCE STATUS IDENTIFIER_STRING
--------- ------- ------ ------- -----------------
ACCT00001 SG ABC NEW TXN0000000001
ACCT00001 SG ABC NEW TXN0000000001
ACCT00001 SG ABC PROCESS TXN0000000001
ACCT00001 SG ABC PROCESS TXN0000000001
ACCT00001 SG ABC PROCESS TXN0000000001
ACCT00001 SG XYZ DUMMY TXN0000000001
ACCT00001 SG ABC NEW TXN0000000002
ACCT00001 SG ABC NEW TXN0000000002
ACCT00001 SG ABC PROCESS TXN0000000002
ACCT00001 SG ABC PROCESS TXN0000000002
ACCT00001 SG ABC PROCESS TXN0000000002
ACCT00001 SG XYZ DUMMY TXN0000000002
ACCT00002 KL ABC NEW TXN0000000003
ACCT00002 KL ABC NEW TXN0000000003
ACCT00002 KL ABC NEW TXN0000000003
ACCT00002 KL ABC PROCESS TXN0000000003
ACCT00002 KL ABC PROCESS TXN0000000003
ACCT00002 KL ABC NEW TXN0000000004
ACCT00002 KL ABC NEW TXN0000000004
ACCT00002 KL ABC NEW TXN0000000004
ACCT00002 KL ABC PROCESS TXN0000000004
ACCT00002 KL ABC PROCESS TXN0000000004
ACCT00002 KL ABC NEW TXN0000000005
ACCT00002 KL ABC NEW TXN0000000005
ACCT00002 KL ABC NEW TXN0000000005
ACCT00002 KL ABC PROCESS TXN0000000005
ACCT00002 KL ABC PROCESS TXN0000000005
Could you please help/suggest on preparing SQL for the same.
You can use the connect by level trick to generate rows. Placing this in a lateral join enables you to repeat each row from the source table based on values from this table.
Which has this form:
from tab, lateral (
select level l from dual
connect by level <= num_rows
)
You can repeat this many times to:
- Split the string into rows
- Duplicate each substing {N} times
- Repeat each transaction NO_OF_TXN times
Giving something like:
with vals as (
select a.*,
regexp_substr ( sys_n_status, '[^;]+', 1, l ) str,
l n
from acct_test a, lateral (
select level l from dual
connect by level <= ( length ( sys_n_status ) - length ( replace ( sys_n_status, ';' ) ) + 1 )
)
), rws as (
select r.*
from vals r, lateral (
select * from dual
connect by level <= regexp_substr ( str, '[0-9]+' )
)
)
select *
from rws, lateral (
select level l from dual
connect by level <= no_of_txn
)
order by account_no, l, n;
ACCOUNT_NO COUNTRY NO_OF_TXN SYS_N_STATUS STR N L
ACCT00001 SG 2 ABC-NEW{2};ABC-PROCESS{3};XYZ:DUMMY{1} ABC-NEW{2} 1 1
ACCT00001 SG 2 ABC-NEW{2};ABC-PROCESS{3};XYZ:DUMMY{1} ABC-NEW{2} 1 1
ACCT00001 SG 2 ABC-NEW{2};ABC-PROCESS{3};XYZ:DUMMY{1} ABC-PROCESS{3} 2 1
ACCT00001 SG 2 ABC-NEW{2};ABC-PROCESS{3};XYZ:DUMMY{1} ABC-PROCESS{3} 2 1
ACCT00001 SG 2 ABC-NEW{2};ABC-PROCESS{3};XYZ:DUMMY{1} ABC-PROCESS{3} 2 1
ACCT00001 SG 2 ABC-NEW{2};ABC-PROCESS{3};XYZ:DUMMY{1} XYZ:DUMMY{1} 3 1
ACCT00001 SG 2 ABC-NEW{2};ABC-PROCESS{3};XYZ:DUMMY{1} ABC-NEW{2} 1 2
ACCT00001 SG 2 ABC-NEW{2};ABC-PROCESS{3};XYZ:DUMMY{1} ABC-NEW{2} 1 2
ACCT00001 SG 2 ABC-NEW{2};ABC-PROCESS{3};XYZ:DUMMY{1} ABC-PROCESS{3} 2 2
ACCT00001 SG 2 ABC-NEW{2};ABC-PROCESS{3};XYZ:DUMMY{1} ABC-PROCESS{3} 2 2
ACCT00001 SG 2 ABC-NEW{2};ABC-PROCESS{3};XYZ:DUMMY{1} ABC-PROCESS{3} 2 2
ACCT00001 SG 2 ABC-NEW{2};ABC-PROCESS{3};XYZ:DUMMY{1} XYZ:DUMMY{1} 3 2
ACCT00002 KL 3 ABC-NEW{3};ABC-PROCESS{2} ABC-NEW{3} 1 1
ACCT00002 KL 3 ABC-NEW{3};ABC-PROCESS{2} ABC-NEW{3} 1 1
ACCT00002 KL 3 ABC-NEW{3};ABC-PROCESS{2} ABC-NEW{3} 1 1
ACCT00002 KL 3 ABC-NEW{3};ABC-PROCESS{2} ABC-PROCESS{2} 2 1
ACCT00002 KL 3 ABC-NEW{3};ABC-PROCESS{2} ABC-PROCESS{2} 2 1
ACCT00002 KL 3 ABC-NEW{3};ABC-PROCESS{2} ABC-NEW{3} 1 2
ACCT00002 KL 3 ABC-NEW{3};ABC-PROCESS{2} ABC-NEW{3} 1 2
ACCT00002 KL 3 ABC-NEW{3};ABC-PROCESS{2} ABC-NEW{3} 1 2
ACCT00002 KL 3 ABC-NEW{3};ABC-PROCESS{2} ABC-PROCESS{2} 2 2
ACCT00002 KL 3 ABC-NEW{3};ABC-PROCESS{2} ABC-PROCESS{2} 2 2
ACCT00002 KL 3 ABC-NEW{3};ABC-PROCESS{2} ABC-NEW{3} 1 3
ACCT00002 KL 3 ABC-NEW{3};ABC-PROCESS{2} ABC-NEW{3} 1 3
ACCT00002 KL 3 ABC-NEW{3};ABC-PROCESS{2} ABC-NEW{3} 1 3
ACCT00002 KL 3 ABC-NEW{3};ABC-PROCESS{2} ABC-PROCESS{2} 2 3
ACCT00002 KL 3 ABC-NEW{3};ABC-PROCESS{2} ABC-PROCESS{2} 2 3 From there it's just a matter of splitting the values out from STR and using dense_rank to assign the transaction numbers.