Oracle Apex 20.1 Set Status to ‘Fullfilled’ or ‘Pending’ based on overall project status

in my table I have a column for a project which can have multiple project milestones (second column). Each of the milestone can have the value ‘fullfilled’ or ‘pending’ as current_status (third column). Now I want to aggregate the overall status of each project and assign it a final status (new virtual column) for better overview.

The following rule should apply:

• If every milestone is fullfiled then the status of a project should change to ‘Finished’

• If one or more milestones are pending then the status of project should be ‘On-Hold’

Meaning that I want to checklist every current status per project to get a final_status.

Any idea how to do this with a case-when-clause or something similar?

Thank you.

Kind regards.

Answer

If there are only two statuses, then check whether the only status is ‘finished’.

Sample data in lines #1 – 9:

• project 1 is finished as its all milestone statuses are “fulfilled”
• project 2 is still on hold as only milestone is “fulfilled”

---

SQL> with project (projno, mileno, milestatus) as
  2    (select 1, 1, 'fulfilled' from dual union all
  3     select 1, 2, 'fulfilled' from dual union all
  4     select 1, 3, 'fulfilled' from dual union all
  5     --
  6     select 2, 1, 'fulfilled' from dual union all
  7     select 2, 2, 'pending'   from dual union all
  8     select 2, 3, 'pending'   from dual
  9    )
 10  select projno,
 11    case when min_status = max_status and
 12              max_status = 'fulfilled'
 13         then 'Finished'
 14    else 'On hold'
 15    end project_status
 16  from (select projno,
 17          min(milestatus) min_status,
 18          max(milestatus) max_status
 19        from project
 20        group by projno
 21       );

    PROJNO PROJECT_
---------- --------
         1 Finished
         2 On hold

SQL>