output of two different queries as one result in oracle SQL

Question

I have two different table on which i apply select query with some filters and aggregate functions like SUM,COUNT,SUBSTR.

I want to get these two different output in a single result.example:

Query 1:

SELECT
    a.message_type,
    a.queue_seqnum,
    b.queue_seqnum,
    SUBSTR(b.char_data,1,2) files
FROM
    ad_in_messageheader a,
    ad_in_messagedetail b 
WHERE
    a.queue_seqnum = b.queue_seqnum AND
    a.MESSAGE_TYPE IN ('ERP_COSTS_SMRY','ERP_SALES_SMRY','ERP_SPEND_SMRY') AND
    a.create_time > '17-DEC-13 07.00.00 AM'
ORDER BY
    a.queue_seqnum desc;

Query 2:

SELECT
    a.message_type,
    count(a.message_type) count
FROM
    ad_in_messageheader a 
WHERE
    a.MESSAGE_TYPE in ('ERP_COSTS','ERP_SALES','ERP_SPEND') AND
    create_time > '17-DEC-13 07.00.00 AM'
GROUP BY
    a.message_type;

I have tried UNION and UNION ALL both. But those are not working. I also tried to Select * from (query 1),(query 2), But it also did not work. Kindly suggest me some solution which will be helpful in this scenario. Thanks.

Answer 1

There are two ways of putting queries together: Sideways by using joins and on top of each other with unions. When using joins the result will include columns of both queries. When using unions, the result will include rows of both queries. For unions to work, both queries must return the same number of corresponding columns.

I assume that you want to add the count calculated in the second query as column to the first query. This works like this (I'm using the new JOIN syntax):

SELECT
    q1.x, q1.y, q2.z, ...
FROM
    (SELECT ... FROM ...) q1
    LEFT JOIN
    (SELECT ... FROM ...) q2
        ON q1.column = q2.column

You can also use INNER JOIN instead of LEFT JOIN if you know that query2 yields at least one row for each row of query1 or if you are not interested in rows from query1 where corresponding rows are missing from query2.

SELECT 
    q1.message_type,
    q1.queue_seqnum,
    q1.files,
    q2.message_count
FROM (SELECT
         a.message_type,
         a.queue_seqnum,
         SUBSTR(b.char_data, 1, 2) files
      FROM
         ad_in_messageheader a,
         INNER JOIN ad_in_messagedetail b
            ON  a.queue_seqnum = b.queue_seqnum 
      WHERE
         a.message_type IN ('ERP_COSTS_SMRY', 'ERP_SALES_SMRY', 'ERP_SPEND_SMRY') AND
         a.create_time > '17-DEC-13 07.00.00 AM') q1
   LEFT JOIN
     (SELECT
         a.message_type,
         COUNT(a.message_type) message_count
      FROM
         ad_in_messageheader a 
      WHERE
         a.message_type IN ('ERP_COSTS', 'ERP_SALES', 'ERP_SPEND') AND
         create_time > '17-DEC-13 07.00.00 AM'
      GROUP BY
         a.message_type) q2
   ON q1.message_type = q2.message_type
ORDER BY
   q1.queue_seqnum DESC;

I would also do the sorting after joining the two sub queries, because the joining process could destroy any order established before.

There is also a problem with the message types: You are not selecting the same message types in the two sub queries. In ORACLE, you can use the DECODE function to translate the message types to make them match

In sub query 1:

SELECT
    DECODE(a.message_type,
           'ERP_COSTS_SMRY', 'ERP_COSTS', 
           'ERP_SALES_SMRY', 'ERP_SALES',
           'ERP_SPEND_SMRY', 'ERP_SPEND') message_type

---

If create_time is a DATE column, you must convert the date/time string to a date.

WHERE
    a.create_time > TO_DATE('17-12-2013 19:00:00', 'DD-MM-YYYY HH24:MI:SS')

(See https://stackoverflow.com/a/10178346/880990)

Also use a four-digit year if possible. This is safer. Is 31 1931 or 2031? Also, a month number will work also on systems with different locales. DEC would not be recognized on a German system. Instead DEZ would be expected.

Answer 2

If you are using union or union all to combine multiple query, then each query should have same number of columns with same data types.

Use below query:

SELECT a.message_type,a.queue_seqnum, b.queue_seqnum,SUBSTR (b.char_data,1,2) 
files,0 as count FROM ad_in_messageheader a, ad_in_messagedetail b WHERE a.queue_seqnum = 
b.queue_seqnum AND a.MESSAGE_TYPE in
('ERP_COSTS_SMRY','ERP_SALES_SMRY','ERP_SPEND_SMRY') AND
a.create_time > '17-DEC-13 07.00.00 AM' 
union all
SELECT a.message_type,'' as queue_seqnum,'' as queue_seqnum, '' as files 
count(a.message_type) count FROM ad_in_messageheader a 
where a.MESSAGE_TYPE in ('ERP_COSTS','ERP_SALES','ERP_SPEND') AND 
create_time > '17-DEC-13 07.00.00 AM' group by a.message_type

And you have to use order by finally after combine both queries..