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.

It doesn't work because the two queries select totally different things. I'm not sure how you expect the query results to become available in 1 query. — Rene, Dec 17 '13 at 14:09
Yes both the query return different result but same num of rows. Can it be possible to display these two different results in one go. — Himshu1710, Dec 17 '13 at 14:18
I have one table with entries for different sales,cost and spend files. and one another table having entries for summary file of cost,sales and spend files. Suppose total 8 sales files are there in first table then the sales summary file in second table will have a character as 8 in its name which i am extracting by using SUBSTR function. Now i want a query which will count the sales file in first table as well as extract that first character of sales summary file in second table and display as a single result — Himshu1710, Dec 17 '13 at 14:33
Expected result: a.que_seqnum message_type Count b.queue_seqnum files 3081 ERP_SALES 8 3089 8 3082 ERP_COSTS 10 3090 10 3083 ERP_SPEND 7 3091 7 — Himshu1710, Dec 17 '13 at 14:34

Olivier Jacot-Descombes · Accepted Answer · 2017-12-02T16:33:12.197

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.

great... It worked and i can see now the way i was trying to show. Thanks friend. — Himshu1710, Dec 17 '13 at 15:30

Haji · Answer 2 · 2013-12-17T14:15:32.447

6

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..

edited Dec 17 '13 at 14:15

answered Dec 17 '13 at 14:10

Haji

1,999
1
15
21

Union all won't work in this scenario as both the query has not similar columns which they are fetching. Thanks. – Himshu1710 Dec 17 '13 at 15:31
Yes Haji. I have tried your query but it threw following error: ORA-01790: expression must have same datatype as corresponding expression 01790. 00000 - "expression must have same datatype as corresponding expression" – Himshu1710 Dec 18 '13 at 09:50
@Himshu1710 this error comes mismatching of data types in each union all. here i passed queue_seqnum,queue_seqnum columns as text. if it is number number fields then pass as 0 in second query..? may i know the data type of queue_seqnum,queue_seqnum? – Haji Dec 18 '13 at 16:03
I used that also but it is throwing same error. The data type of queue_seqnum is number and i passed 0 in the second query. – Himshu1710 Dec 19 '13 at 16:24

output of two different queries as one result in oracle SQL

2 Answers2

Linked