0

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 

1 Answer 1

1

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
Sign up to request clarification or add additional context in comments.

5 Comments

I like this approach. but I'm getting the same error as my previous attempts. "SQL command not properly ended." and the red swiggle is under "as" in this statement "from cte1 as tos"
@GET_Amazed - check now updated. Am not good with oracle syntax
VR46 can you look at another query for me?
@GET_Amazed - Sure ask a new question you will get help
I just did. about looping. please help

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.