Why are scalar functions and case statements not allowed in the ORDER BY clause of a compound query? What Oracle SQL rules are violated by attempting this?
By "compound" query, I'm referring to a query composed of two (or more) distinct queries combined using the set operators UNION, UNION ALL, INTERSECT, and MINUS.
Scalar functions and case statements are typically allowed in the ORDER BY clause, but not with a compound query. The ORDER BY clause works with a compound query, but not when it contains a scalar function or a case statement. These two statements appear to be rules in SQL, but I haven't found any documentation that addresses this specific issue one way or the other.
I'll create two similar tables then UNION them together. The ORDER BY clause with a function or CASE statement works fine with both tables when run individually, but not when unioned together. It also works with the UNION, but not if the ORDER BY contains a function.
create table myschema.vip_customers
(
firstname varchar2(20) not null,
lastname varchar2(20) not null,
order_ct number(5)
);
create table myschema.musical_customers
(
firstname varchar2(20) not null,
lastname varchar2(20) not null,
order_ct number(5)
);
insert into myschema.vip_customers values ('George','Washington',89);
insert into myschema.vip_customers values ('John','Adams',97);
insert into myschema.vip_customers values ('Thomas','Jefferson',1);
insert into myschema.vip_customers values ('James','Madison',9);
insert into myschema.musical_customers values ('Wolfgang','Mozart',1756);
insert into myschema.musical_customers values ('Ludwig','Beethoven',1770);
insert into myschema.musical_customers values ('Johann','Bach',1685);
insert into myschema.musical_customers values ('Pyotr','Tchaikovsky',1840);
commit;This query succeeds:
select firstname, lastname, order_ct
from myschema.vip_customers
union all
select firstname, lastname, order_ct
from myschema.musical_customers
order by firstname
This query fails:
select firstname, lastname, order_ct
from myschema.vip_customers
union all
select firstname, lastname, order_ct
from myschema.musical_customers
order by lower(firstname)
Error message:
ORA-01785: ORDER BY item must be the number of a SELECT-list expression
It appears that all scalar functions (decode, trunc, round, nvl, length, substr, etc.) in the ORDER BY clause result in this error. I initially found the issue while attempting to use a DECODE to make customized ordering on a column, but I'm using a LOWER function in this example in hopes of simplifying the scenario (obviously LOWER won't have an impact on the given dataset). As stated above, DECODE (and others) in the ORDER BY clause works fine on each table when run individually, but unioning the tables together causes it to fail. Attempting to reword the DECODE using a CASE statement results in the same error. However, ordering simply on the column itself (by name or number) works fine. Also, unioning the first table to itself (instead of using the 2nd table) also causes the error, so the issue appears to not be due to table structure mismatch, datatype mismatch, or an attempt to ORDER on a column that doesn't exist. I'm not sure what else to rule out and I still haven't found any reason why this shouldn't succeed.
Known workaround:
Wrapping the compound query with an outer query results in it running successfully:
select * from
(
select firstname, lastname, order_ct
from myschema.vip_customers
union all
select firstname, lastname, order_ct
from myschema.musical_customers
)
order by lower(firstname)
Thanks.
-Ben