I have written query which should fetch ADJUSTMENT_AMOUNT
and PAY_AMOUNT
from the table ABC for different month, I have used subqueries to get this done, each ADJUSTMENT_AMOUNT
and PAY_AMOUNT
contains number, which is addition of multiple rows for the month by that each month I can see how much adjustment amount is available, same with PAY_AMOUNT
.
But with the query that I have written, it is failing when ADJUSTMENT_AMOUNT
is calculated using june,july,august,september month but PAY_AMOUNT
is calculated using only september month, here in this condition PAY_AMOUNT
is duplicated. I just want to avoid the duplicate values and make it blank, so basically only one row should be available for PAY_AMOUNT
and rest 3 rows should be blank.
ADJ_MONTH ADJUSTMENT_AMOUNT CURRENCY PAY_MONTH PAY_AMOUNT
September 445 USD September 177.14
June 200 USD September 177.14
July 67 USD September 177.14
August 23 USD September 177.14
My query:
SELECT *
FROM
(SELECT TO_CHAR(CRE_DT, 'Month') AS ADJ_MONTH ,
SUM(ADJ_AMT) AS ADJUSTMENT_AMOUNT,
CURRENCY_CD
FROM ci_Adj
WHERE sa_id IN
(SELECT sa_id FROM ci_Sa WHERE acct_id=:F1
)
AND EXTRACT( YEAR FROM cre_dt) = EXTRACT(YEAR FROM sysdate)
GROUP BY TO_CHAR(CRE_DT, 'Month'),
CURRENCY_CD
ORDER BY TO_CHAR(CRE_DT, 'Month') DESC
),
(SELECT TO_CHAR(pae.cre_dttm, 'Month') AS PAY_MONTH ,
SUM(pa.PAY_AMT) AS PAY_AMOUNT
FROM ci_pay_event pae,
ci_pay pa
WHERE pa.acct_id =:F1
AND pa.pay_status_flg ='50'
AND pae.pay_event_id =pa.pay_event_id
AND EXTRACT( YEAR FROM pae.cre_dttm) = EXTRACT(YEAR FROM sysdate)
GROUP BY TO_CHAR(pae.cre_dttm, 'Month')
ORDER BY TO_CHAR(pae.cre_dttm, 'Month') DESC
)
Advertisement
Answer
Adding to points mentioned by (kfinity) and assuming the sub-queries are working fine. The below query will give all the data from the first sub-query and if it has any co-related data in the second query you get it, else the columns from query b will be null.
SELECT a.ADJ_MONTH, a.ADJUSTMENT_AMOUNT, a.CURRENCY_CD, b.PAY_MONTH, b.pay_amount
FROM
(SELECT TO_CHAR(CRE_DT, 'Month') AS ADJ_MONTH ,
SUM(ADJ_AMT) AS ADJUSTMENT_AMOUNT,
CURRENCY_CD
FROM ci_Adj
WHERE
sa_id IN (SELECT sa_id FROM ci_Sa WHERE acct_id=:F1)
AND EXTRACT( YEAR FROM cre_dt) = EXTRACT(YEAR FROM sysdate)
GROUP BY TO_CHAR(CRE_DT, 'Month'), CURRENCY_CD
ORDER BY TO_CHAR(CRE_DT, 'Month') DESC
)a,
(SELECT TO_CHAR(pae.cre_dttm, 'Month') AS PAY_MONTH ,
SUM(pa.PAY_AMT) AS PAY_AMOUNT
FROM ci_pay_event pae,
ci_pay pa
WHERE pa.acct_id =:F1
AND pa.pay_status_flg ='50'
AND pae.pay_event_id =pa.pay_event_id
AND EXTRACT( YEAR FROM pae.cre_dttm) = EXTRACT(YEAR FROM sysdate)
GROUP BY TO_CHAR(pae.cre_dttm, 'Month')
ORDER BY TO_CHAR(pae.cre_dttm, 'Month') DESC
)b
where a.ADJ_MONTH=b.pay_amount (+);