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 (+);