1 Reply Latest reply: Feb 8, 2014 9:32 PM by Shoaib RSS

How to prevent from generating duplicate record from report populating in oracle reports 6i??

Vijetha Explorer
Currently Being Moderated

Hi All,

         I have created a report which generates letter for all Cust_Code.

         In Query Builder, the code is :

SELECT DISTINCT KEC_CUST_MAST.SIR_NAME, FDACCT_CUST_MAST.NOMINEE_ADDR1, FDACCT_CUST_MAST.NOMINEE_ADDR2,

FDACCT_CUST_MAST.NOMINEE_ADDR3, FDACCT_CUST_MAST.CITY,

FDACCT_CUST_MAST.PINCODE, FDACCT_CUST_MAST.FORM_15G_NO, FDACCT_CUST_MAST.CUST_NAME, FDACCT_CUST_MAST.CUST_CODE,

FDACCT_CUST_MAST.PAN_NO

FROM FDACCT_CUST_MAST, KEC_CUST_MAST

WHERE (FDACCT_CUST_MAST.CUST_CODE = KEC_CUST_MAST.CUST_CODE)

AND KEC_CUST_MAST.CUST_CODE IN (22,54,74,121,122,153, 4141) ORDER BY CUST_CODE;


Since there are 7 Cust_Code that is (22,54,74,121,122,153, 4141) , only 7 Letters should get generated,

but 11 letter are getting generated that duplicates are generated.

This is because in Table FDACCT_CUST_MAST, there are many ACCT_FD_NOs for one CUST_CODE.

Hence duplicates are generated.

So how do i avoid duplicate CUST_CODE??

I want to generate only 7 Letters if there are 7 Cust_Codes.

So how do i do this??


Thank You,


Oracle Reports 6i.

Oracle DB 9i.






  • 1. Re: How to prevent from generating duplicate record from report populating in oracle reports 6i??
    Shoaib Explorer
    Currently Being Moderated

    Did you checked that in duplicate rows each column value is duplicated.

     

    If this is the case ,then you can try like this :

     

    SELECT DISTINCT SIR_NAME,NOMINEE_ADDR1,NOMINEE_ADDR2,NOMINEE_ADDR3,CITY,PIN_CODE,FORM_15G_NO,CUST_NAME,CUST_CODE,PAN_NO FROM (

    SELECT DISTINCT KEC_CUST_MAST.SIR_NAME, FDACCT_CUST_MAST.NOMINEE_ADDR1, FDACCT_CUST_MAST.NOMINEE_ADDR2,

    FDACCT_CUST_MAST.NOMINEE_ADDR3, FDACCT_CUST_MAST.CITY,

    FDACCT_CUST_MAST.PINCODE, FDACCT_CUST_MAST.FORM_15G_NO, FDACCT_CUST_MAST.CUST_NAME, FDACCT_CUST_MAST.CUST_CODE,

    FDACCT_CUST_MAST.PAN_NO

    FROM FDACCT_CUST_MAST, KEC_CUST_MAST

    WHERE (FDACCT_CUST_MAST.CUST_CODE = KEC_CUST_MAST.CUST_CODE)

    AND KEC_CUST_MAST.CUST_CODE IN (22,54,74,121,122,153, 4141)

    )

    ORDER BY CUST_CODE