Exclude not null and less than condition

Question

I need to sort out data into 2 reports for oracle report. Below is my query to sort out everything. For now I need to exclude all data that is shown in other report with following condition

generally I would like to exclude client_no that is not in value in this condition

WHERE ((CHQ_NO IS NOT NULL AND **CHQ_AMT>50000**)
or (CATEGORY='3' AND **CHQ_AMT>10000**))

1. FIRST REPORT

WHERE ((CHQ_NO IS NOT NULL AND CHQ_AMT>50000) or (CATEGORY='3' AND CHQ_AMT>10000))

For second report I used the below condition and it must exclude condition from first report.

SECOND REPORT
WHERE ((CHQ_NO IS NOT NULL AND CHQ_AMT<50000) or (CATEGORY='3' AND CHQ_AMT<10000))

Below is my coding and need to add condition to exclude report 1

SELECT CLIENT_NO,
       sum(decode(category,'3',decode(nvl(cancel_flag,'N'),'N',1,-2) ,0)) CASH,
       sum(decode(chq_no, null,0, decode(nvl(cancel_flag,'N'),'N',1,-2))) CHQ,
       0 YTD_PURCHASE,
       0 YTD_SALES,
       0  CURRENT_CRLIMIT,
       0 CR_LIMIT 
FROM BOS_M_LEDGER_REC 
WHERE ((CHQ_NO IS NOT NULL AND CHQ_AMT<50000) or (CATEGORY='3' AND CHQ_AMT<10000)) 
and CLIENT_NO>=:P_CLIENT_NO_FROM 
AND CLIENT_NO <=:P_CLIENT_NO_TO 
AND TRAN_DATE>=:P_FROM_DATE 
AND TRAN_DATE<=:P_TO_DATE 
GROUP BY CLIENT_NO

Answer 1

I have added the logic to remove those client_no in report 1 in the subquery represented by the alias B. Used that list of client_no in B in a full outer join on client_no with your original table(report 2 with the alias A). This will create NULLs in both A.client_no and B.client_no columns wherever there is a mismatch.

Then added a WHERE B.CLIENT_NO IS NULL which means you will only have client_no that are in A and not in B[FYI if B.client_no is not NULL, that means there is a match with A.client_no and you don't want that client_no in report 3].

I have not made changes at other places in the query.

SELECT A.CLIENT_NO,
    sum(decode(category,'3',decode(nvl(cancel_flag,'N'),'N',1,-2) ,0)) CASH,
    sum(decode(chq_no, null,0, decode(nvl(cancel_flag,'N'),'N',1,-2))) CHQ,
    0 YTD_PURCHASE,
    0 YTD_SALES,
    0  CURRENT_CRLIMIT,
    0 CR_LIMIT 
FROM BOS_M_LEDGER_REC A
FULL OUTER JOIN
(SELECT CLIENT_NO FROM BOS_M_LEDGER_REC WHERE ((CHQ_NO IS NOT NULL AND CHQ_AMT>=50000) or (CATEGORY='3' AND CHQ_AMT>=10000)) GROUP BY CLIENT_NO) B
ON A.CLIENT_NO = B.CLIENT_NO
WHERE B.CLIENT_NO IS NULL
AND ((CHQ_NO IS NOT NULL AND CHQ_AMT<50000) or (CATEGORY='3' AND CHQ_AMT<10000)) 
AND A.CLIENT_NO>=:P_CLIENT_NO_FROM 
AND A.CLIENT_NO <=:P_CLIENT_NO_TO 
AND TRAN_DATE>=:P_FROM_DATE 
AND TRAN_DATE<=:P_TO_DATE 
GROUP BY A.CLIENT_NO;