ORDER BY issue on PLSQL

Question

I have set of data which should be fetched out and should prepare a report in the following order

Last Name      First Name      Location
Blake          James           101007
fusio          Wiko            101008 
                     100 Total

FAROMOJU       BANKOLE         303315
Gibbs          Rhonda          303315
Greene         Leette          303315
                     331 Total

......
............

In that we can see that a 3 letter code [extracted from the location code - CONCAT(SUBSTR(COLUMN3,3,3),'Total')] after all location set. I just figured out to get the report and it works wel except in the case of descending order of location code. It uses 'location' to order by after making it into the 3 letter code instead of using the full number code. So in the result the descending order is not working well as I need. Please give me a solution,

I am attaching my code,

******************************************************

FUNCTION GET_ACTY_SUMMARY(V_STARTDATE IN DATE,V_ENDDATE IN DATE)
RETURN TEMP_OUTPUT_TABLE PIPELINED IS
CURSOR ACTY_SUMMARY IS

/* The query fetches the report field grouped by First Name,Last Name
and Location.*/

   SELECT
          COLUMN1 AS LASTNAME,
          COLUMN2 AS FIRSTNAME,
          COLUMN3 AS LOCATION,
          SUM(COLUMN4) AS ACTIVITYNM1,
          SUM(COLUMN5) AS ACTIVITYNM2,
          SUM(COLUMN6) AS ACTIVITYNM3,
          SUM(COLUMN7) AS ACTIVITYNM4,
          SUM(COLUMN8) AS ACTIVITYNM5,
          SUM(COLUMN9) AS REG_HRS,
          SUM(COLUMN10) AS OT_HRS,
          SUM(COLUMN11) AS TOTAL_HRS,
          SUM(COLUMN12) AS PRODUCTIVITY_PERCENTAGE

  FROM
  TABLE(ACTY_COLUMN(V_STARTDATE,V_ENDDATE))
  GROUP BY
   COLUMN1,
   COLUMN2,
   COLUMN3


  UNION

  SELECT NULL,
       NULL,
       CONCAT(SUBSTR(COLUMN3,3,3),'Total'),
       SUM(COLUMN4) AS ACTIVITYNM1,
       SUM(COLUMN5) AS ACTIVITYNM2,
       SUM(COLUMN6) AS ACTIVITYNM3,
       SUM(COLUMN7) AS ACTIVITYNM4,
       SUM(COLUMN8) AS ACTIVITYNM5,
       SUM(COLUMN9) AS REG_HRS,
       SUM(COLUMN10) AS OT_HRS,
       SUM(COLUMN11) AS TOTAL_HRS,
       SUM(COLUMN12) AS PRODUCTIVITY_PERCENTAGE

  FROM TABLE(ACTY_COLUMN(V_STARTDATE,V_ENDDATE))
  GROUP BY COLUMN3
  UNION

  SELECT NULL,
       NULL,
       'Total',
       SUM(COLUMN4) AS ACTIVITYNM1,
       SUM(COLUMN5) AS ACTIVITYNM2,
       SUM(COLUMN6) AS ACTIVITYNM3,
       SUM(COLUMN7) AS ACTIVITYNM4,
       SUM(COLUMN8) AS ACTIVITYNM5,
       SUM(COLUMN9) AS REG_HRS,
       SUM(COLUMN10) AS OT_HRS,
       SUM(COLUMN11) AS TOTAL_HRS,
       SUM(COLUMN12) AS PRODUCTIVITY_PERCENTAGE

  FROM TABLE(ACTY_COLUMN(V_STARTDATE,V_ENDDATE))


  ORDER BY 3 desc ;

GAS ACTY_SUMMARY%ROWTYPE;

/*Intialize the table output format with NULL.*/

TT TEMP_OUTPUT_FORMAT := INITIALIZE_TABLE_FORMAT();
  BEGIN
  OPEN ACTY_SUMMARY;
  FETCH ACTY_SUMMARY INTO GAS;
    WHILE ACTY_SUMMARY%FOUND LOOP
      BEGIN
        TT.COLUMN1 := GAS.LASTNAME;
        TT.COLUMN2 := GAS.FIRSTNAME;
        TT.COLUMN3 := GAS.LOCATION;
        TT.COLUMN4 := GAS.ACTIVITYNM1;
        TT.COLUMN5 := GAS.ACTIVITYNM2;
        TT.COLUMN6 := GAS.ACTIVITYNM3;
        TT.COLUMN7 := GAS.ACTIVITYNM4;
        TT.COLUMN8 := GAS.ACTIVITYNM5;
        TT.COLUMN9 := GAS.REG_HRS;
       TT.COLUMN10 := GAS.OT_HRS;
       TT.COLUMN11 := GAS.TOTAL_HRS;
       TT.COLUMN12 := GAS.PRODUCTIVITY_PERCENTAGE;
       PIPE ROW(TT);
     END;
    FETCH ACTY_SUMMARY INTO GAS;
   END LOOP;
  CLOSE ACTY_SUMMARY;
  RETURN;
END GET_ACTY_SUMMARY;

END GEHC_ACTY_REPT_PKG;


******************************************************

Thanks Anand

Answer 1

