Can I combine these two queries into a single query?

Note:AUT_GROUP_ID column in Table CBS_OWNER.AUT_GROUP_MAP is the only factor differentiating both queries.  
AUT_GROUP_ID IN (20,4,7) --->ONNET
AUT_GROUP_ID IN (3,6) ---> OFFNET

The first query is fetching all the usage details for service ‘ONNET’

select A.AUT_ID,
A.Service,
A.CDR_DATE,
(case when A.UNIT_TYPE_CB=1 then A.UNITS_CB/10000 when A.UNIT_TYPE_CB=2 then A.UNITS_CB/60 end )as USAGE,
(case when A.UNIT_TYPE_CB=1 then 'Currency' when A.UNIT_TYPE_CB=2 then 'Seconds' end )as UNIT_TYPE,
A.UNIT_TYPE_CB,
B.PAYMENT_MODE,
(case when B.PAYMENT_MODE=1 then 'Prepaid' else 'postpaid' end ) as PAYMENT_TYPE
from 
(select 'ONNET' as Service,
cd.AUT_ID as AUT_ID,
cd.expected_cutoff_dt as CDR_DATE,
cd.PAYMENT_MODE as POST_PRE_TYPE,
cb.UNITS as UNITS_CB,
cb.UNIT_TYPE as UNIT_TYPE_CB

FROM cbs_owner.cdr_balance cb left  join  cbs_owner.cdr_data cd
on  cb.MSG_ID = cd.MSG_ID
and   cb.MSG_ID2 = cd.MSG_ID2 where  cb.EXPECTED_CUTOFF_DT='09-MAY-20'  and cd.EXPECTED_CUTOFF_DT='09-MAY-20' and  cd.AUT_ID in 
    (SELECT DISTINCT AUT_ID
      FROM CBS_OWNER.AUT_GROUP_MAP
     WHERE AUT_GROUP_ID IN (20,4,7)
       AND RESELLER_VERSION_ID IN (SELECT MAX(RESELLER_VERSION_ID)
                                     FROM CBS_OWNER.RESELLER_VERSION
                                    WHERE STATUS = 3)))A ,CBS_OWNER.offer_ref B where A.OFFER_ID_CD=b.offer_id;

The second query is fetching all the usage details and balance details for service ‘OFFNET’

select A.AUT_ID,
A.Service,
A.CDR_DATE,
(case when A.UNIT_TYPE_CB=1 then A.UNITS_CB/10000 when A.UNIT_TYPE_CB=2 then A.UNITS_CB/60 end )as USAGE,
(case when A.UNIT_TYPE_CB=1 then 'Currency' when A.UNIT_TYPE_CB=2 then 'Seconds' end )as UNIT_TYPE,
A.UNIT_TYPE_CB,
B.PAYMENT_MODE,
(case when B.PAYMENT_MODE=1 then 'Prepaid' else 'postpaid' end ) as PAYMENT_TYPE
from 
(select 'OFFNET' as Service,
cd.AUT_ID as AUT_ID,
cd.expected_cutoff_dt as CDR_DATE,
cd.PAYMENT_MODE as POST_PRE_TYPE,
cb.UNITS as UNITS_CB,
cb.UNIT_TYPE as UNIT_TYPE_CB
    FROM cbs_owner.cdr_balance cb left  join  cbs_owner.cdr_data cd
on  cb.MSG_ID = cd.MSG_ID
and   cb.MSG_ID2 = cd.MSG_ID2 where  cb.EXPECTED_CUTOFF_DT='09-MAY-20'  and cd.EXPECTED_CUTOFF_DT='09-MAY-20' and  cd.AUT_ID in 
    (SELECT DISTINCT AUT_ID
      FROM CBS_OWNER.AUT_GROUP_MAP
     WHERE AUT_GROUP_ID IN (3,6)
       AND RESELLER_VERSION_ID IN (SELECT MAX(RESELLER_VERSION_ID)
                                     FROM CBS_OWNER.RESELLER_VERSION
                                    WHERE STATUS = 3)))A ,CBS_OWNER.offer_ref B where A.OFFER_ID_CD=b.offer_id;

How can I combine these two queries into one?

Answer

SELECT 
  <all those columns that irrelevant to the question>
FROM  
 (
  (select 'OFFNET' as Service,
    <lots of stuff>
    and rownum <300)
  ) UNION ALL
  (select 'ONNET' as Service,
    <lots of stuff>
    and rownum <300)
 )

You might be able to simplify but this will work. Next time you post a question, try to abstract your problem from the statement, maybe even reproduce it on a sample dataset. It will make you better understand the issue and maybe even solve it before you post it AND it is much easier for people to help you.

Advertisement

Answer