Curious Issue with Oracle Union and Order By

Curious issue with Oracle UNION and ORDER BY

This doesn't really answer the question, but it seems to be a parser bug (or 'feature') rather than a language requirement.

According to My Oracle Support, this seems to have been raised as bug 14196463 but closed with no resolution. It's also mentioned in community thread 3561546. You need a MOS account, or at least an Oracle account, to see either of those though.

It's also been discussed in an OTN thread which requires a basic Oracle login rather than a MOS account, as far as I can tell. That also doesn't have much information but repeats your findings, and also suggests the behaviour has existed back at least to 9.2.0.8 and perhaps much earlier.

The documentation is a bit vague but doesn't indicate this is expected to be a problem:

For compound queries containing set operators UNION, INTERSECT, MINUS, or UNION ALL, the ORDER BY clause must specify positions or aliases rather than explicit expressions. Also, the ORDER BY clause can appear only in the last component query. The ORDER BY clause orders all rows returned by the entire compound query.

You are aliasing your expression and using that, and it doesn't say you have to alias particular components (although of course it doesn't say you don't have to either).

The behaviour seems inconsistent with the alias being valid for the final projection, and the usual rule about the alias only being valid in the order by clause - this seems to be falling down somewhere in between.

How can you order by the result of union of two queries in Oracle?

Just add a dummy column to sort

 SELECT <yourfields>
 FROM (
      SELECT 1 as dummy, <yourfields>
      FROM Query1
      UNION
      SELECT 2 as dummy, <yourfields>
      FROM Query2
     ) T
ORDER BY dummy

group by and union in oracle

The error you've run into

In Oracle, it's best to always name each column in each UNION subquery the same way. In your case, the following should work:

select count(*) as theCount,
       COMP_IDENTIFIER 
from CORDYS_NCB_LOG 
where AUDIT_CONTEXT='FAULT' 
group by COMP_IDENTIFIER -- don't forget this
union 
select count(*) as theCount,
       COMP_IDENTIFIER 
from CORDYS_NCB_LOG 
where AUDIT_CONTEXT='RESPONSE' 
group by COMP_IDENTIFIER  
order by theCount; 

See also:

Curious issue with Oracle UNION and ORDER BY

A good workaround is, of course, to use indexed column references as suggested by a_horse_with_no_name

The query you really wanted

From your comments, however, I suspect you wanted to write an entirely different query, namely:

select count(case AUDIT_CONTEXT when 'FAULT'    then 1 end) as faultCount,
       count(case AUDIT_CONTEXT when 'RESPONSE' then 1 end) as responseCount,
       COMP_IDENTIFIER 
from CORDYS_NCB_LOG 
where AUDIT_CONTEXT in ('FAULT', 'RESPONSE')
group by COMP_IDENTIFIER  
order by responseCount; 

interesting behaviour of order by with union all clause

The statement

select * from (select * from dual order by 1)

has no defined order at all. Only the outermost ORDER BY takes effect in SQL (except if there is a row limit set).

If you still happen to observe order in the query results this is a coincidence that can go away at any time.

In the statement

select * from dual
union all
select * from dual order by 1

The order by is attached to the union all, not the the 2nd select. It is therefore top-level and well-defined.

Use the last form. And put the order by into a new line to make this easier to read.

---

How can I then sort just single select with union all?

The output order of union all is undefined without order-by clause. Certainly the two inputs are not guaranteed to be concatenated.

select *, 1 as Tag from dual
union all
select *, 2 as Tag from dual
order by Tag, 1 --simulate ordered concatenation of inputs

How to optimize an Oracle UNION ALL of two 3-sec queries that takes over 200 secs ... even with rownum

As I don't know the underlying tables, their structure and their data, I can only guess. Having said that, I'd try the following things (not knowing if any of the steps are helpful or not!):

1. remove the rownum from receiving_all

2. add the where clause from the main query to each of the subqueries in receiving_all. The optimizer might be able to infer this, but I am not sure.

3. change union all to union in receiving_all and remove the distinct from receiving_1 and receiving_2

4. receiving_2 seems to be included in receiving_2?

5. If 4. is not the case, I would combine both into a single query with WHERE UPPER(bq.JE_CATEGORY) = UPPER('Receiving') AND ( (<conditions for 1>) OR (<conditions for 2>) )

6. I would try if UPPER(bq.JE_CATEGORY) can be avoided. Functions prevent index access.

7. In case you can create tables, I'd unroll the values of gccs into two tables, one for segment_1, and another table for segment_2.

8. If 7. is not an option, I'd add the hint select /*+ materialize */ gcc.code_combination_id to gcc.

I'm very curious to see which suggestion has any positive impact...

What is the difference between UNION and UNION ALL?

UNION removes duplicate records (where all columns in the results are the same), UNION ALL does not.

There is a performance hit when using UNION instead of UNION ALL, since the database server must do additional work to remove the duplicate rows, but usually you do not want the duplicates (especially when developing reports).

To identify duplicates, records must be comparable types as well as compatible types. This will depend on the SQL system. For example the system may truncate all long text fields to make short text fields for comparison (MS Jet), or may refuse to compare binary fields (ORACLE)

UNION Example:

