SQL Query: How to Join Two SQL Queries in Oracle Report

Question

I have been tasked with converting an old reports program to Oracle reports and I came to a halt when I needed to join two queries to make the report work. I'm not new to SQL, but I do need help on this one.

For Oracle Reports 11g, reports needs to show the results of the following two queries, therefore, these queries need to be joined together in one single SQL query for the report to work.

First query:

select table_name
     , to_char(load_date, 'MM/DD/ YYYY') as XDATE
     , to_char(number_name) as NUMBER NAME
     , round(sysdate-load_date) as DAYS
     , 'E' AS TABLEIND
from error_table
where load_date is not null
  and round(sysdate-load_date) > 15
  and number_name not in
    (select number_name
     from table_comments)
order by table_name

Second query:

select table_name
     , to_char(load_date, 'MM/DD/ YYYY') as XDATE
     , to_char(number_name) as NUMBER NAME
     , round(sysdate-load_date) as DAYS
     , 'O' AS TABLEIND
from other_table
where load_date is not null 
  and round(sysdate-load_date) > 15 
  and number_name not in
   (select number_name
    from table_comments) 
order by table_name

---

The results of these two queries should show the results of these two queries with the first query first, and the second query second. Any help with this problem is highly appreciated.

Answer 1

( Query1
--leave out the "order by" line
)
UNION ALL
( Query2
--leave out the "order by" line, too 
)
ORDER BY TABLEIND
       , table_name

Answer 2

If you're trying to get these to come out in one result set, try a UNION between them. You can order the whole result set by TABLEIND, table_name to sort the way you want, I believe.

Answer 3

You can create a union query with the existing queries as inline views:

    select 1 as whichQuery, q1.col, q1.col, ...
    from
    (select....) as q1
    union all
    select 2 as whichQuery, q2.col, q2.col, ...
    from
    (select ....) as q2

and then you can order by whichQuery. That guarantees the order you want in case TABLEIND alpha sort value should vary (and not sort in the order you want).

Answer 4

If you HAVE to have it in this format dump the results of the first query into a temp table with an identity column then dump the results of the second query into the same table.

Then select from that temp table sorted off that identity column