This is a complicated explanation, so sorry that it is going to be a wall of text. I'm dealing with pre-existing code and am trying to work out a solution, so I can't alter the core way any of this works.
Essentially, I have a series of forms in a project, all of which have a status assigned to them. The statuses have an assigned order of precedence, which will trickle up to the higher level visits. The way this is currently working uses the following sql statement:
select state_code
from (select ' || v_priorstr || '
from m_object mo, m_form mf
where mo.id = mf.id
and mf.formlayout_id = :1
and mo.v = :2)
unpivot
(
state_counts
for state_code in (' || v_priorstr || ')
) where state_counts = 1 and rownum = 1
where v_priorstr is a comma separated string of the statuses in order of their precedence. As an example, consider the following string: "Blank,Error,InProgress,Completed". The m_object table contains each of these columns, with either a 0 or 1 to show which statuses apply. (Forms can have more than one status) This query selects the highest priority status that it will then use to display a status icon for that form.
The problem arises when dealing with repeating forms (which can have multiple iterations of themselves under one link). They all share a single status icon in the navigation system.
While normally this works fine, since SQL will naturally display columns in the order you request them, and they maintain that order when unpivoted. However, with the multiple forms, and thus multiple rows, all that statuses of the first form/row take absolute precedence over the statuses of all the forms.
For example, if I have two of these repeating forms, the first of which is 'Completed' the second is 'Error', the above query (sans the rownum = 1) will return the following two rows:
Completed
Error
I have thus far attempted the following solutions:
Group By without an Order By throws the order off. I can't use a case-based Order By because the status names can change.
In short, I need it to either sort by or group by the status names, in the order that is passed in via that string.