Oracle query to extract distinct routes

Question

Burning out a brain cell... there has to be a simple way to do this.

I've inherited the following tables:

approval_path
approval_path_steps
applications
application_roles
requests
role_approvals

A user requests an application role for an application, which must go through an approval path, the steps of which are defined in approval_path_steps. The approval history for each step of the approval path is stored in role_approvals. So:

    approval_path:
    -> (p)approval_path_id 
   |                            
    -------------------------
                             |
    approval_path_steps:     |
       (p)approval_path_id --|
 -->   (p)sequence_nbr       |
|         approver           |
|                            |
|                            |
|   applications:            |
|   -> (p)application_id     |
|  |      approval_path_id --
|  |
|   -------------------------
|                            |
|   application_roles:       |
|   -> (p)role_id            |
|  |      application_id  ---   
|  |                        
|   -------------------------
|                            |
|   requests:                |
|   -> (p)request_nbr        |
|  |      role_id         --- 
|  |      requestor
|  |
|   -------------------------
|                            |
|   role_approvals:          |
|      (p)request_nbr     --- 
 ----  (p)sequence_nbr        (NOT ACTUALLY KEYED!!!  ENTERED MANUALLY!!)
          approver
          status

where (p) indicates the primary key. Fields not immediately relevant have been omitted. (btw, this was not my design)

The problem: Approval path steps have changed over time for a given approval path; steps have been added, removed, or changed from one approver to another. Therefore, the approval_path_steps that were actually taken for a request don't match the approval_path_steps that are currently defined for the requested role's approval_path.

What I need: I need to query the role_approvals table in such a way that I can list the distinct paths that were used. So:

role_approvals
--------------
 1000  role1  1  manager    approved
 1000  role1  2  hr_mgr     approved
 1000  role1  3  app_owner  approved

 1001  role1  1  manager    approved
 1001  role1  2  hr_mgr     approved
 1001  role1  3  app_owner  approved

 1002  role1  1  app_owner  approved
 1002  role1  2  manager    approved

The results I want:

id  seq_nbr  approver
--  -------  --------
 1        1  manager
 1        2  hr_mgr
 1        3  app_owner

 2        1  app_owner
 2        2  manager

where 'id' can be calculated in some identifying way, it doesn't matter how, to identify that unique approval path that was taken.

Any ideas?

Thanks in advance! James

Answer 1

This is only a partial solution. Sadly, I am getting ORA-600 errors when I try to build on this to convert it back into the original format. But at least it will get you the distinct paths.

Basically, it seems you need to aggregate the approver text field by the request number, and find distinct values of the aggregate. XML functions are the only (built-in) text aggregation method that I know of in Oracle 10g.

select
  distinct xmlserialize(CONTENT approver_path AS VARCHAR2(2000)) distinct_path
from (
  select
    request_nbr,
    xmlagg(xmlelement("Approver",approver) order by sequence_nbr) approver_path
  from
    role_approvals
  group by
    request_nbr
)