CODEWITHSUNDEEP
oracle-databaseunionunion-all
Travis
Travis

Reputation: 2245

Oracle Union All How are the results output

We have a situation where we are using a 3rd party tool (jitterbit) to output a file and we need the file to have the header row. So I was thinking that we could use a union all in the Oracle view that we use to generate the data for the file.

But my question is will Oracle always read the top query of the union first?

Here would be a very basic example of what i am looking for 

  select 'user.CustomAttribute.Client'
         ,'user.Email'
         ,'user.customAttribute.alternateEmail'
  from dual 
  UNION ALL 
  Select c.client 
         ,c.email
         ,c.alt_email
  from contact

So would the top part with all the hard coded values always be returned first?

Upvotes: 0

Views: 140

Answers (2)

Michael Broughton
Michael Broughton

Reputation: 4055

Push it down into a subquery and add a column to manage your explicit ordering

SELECT client, email, alt_email
FROM (
 select 'user.CustomAttribute.Client' client 
         ,'user.Email' email
         ,'user.customAttribute.alternateEmail' alt_email
          -- here we add an aid to ordering, 1 comes first
         , 1 displayorder
  from dual 
  UNION ALL 
  Select c.client 
         ,c.email
         ,c.alt_email
          -- here we add an aid to ordering, 2 comes after our header
         , 2 displayorder
  from contact    )
order by displayorder, client;

Upvotes: 2

Ashraf Iqbal
Ashraf Iqbal

Reputation: 424

There is no guarantee or no default order for such queries in Oracle. You can, however, manage the desired order if you use a sub-query, such as:

 select * from (select 'user.CustomAttribute.Client' as client 
     ,'user.Email' as email
     ,'user.customAttribute.alternateEmail' as alt_email
from dual 
UNION ALL 
Select c.client 
     ,c.email
     ,c.alt_email
from contact)
order by client, email, alt_email;

Read this link for more details: https://community.oracle.com/thread/2341048?tstart=0

Upvotes: 0

Related Questions