-1

I have one SQL query which i mentioned below, but i have used UNION ALL which is taking more time to execute. But i want some different approach to get the same details which will be more efficient. Please help me on this. 

select creation_time, collected, errored
from batch_summary
where creation_time < (SYSDATE -1/24) and source_type in ('A','B','c')
group by creation_time, source_type

union all

select creation_time, collected, errored
from batch_summary
where creation_time < (SYSDATE -1/24) and source_type in ('d') and batch_id like '%PGW%'
group by creation_time, source_type

union all

select creation_time, collected, errored
from batch_summary
where creation_time < (SYSDATE -1/24) and source_type in ('E','F')
group by creation_time, source_type
user2642751
  • 43
  • 1
  • 9
  • 1
    This is invalid SQL, you have columns in the select list that are neither argument to an aggregate function nor listed in the GROUP BY clause. – jarlh Mar 13 '15 at 12:23
  • What's with the group by? You have no aggregated values. Your query doesn't make any sense. – Bohemian Mar 13 '15 at 12:25
  • this is not the actual query.. actual query is huge.. but it is having source_type in select lists... – user2642751 Mar 13 '15 at 12:29
  • select creation_time, collected, errored, source_type from batch_summary where creation_time < (SYSDATE -1/24) and source_type in ('A','B','c') group by creation_time, source_type union all select creation_time, collected, errored, source_type from batch_summary where creation_time < (SYSDATE -1/24) and source_type in ('d') and batch_id like '%PGW%' group by creation_time, source_type union all select creation_time, collected, errored, source_type from batch_summary where creation_time < (SYSDATE -1/24) and source_type in ('E','F') group by creation_time, source_type – user2642751 Mar 13 '15 at 12:30
  • @user2642751 your"big" queryhas the same problem. Remove all the group by clauses or replace collected and errored with aggregations eg sum(collected) etc. as it stands, your query makes no sense. – Bohemian Mar 13 '15 at 17:32

1 Answers1

0

Put the conditions in an OR

select creation_time, collected, errored
from batch_summary
where 

(creation_time < (SYSDATE -1/24) and source_type in ('A','B','c'))
OR
(creation_time < (SYSDATE -1/24) and source_type in ('d') and batch_id like '%PGW%')
OR
(creation_time < (SYSDATE -1/24) and source_type in ('E','F'))
group by creation_time, source_type

By the way, UNION ALL is one the fastest way of getting results in any set based language.

SouravA
  • 5,147
  • 2
  • 24
  • 49