I have the table AK_ASSIGNMENT_STATUS. Below is the table creation script and insert script:
CREATE TABLE AK_ASSIGNMENT_STATUS
( COMP_SEQ_PATH VARCHAR2(200 ),
PARENT_COMP_SEQ_PATH VARCHAR2(200 ),
ASSIGNMENT VARCHAR2(30 ),
PLAN_LEVEL NUMBER
);
Insert into AK_ASSIGNMENT_STATUS (COMP_SEQ_PATH,PARENT_COMP_SEQ_PATH,ASSIGNMENT,PLAN_LEVEL) values ('12079',null,'Incomplete',0);
Insert into AK_ASSIGNMENT_STATUS (COMP_SEQ_PATH,PARENT_COMP_SEQ_PATH,ASSIGNMENT,PLAN_LEVEL) values ('12079-12080','12079','Incomplete',1);
Insert into AK_ASSIGNMENT_STATUS (COMP_SEQ_PATH,PARENT_COMP_SEQ_PATH,ASSIGNMENT,PLAN_LEVEL) values ('12079-12080-12039','12079-12080','Explicit',2);
Insert into AK_ASSIGNMENT_STATUS (COMP_SEQ_PATH,PARENT_COMP_SEQ_PATH,ASSIGNMENT,PLAN_LEVEL) values ('12079-12080-90001','12079-12080','Explicit',2);
Insert into AK_ASSIGNMENT_STATUS (COMP_SEQ_PATH,PARENT_COMP_SEQ_PATH,ASSIGNMENT,PLAN_LEVEL) values ('12079-12081','12079','Explicit',1);
Insert into AK_ASSIGNMENT_STATUS (COMP_SEQ_PATH,PARENT_COMP_SEQ_PATH,ASSIGNMENT,PLAN_LEVEL) values ('12079-12081-01','12079-12081','Incomplete',2);
Insert into AK_ASSIGNMENT_STATUS (COMP_SEQ_PATH,PARENT_COMP_SEQ_PATH,ASSIGNMENT,PLAN_LEVEL) values ('12079-12081-02','12079-12081','Incomplete',2);
Every row would either have the status as 'Incomplete' or 'Explicit' . However, now my result should show have the following statuses:
- Complete - When all the children are assigned
- Explicit - When the record has been assigned explicity
- Implicit - When the parent row is assigned.
- Incomplete - If the record doesn't fall in any of the above buckets.
I am currently doing through a recursive PL/SQL function. I call the PL/SQL function when plan_level=0. Inside the function, I'm calling recursively by navigating through the children. My question is - Would this be possible in SQL?
Below is the expected result:
Comp_seq_path
| Parent_comp_seq_path
| Status
| Plan_level
|
|---|
| 12079 | null | Complete | 0 |
| 12079-12080 | 12079 | Complete | 1 |
| 12079-12080-12039 | 12079-12080 | Explicit | 2 |
| 12079-12080-90001 | 12079-12080 | Explicit | 2 |
| 12079-12081 | 12079 | Explicit | 1 |
| 12079-12081-01 | 12079-12081 | Implicit | 2 |
| 12079-12081-02 | 12079-12081 | Implicit | 2 |
My Oracle database version is 12.1 "Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production"
Message was edited by: 863303 to add the version
Message was edited by: 863303. The result was incorrect. So, changing it