I suggest you change the query to be

   SELECT 
          COLUMN1 AS LASTNAME, 
          COLUMN2 AS FIRSTNAME, 
          COLUMN3 AS LOCATION,
          COLUMN3 AS FULL_LOCATION,
          SUM(COLUMN4) AS ACTIVITYNM1, 
          SUM(COLUMN5) AS ACTIVITYNM2, 
          SUM(COLUMN6) AS ACTIVITYNM3, 
          SUM(COLUMN7) AS ACTIVITYNM4, 
          SUM(COLUMN8) AS ACTIVITYNM5, 
          SUM(COLUMN9) AS REG_HRS, 
          SUM(COLUMN10) AS OT_HRS, 
          SUM(COLUMN11) AS TOTAL_HRS, 
          SUM(COLUMN12) AS PRODUCTIVITY_PERCENTAGE 
  FROM TABLE(ACTY_COLUMN(V_STARTDATE,V_ENDDATE)) 
  GROUP BY 
   COLUMN1, 
   COLUMN2, 
   COLUMN3
UNION ALL
  SELECT NULL, 
       NULL, 
       CONCAT(SUBSTR(COLUMN3,3,3),'Total'),
       COLUMN3 AS FULL_LOCATION,
       SUM(COLUMN4) AS ACTIVITYNM1, 
       SUM(COLUMN5) AS ACTIVITYNM2, 
       SUM(COLUMN6) AS ACTIVITYNM3, 
       SUM(COLUMN7) AS ACTIVITYNM4, 
       SUM(COLUMN8) AS ACTIVITYNM5, 
       SUM(COLUMN9) AS REG_HRS, 
       SUM(COLUMN10) AS OT_HRS, 
       SUM(COLUMN11) AS TOTAL_HRS, 
       SUM(COLUMN12) AS PRODUCTIVITY_PERCENTAGE 
  FROM TABLE(ACTY_COLUMN(V_STARTDATE,V_ENDDATE)) 
  GROUP BY COLUMN3 
UNION ALL
  SELECT NULL, 
       NULL, 
       'Total',
       'Total' AS FULL_LOCATION,
       SUM(COLUMN4) AS ACTIVITYNM1, 
       SUM(COLUMN5) AS ACTIVITYNM2, 
       SUM(COLUMN6) AS ACTIVITYNM3, 
       SUM(COLUMN7) AS ACTIVITYNM4, 
       SUM(COLUMN8) AS ACTIVITYNM5, 
       SUM(COLUMN9) AS REG_HRS, 
       SUM(COLUMN10) AS OT_HRS, 
       SUM(COLUMN11) AS TOTAL_HRS, 
       SUM(COLUMN12) AS PRODUCTIVITY_PERCENTAGE 
  FROM TABLE(ACTY_COLUMN(V_STARTDATE,V_ENDDATE)) 
  ORDER BY 4 DESC;

What we're trying to do here is to add the complete location (COLUMN3 in your data, if I'm reading the original query correctly) to the query so that the sort can be based on it. You may need to alter the GROUP BY's - I'm not sure and don't have your data available to test with. But that's the basic idea.

Share and enjoy.

Answer 2

I would recommend looking at grouping sets, rather than using unioning data together. Grouping sets (along with CUBE and ROLLUP) are ways of aggregating data at multiple levels, which is what you seem to be doing. Using grouping sets will get the data you are looking for.

In order to sort the data, you need to add additional columns to the order by clause. The grouping_id() function I have used returns a number (0 or 1) depending on whether it is a 'superaggregate' row or not for the given expression. These 'superaggregate' rows are the additional rows that relate to totals and subtotals. I am sorting on the data and on whether the column is a superaggregate or not.

Think you should be able to do something like:

SELECT 
  COLUMN1 AS LASTNAME, 
  COLUMN2 AS FIRSTNAME, 
  case 
    when grouping_id(column3, substr(column3,3,3)) = 3 then 'Total'
    when grouping_id(column3, substr(column3,3,3)) = 2 
      then substr(column3,3,3) ||' Total'
    else column3 end as location,
  SUM(COLUMN4) AS ACTIVITYNM1, 
  SUM(COLUMN5) AS ACTIVITYNM2, 
  SUM(COLUMN6) AS ACTIVITYNM3, 
  SUM(COLUMN7) AS ACTIVITYNM4, 
  SUM(COLUMN8) AS ACTIVITYNM5, 
  SUM(COLUMN9) AS REG_HRS, 
  SUM(COLUMN10) AS OT_HRS, 
  SUM(COLUMN11) AS TOTAL_HRS, 
  SUM(COLUMN12) AS PRODUCTIVITY_PERCENTAGE 
FROM TABLE(ACTY_COLUMN(V_STARTDATE,V_ENDDATE)) 
GROUP BY grouping sets ((),  (substr(column3,3,3)), 
    (COLUMN1,COLUMN2,COLUMN3, substr(column3,3,3)))
order by 
  grouping_id(substr(column3,3,3)), 
  substr(column3,3,3) desc, 
  grouping_id(column3, substr(column3,3,3)), 
  column3 desc

Will hopefully run quicker and do what you want. I might have made the order by clause more complicated than necessary but it should do what you need.