Union Query Order By Decode 1

[sxschech]

I was able to sort a query using

GROUP BY ACADEMIC_PERIOD, 'Total', COLLEGE
ORDER BY 1, 2, DECODE (COLLEGE,'LA', 1, 'SC', 2, 'BN', 3, 'NS', 4, 'PS', 5);

However, when I changed the query to a union and moved the decode statement to the last line of the query, this no longer worked. "ORA-01785: ORDER BY item must be the number of a SELECT-list expression. Error at Line:30 Column:15"

Any suggestions on changing the sort without making the calculated field visible on the results.

The purpose of the union was to show two different groups and then union that with another query to provide the totals for the two groups.

 

[SantaMufasa]

sxschech,

To simplify things, I recommend that you turn the UNION query into an in-line VIEW, then place the ORDER BY outside of the in-line VIEW. Here is syntactical proof-of-concept for you using a couple of my own tables:

select *
  from (select id, last_name from s_emp
         union
        select id, name from s_region)
 order by 1,2;

 ID LAST_NAME
--- --------------------
  1 North America
  1 Velasquez
  2 Ngao
  2 South America
  3 Africa / Middle East
  3 Nagayama
...
 24 Dancs
 25 Schwartz

Let us know if you have additional questions/problems with this.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[sxschech]

Thanks Mufasa, nice trick. I am trying to learn Oracle SQL and am using SQL Developer after several years of using MS-Access. I have seen a demo of SQL+ that can do subtotals and grand totals, but I haven't gotten that to work yet.

 

[SantaMufasa]

I have seen a demo of SQL+ that can do subtotals and grand totals, but I haven't gotten that to work yet.

Since that is a completely separate issue, and since we don't want to confuse and future readers of this thread, let's have you post a separate SQL*Plus question to any of the Oracle forums (e.g., Oracle 8i, 9i, or 10g), and we can help you become familiar with the report-formatting features of SQL*Plus.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]