Reputation:
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
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