CODEWITHSUNDEEP
sqloracle-database
user5692174
user5692174

Reputation:

joining multiple subqueries Oracle

I have 2 working sets of subqueries. I'm trying to make them into one query with these fields:

select tos.source_system_order_id , tos.TOS_Date,  tos.TOS_Final_Charge_Amt_Sum , oes.OES_Final_Charge_Amt_Sum

first query:

SELECT tos1.source_system_order_id, 
       tos1.tos_date, 
       SUM(tos1.tos_final_charge_amt_sum) 
FROM   (SELECT source_system_order_id, 
               source_system_cd, 
               To_char(billing_month_dt, 'YYYYMM') AS TOS_Date, 
               tos_final_charge_amt_sum 
        FROM   tl_ov_stage 
        ORDER  BY source_system_order_id) TOS1 
GROUP  BY tos1.source_system_order_id, 
          tos1.tos_date

2ndquery

SELECT OES1.source_system_order_id, 
       oes1.oes_date, 
       SUM(oes1.oes_final_charge_amt_sum) AS OES_Final_Charge_Amt_Sum 
FROM   (SELECT To_char("date", 'YYYYMM')   AS OES_Date, 
               To_char("service order id") AS SOURCE_SYSTEM_ORDER_ID, 
               oes_final_charge_amt_sum 
        FROM   v_ord_valuation_detail@prodr_link) OES1 
GROUP  BY OES1.source_system_order_id, 
          oes1.oes_date, 
          oes1.order_status

Upvotes: 0

Views: 1328

Answers (1)

Pரதீப்
Pரதீப்

Reputation: 93764

Try using CTE to combine the two select queries. I find CTE is more readable in such cases

with tos 
as
(
SELECT tos1.source_system_order_id, 
       tos1.tos_date, 
       SUM(tos1.tos_final_charge_amt_sum) 
FROM   (SELECT source_system_order_id, 
               source_system_cd, 
               To_char(billing_month_dt, 'YYYYMM') AS TOS_Date, 
               tos_final_charge_amt_sum 
        FROM   tl_ov_stage 
        ORDER  BY source_system_order_id) TOS1 
GROUP  BY tos1.source_system_order_id, 
          tos1.tos_date 
), 
OES as
(
SELECT OES1.source_system_order_id, 
       oes1.oes_date, 
       SUM(oes1.oes_final_charge_amt_sum) AS OES_Final_Charge_Amt_Sum 
FROM   (SELECT To_char("date", 'YYYYMM')   AS OES_Date, 
               To_char("service order id") AS SOURCE_SYSTEM_ORDER_ID, 
               oes_final_charge_amt_sum 
        FROM   v_ord_valuation_detail@prodr_link) OES1 
GROUP  BY OES1.source_system_order_id, 
          oes1.oes_date, 
          oes1.order_status 
)
select tos.source_system_order_id, 
       tos.TOS_Date,  
       tos.TOS_Final_Charge_Amt_Sum, 
       oes.OES_Final_Charge_Amt_Sum
from tos
inner join oes
        on tos.source_system_order_id = oes.source_system_order_id
       AND tos.tos_date = oes.oes_date -- Remove if this is not needed

Upvotes: 1

Related Questions