Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

migrated from stackoverflow.com May 18 '12 at 16:23

This question came from our site for professional and enthusiast programmers.

    
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. –  jonearles May 18 '12 at 18:27

1 Answer 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;
/
share|improve this answer
    
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. –  guildsbounty May 21 '12 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. –  Leigh Riffel May 21 '12 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. –  Leigh Riffel May 21 '12 at 19:17

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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