SELECT 'foo' AS bar UNION SELECT 'foo' AS bar

Result:

+-----+
| bar |
+-----+
| foo |
+-----+
1 row in set (0.00 sec)

UNION ALL example:

SELECT 'foo' AS bar UNION ALL SELECT 'foo' AS bar

Result:

+-----+
| bar |
+-----+
| foo |
| foo |
+-----+
2 rows in set (0.00 sec)

Segregate results based on order in Oracle SQL

Since this is a Gaps & Island problem where "beaches" are defined as department changes. You can detect them using LAG() analytical function. For example, you can do:

with data as (
        SELECT 'Bob' name, 1 years_at_company, 'Sales' role FROM DUAL
        UNION
        SELECT 'Bob', 2, 'Sales' FROM DUAL
        UNION
        SELECT 'Bob', 3, 'Sales' FROM DUAL
        UNION 
        SELECT 'Bob', 4, 'IT' FROM DUAL
        UNION
        SELECT 'Bob', 5, 'Sales' FROM DUAL
        UNION
        SELECT 'Bob', 6, 'Marketing' FROM DUAL
)
select *
from (
  select d.*,
    case when role = lag(role) over(partition by name order by years_at_company)
         then 0 else 1 end as beach
  from data d
) x
order by name, years_at_company

Result:

 NAME  YEARS_AT_COMPANY  ROLE       BEACH 
 ----- ----------------- ---------- ----- 
 Bob   1                 Sales      1     
 Bob   2                 Sales      0     
 Bob   3                 Sales      0     
 Bob   4                 IT         1     
 Bob   5                 Sales      1     
 Bob   6                 Marketing  1     

See running example at db<>fiddle.

The query above preprocessed the data to produce the beach column. With it you can clearly distinguish which rows actually represent new jobs and which ones don't.

You can continue processing the data to aggregate it or to filter it according to your needs, but this should give you all the info you need.

Oracle SQL: If I use a function both as a field and in order by, is it evaluated again?

A good way to check what is going on in such cases is to use sequences (but I have oracle version 12.1). For example:

SQL> create sequence func_seq;

Sequence created.

SQL> create or replace function foo return number is
begin
  return func_seq.nextval;
end;
/

Function created.

First, make a query that return two rows (without ORDER BY clause) and check value of sequence:

SQL> select foo from dual connect by level <= 2;

       FOO
----------
         1
         2

SQL> select func_seq.currval from dual;

   CURRVAL
----------
         2

Then a query with ORDER BY:

SQL> select foo from dual connect by level <= 2 order by foo;

       FOO
----------
         3
         4

SQL> select func_seq.currval from dual;

   CURRVAL
----------
         4

In both cases function was executed 2 times.

But if your function takes arguments, you have to pay attention to their values:

SQL> create or replace function foo(p number) return number is
begin
  return func_seq.nextval;
end;
/  

Function created.

Make query with different arguments:

SQL> select foo(1) from dual connect by level <= 2 order by foo(2);

    FOO(1)
----------
         6
         8

SQL> select func_seq.currval from dual;

   CURRVAL
----------
         8

As we can see, function was executed 4 times.

Oracle 12c - Ambiguous column in Insert Into Select Query, ORA-00918

In this case you don't need the subquery - as @Littlefoot showed. But if you did, with a more complicated scenario, you can avoid the error by aliasing the column expressions in the subquery:

INSERT INTO sample (
    HOST,
    TOTAL_PING,
    TOTAL_UNAVAILABLE_PING
)

SELECT * FROM (

    SELECT 'FR3158-73-1' as host,
    82 as total_ping,
    82 as total_unavailable_ping 
    FROM DUAL
    UNION ALL

    SELECT 'FR3158-76-2',
    80,
    10
    FROM DUAL
)
/

2 rows inserted.

---

The problem is that the subquery on its own gets implied column aliases, derived from the values in the first branch of the query:

SELECT 'FR3158-73-1',
82,
82 
FROM DUAL
UNION ALL

SELECT 'FR3158-76-2',
80,
10
FROM DUAL

'FR3158-73-         82         82
----------- ---------- ----------
FR3158-73-1         82         82
FR3158-76-2         80         10

The second and third columns are both called "82", which is the ambiguity the ORA-00918 is complaining about, from the outer select. If you add aliases that goes away:

SELECT 'FR3158-73-1' as host,
82 as total_ping,
82 as total_unavailable_ping 
FROM DUAL
UNION ALL

SELECT 'FR3158-76-2',
80,
10
FROM DUAL

HOST        TOTAL_PING TOTAL_UNAVAILABLE_PING
----------- ---------- ----------------------
FR3158-73-1         82                     82
FR3158-76-2         80                     10

so the outer query is no longer confused. Note that you only need the aliases in the first branch of the union (usually, anyway) - it doesn't hurt to have them in all branches, they'll just be ignored, but it saves a bit of typing if you're creating this manually. The actual alias names also don't matter in this case, they just have to be unique; specifically, they don't have to match the columns you're inserting into - but it makes it easier to follow if they do.

If you do it as @Littlefoot showed you don't have the intermediate result set select, so the derived names don't need to be evaluated (if they can be said to exist at all), so the ambiguity is not seen - it's purely positional.

---

Related Topics