Hi,
I have the below table and sample data. Using PL/SQL stored procedure, through Cursor, I want to iterate the data and have the below output.
The real output is more complex, very difficult to present it fully, I don't want to use SQL Select and Analytical queries to achieve this, I need to iterate it in a Cursor only and get the below output.
The below code snippet is a Skeleton, please let me know how to have nested iterations for a single cursor and process this in a PL/SQL block. I don't want to use a single loop and multiple IF condition within it. I would like to have 3 loops, 1st main loop, 2nd as long as the DeptId is same, 3rd as long as the Empid is same within the Deptid.
D001, E001, Dept 1, Row1
D001, E001, Dept 1, Row2
D001, E002, Dept 1, Row1
D001, E002, Dept 1, Row2
D001, E001, Dept 1, Row1
D002, E003, Dept 2, Row1
D002, E003, Dept 2, Row2
D002, E004, Dept 2, Row1
D002, E004, Dept 2, Row2
D002, E003, Dept 2, Row1
create table tmp_dept_emp_txn
(deptid varchar2(50), empid varchar2(50), txndate date, emp_flag varchar2(10));
insert into tmp_dept_emp_txn
values('D001', 'E001', sysdate, 'A');
insert into tmp_dept_emp_txn
values('D001', 'E001', sysdate, 'B');
insert into tmp_dept_emp_txn
values('D001', 'E002', sysdate, 'A');
insert into tmp_dept_emp_txn
values('D001', 'E002', sysdate, 'D');
insert into tmp_dept_emp_txn
values('D001', 'E001', sysdate, 'Z');
insert into tmp_dept_emp_txn
values('D002', 'E003', sysdate, 'A');
insert into tmp_dept_emp_txn
values('D002', 'E003', sysdate, 'E');
insert into tmp_dept_emp_txn
values('D002', 'E004', sysdate, 'L');
insert into tmp_dept_emp_txn
values('D002', 'E004', sysdate, 'N');
insert into tmp_dept_emp_txn
values('D002', 'E003', sysdate, 'T');
DECLARE
cursor_dept_emp
is select * from tmp_dept_emp_txn order by empid, txndate;
v_tmp_dept_emp_txn tmp_dept_emp_txn%ROWTYPE;
BEGIN
FETCH cursor_dept_emp INTO v_tmp_dept_emp_txn;
EXIT WHEN cursor_dept_emp%NOTFOUND;
LOOP -- main loop
LOOP -- for deptid, as long as they are same
LOOP -- within this deptid, for empid, as long as they are same
END LOOP;
END LOOP;
END LOOP;
CLOSE cursor_dept_emp;
END;