Skip to toolbar

Forum

How to achieve this...
 
Notifications
Clear all

How to achieve this complex scenario using PLSQL Procedure?  


VK BI
Posts: 13
 VK BI
Guest
(@VK BI)
Joined: 4 months ago

How to acheive this complex scenario using PL SQL procedure?

When all TASKS are in COMPLETED status then event status should be "ACHIEVED"

When One or more tasks in COMPLETED status then "WORK IN PROGRESS"

When one or more tasks in "TROUBLESHOOTING" status then AWAITING FOR TROUBLESHOOT COMPLETE
else AWAITING FOR FIX TO BE DONE by default.

Note Task Priority should be COMPLETED, TROUBLESHOOTING, FIXING

If one task is in COMPLETED and other one is TROUBLESHOOTING the task status would be WORK IN PROGRESS

Similarly if one task is in TROUBLESHOOTING and others in FIXING
then task status should be "AWAITING FOR FIX TO BE DONE".

Please check the attached document for the table and expected output. 

2 Replies
SQL Admin
Posts: 3
Admin
(@sql-admin)
Member
Joined: 3 months ago

Let us see how we can achieve this. These type of status fields are very common in database or data warehouse we frequently use it. 

 I use the table called Work for this example. 

First let us try to use the below query to capture only list of status using list tag function. 

select EVENT_ID, listagg ( WORK_STATUS,'|') within group (order by WORK_STATUS) lst
from
(
select distinct EVENT_ID, WORK_STATUS from work)

group by EVENT_ID
)

I got the below output

EVENT_ID              lst
EA                         COMPLETED
EB                         Fixing| COMPLETED | Troubleshooting
EC                         Fixing | Troubleshooting
ED                         Fixing

Now here one more thing which is instr. Using this, I can check whether the next status is present or not based on some symbol. In my case it is pipe symbol. 

With S as
(
select EVENT_ID, listagg ( WORK_STATUS,'|') within group (order by WORK_STATUS) lst
from
(
select distinct EVENT_ID, WORK_STATUS from work)

group by EVENT_ID
)
select EVENT_ID, instr(lst,'|'),
case
when instr(lst,'COMPLETED')=1 and instr(lst,'|')=0 then 'ACHIEVED'
when instr(lst,'COMPLETED')>1 and instr(lst,'|')>0 then 'WORK IN PROGRESS'
when instr(lst,'Troubleshooting')>1 and instr(lst,'|')>0 then instr(lst,'COMPLETED')=0 then 'AVAITING FOR TROUBLESHOOT COMPLETE'
else
'AWAITING FOR FIX TO BE DONE'
end cc
from S;

After execution, I got the expected output .You may check in the attached document for reference. 

Reply
kumar BI
Posts: 18
 kumar BI
Guest
(@kumar BI)
Joined: 4 months ago

Second Approach

You can also use the below query to achieve the expected results.

With Function Get_status(P_Event_Id Varchar2) Return Varchar2 As
L_Return_Status Varchar2(50) := 'AWAIT FOR FIX';
L_Status Varchar2(50);
L_Count Number;
L_Count1 Number;
L_Count2 Number;
Begin
Select Count (Distinct WORK_STATUS) Into L_Count From task1
Where Event_Id = P_Event_Id;

If L_Count = 1 Then
Select Distinct WORK_STATUS Into L_Status
From task1 Where Event_Id = P_Event_Id;
If L_Status = 'COMPLETED' Then
L_Return_Status := 'ACHIEVED';
End If;

Else
Begin
Select Count(*) Into L_Count1
From task1 Where Event_Id = P_Event_Id And WORK_STATUS = 'COMPLETED';
If L_Count1 > 0 Then
L_Return_Status := 'WORK IN PROGRESS';
Else
Select Count(*) Into L_Count2
From task1 Where Event_Id = P_Event_Id And WORK_STATUS = 'TroubleShooting';
If L_Count2 > 0 Then
L_Return_Status := 'AWAIT FOR TROUBLESHOOT COMPLETE';
Else
L_Return_Status := 'AWAIT FOR FIX';
End If;
End If;
End;

End If;

Return L_Return_Status;
End Get_status;
Select distinct A.Event_Id, Get_status(A.Event_Id)
From task1 A, Task1 B
Where A.Event_id = B.Event_Id

Reply

Leave a reply

Author Name

Author Email

Title *

Maximum allowed file size is 10MB

 
Preview 0 Revisions Saved
Share: