Skip to content
Advertisement

Avoid duplicate in query and make all the duplicated row blank

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 (+);
User contributions licensed under: CC BY-SA
9 People found this is helpful
Advertisement