5

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.

1
  • I deleted my answer. This question belongs on SO, as per the FAQ, tradition, and common sense. Migrating it here is ridiculous, and I'm sick of being forced to use this site.
    – Jon Heller
    May 18, 2012 at 18:27

1 Answer 1

1

This uses the same basic technique as jonearles (+1) (since removed) but eliminates the PIVOT and would require only one function that turns the string into a CASE statement.

SELECT substr(
      MIN (
         CASE WHEN Blank=1 THEN '1 Blank' 
              WHEN Error=1 THEN '2 Error' 
              WHEN InProgress=1 THEN '3 InProgress' 
              WHEN Completed=1 THEN '4 Completed' 
         END
         )
   ,3) state_code
FROM m_object mo
JOIN m_form mf ON mo.id = mf.id
WHERE mo.v = :2 AND mf.formlayout_id = :1;  

I assume the data looks something like this:

drop table m_object;
create table m_object as 
   (select 10 id, 0 blank, 0 error, 0 inprogress, 1 completed, 99 v from dual);
insert into m_object values (11,1,0,0,0,99);
insert into m_object values (12,0,0,0,1,99);
insert into m_object values (13,0,1,1,0,99);

drop table m_form;
create table m_form as (select 10 id, 20 formlayout_id from dual);
insert into m_form values (11,21);
insert into m_form values (12,22);
insert into m_form values (13,20);

Here is what the function might look like if it were done in PL/SQL. It was just thrown together and is only meant to illustrate building the SQL statement and is not meant to be an example of good coding.

set serveroutput on format wrapped

DECLARE
vPassed Varchar2(500) := 'Blank,Error,InProgress,Completed';

   Function MakeCase(pOriginal In Varchar2) Return Varchar2 Is
      vBuilt     Varchar2(500);
      vWord      Varchar2(100);
      vChar      Char(1);
      vWordCount Number(1) := 1;
   Begin
      For vLoop In 1..Length(pOriginal) Loop
         vChar := substr(pOriginal,vLoop,1);
         If (vChar = ',') Then
            If (vBuilt IS NULL) Then
               vBuilt := 'CASE WHEN ';
            End If;
            vBuilt := vBuilt || vWord || '=1 THEN '''
               || to_char(vWordCount,'FM0') || ' ' || vWord || ''' WHEN ';
            vWord := '';
            vWordCount := vWordCount + 1;            
         Else                  
            vWord := vWord || vChar;            
         End If;
      End Loop;
      vBuilt := vBuilt || vWord || '=1 THEN ''' 
         || to_char(vWordCount,'FM0') || ' ' || vWord || ''' ';
      vBuilt := vBuilt || ' END ';
      Return vBuilt;
   End;

BEGIN
   vPassed := MakeCase(vPassed);
   DBMS_Output.Put_Line(vPassed);

   --Use vPassed here to build SQL statement.
END;
/
3
  • Unfortunately, the statuses can change. There is no set number of them, nor are the names always the same for each iteration of this project. It passes in a string of statuses in the order they should be prioritized, and that is what I have to interpret in order to determine the sort order. May 21, 2012 at 16:24
  • Sorry about the confusion. I was showing the version of the query as it would appear to Oracle after the function was run to create it. This may have made more sense when jonearles answer was present. I will add to the answer in a bit to clarify. May 21, 2012 at 18:49
  • Hope that helps. Call the function and pass it your comma separated list in any order. The function will build a case statement that can be used in the query to retrieve the data in the order you want. May 21, 2012 at 19:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.