Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2014
    Posts
    4

    Unanswered: Records Duplication in Details Section

    Hi all,

    I am trying to merge 2reports into 1 rpt file. I have 11 tables linked using left outer & right joins. I am sort of getting the result, however have duplicate records to it.
    I am also using history tables in this report.

    I have tried grouping and other fopmulas to suppress the details section. However that is not what i am looking at.. I am quite new to CR.. I believe there is some issues with the joins and I am not quite sure how to go about it.

    Any help is appreciated..
    Thnx..

    Ash

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Can you post the SQL?
    George
    Home | Blog

  3. #3
    Join Date
    Jan 2014
    Posts
    4
    Hi

    Thnx for ur reply.. Its a pretty long query..
    I have attached the doc wid query for you..


    Thnx
    Attached Files Attached Files

  4. #4
    Join Date
    Jan 2014
    Posts
    4
    Hi,

    Thnx for ur reply..
    I have attached a file with the query.. Now they have asked me to merge those 2reports into another huge report..

    However i have attached a file were I have merged 2 reports into one.. This report is giving me records however duplicated..

    Any help is appreciated
    Thnx
    Attached Files Attached Files

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Code extracted from the PDF files
    Code:
    SELECT "BORROWER_TRANSACTION"."transaction_date", "BORROWER_TRANSACTION"."transaction_code_id", "BORROWER_TRANSACTION"."loan_amt", "BORROWER"."giv_name", "BORROWER"."surname", "HISTORY_BORROWER_SECURITY_ADDRESS_BLDGS_INS_PLCY"."active_from", "HISTORY_BORROWER_SECURITY_ADDRESS_BLDGS_INS_PLCY"."active_to", "HISTORY_BORROWER_SECURITY_ADDRESS_CONTS_INS_PLCY"."active_from", "HISTORY_BORROWER_SECURITY_ADDRESS_CONTS_INS_PLCY"."active_to", "HISTORY_BORROWER_SECURITY_ADDRESS_BLDGS_INS_PLCY"."bldgs_ins_plcy", "HISTORY_BORROWER_SECURITY_ADDRESS_CONTS_INS_PLCY"."conts_ins_plcy", "HISTORY_BORROWER_SECURITY_ADDRESS_BLDGS_INS_PLCY"."sec_no", "HISTORY_BORROWER_SECURITY_ADDRESS_CONTS_INS_PLCY"."sec_no", "BORROWER_TRANSACTION"."soc_no", "SOCIETY"."soc_type_id", "FINANCIEROVERRIDE"."fncr_name", "FINANCIER"."fncr_name", "BORROWER"."sub_fncr_id", "SOCIETY"."soc_fncr_1", "BORROWER_TRANSACTION"."mem_no", "BORROWER_TRANSACTION"."ID", "LU_TRANSACTION_CODE"."short_description", "BORROWER_DIRECT_DEBIT"."strt_date", "HISTORY_LOAN_STATUS"."active_from", "HISTORY_LOAN_STATUS"."active_to", "BORROWER"."shrs_curr", "BORROWER_DIRECT_DEBIT"."mem_no", "BORROWER_DIRECT_DEBIT"."amt", "BORROWER_DIRECT_DEBIT"."freq", "BORROWER_DIRECT_DEBIT"."direct_debit_counter"
    FROM (((((((("Reporter"."dbo"."BORROWER_TRANSACTION" "BORROWER_TRANSACTION" LEFT OUTER JOIN "Reporter"."dbo"."BORROWER" "BORROWER" ON ("BORROWER_TRANSACTION"."soc_no"="BORROWER"."soc_no") AND ("BORROWER_TRANSACTION"."mem_no"="BORROWER"."mem_no")) LEFT OUTER JOIN "Reporter"."dbo"."LU_TRANSACTION_CODE" "LU_TRANSACTION_CODE" ON "BORROWER_TRANSACTION"."transaction_code_id"="LU_TRANSACTION_CODE"."id") LEFT OUTER JOIN "Reporter"."dbo"."SOCIETY" "SOCIETY" ON "BORROWER_TRANSACTION"."soc_no"="SOCIETY"."soc_no") LEFT OUTER JOIN "Reporter"."dbo"."HISTORY_BORROWER_SECURITY_ADDRESS_BLDGS_INS_PLCY" "HISTORY_BORROWER_SECURITY_ADDRESS_BLDGS_INS_PLCY" ON ("BORROWER_TRANSACTION"."soc_no"="HISTORY_BORROWER_SECURITY_ADDRESS_BLDGS_INS_PLCY"."soc_no") AND ("BORROWER_TRANSACTION"."mem_no"="HISTORY_BORROWER_SECURITY_ADDRESS_BLDGS_INS_PLCY"."mem_no")) LEFT OUTER JOIN "Reporter"."dbo"."HISTORY_BORROWER_SECURITY_ADDRESS_CONTS_INS_PLCY" "HISTORY_BORROWER_SECURITY_ADDRESS_CONTS_INS_PLCY" ON ("BORROWER_TRANSACTION"."soc_no"="HISTORY_BORROWER_SECURITY_ADDRESS_CONTS_INS_PLCY"."soc_no") AND ("BORROWER_TRANSACTION"."mem_no"="HISTORY_BORROWER_SECURITY_ADDRESS_CONTS_INS_PLCY"."mem_no")) LEFT OUTER JOIN "Reporter"."dbo"."HISTORY_LOAN_STATUS" "HISTORY_LOAN_STATUS" ON ("BORROWER_TRANSACTION"."soc_no"="HISTORY_LOAN_STATUS"."soc_no") AND ("BORROWER_TRANSACTION"."mem_no"="HISTORY_LOAN_STATUS"."mem_no")) LEFT OUTER JOIN "Reporter"."dbo"."FINANCIER" "FINANCIER" ON "SOCIETY"."soc_fncr_1"="FINANCIER"."fncr_id") LEFT OUTER JOIN "Reporter"."dbo"."FINANCIER" "FINANCIEROVERRIDE" ON
    "BORROWER"."sub_fncr_id"="FINANCIEROVERRIDE"."fncr_id") RIGHT OUTER JOIN "Reporter"."dbo"."BORROWER_DIRECT_DEBIT" "BORROWER_DIRECT_DEBIT" ON ("BORROWER"."soc_no"="BORROWER_DIRECT_DEBIT"."soc_no") AND ("BORROWER"."mem_no"="BORROWER_DIRECT_DEBIT"."mem_no")
    WHERE ("BORROWER_TRANSACTION"."transaction_date">={ts '2014-02-01 00:00:00'} AND "BORROWER_TRANSACTION"."transaction_date"<{ts '2014-02-04 00:00:00'}) AND (("HISTORY_LOAN_STATUS"."active_from" IS NULL ) OR ("HISTORY_LOAN_STATUS"."active_from"<{ts '2014-02-04 00:00:00'})) AND (("HISTORY_LOAN_STATUS"."active_to" IS NULL ) OR ("HISTORY_LOAN_STATUS"."active_to">={ts '2014-02-03 00:00:00'})) AND (("HISTORY_BORROWER_SECURITY_ADDRESS_BLDGS_INS_PLCY"."sec_no" IS NULL ) OR ("HISTORY_BORROWER_SECURITY_ADDRESS_BLDGS_INS_PLCY"."sec_no"=0)) AND (("HISTORY_BORROWER_SECURITY_ADDRESS_CONTS_INS_PLCY"."sec_no" IS NULL ) OR ("HISTORY_BORROWER_SECURITY_ADDRESS_CONTS_INS_PLCY"."sec_no"=0)) AND (("HISTORY_BORROWER_SECURITY_ADDRESS_BLDGS_INS_PLCY"."active_from" IS NULL ) OR ("HISTORY_BORROWER_SECURITY_ADDRESS_BLDGS_INS_PLCY"."active_from"<{ts '2014-02-04 00:00:00'})) AND (("HISTORY_BORROWER_SECURITY_ADDRESS_BLDGS_INS_PLCY"."active_to" IS NULL ) OR ("HISTORY_BORROWER_SECURITY_ADDRESS_BLDGS_INS_PLCY"."active_to">={ts '2014-02-03 00:00:00'})) AND (("HISTORY_BORROWER_SECURITY_ADDRESS_CONTS_INS_PLCY"."active_from" IS NULL ) OR ("HISTORY_BORROWER_SECURITY_ADDRESS_CONTS_INS_PLCY"."active_from"<{ts '2014-02-04 00:00:00'})) AND (("HISTORY_BORROWER_SECURITY_ADDRESS_CONTS_INS_PLCY"."active_to" IS NULL ) OR ("HISTORY_BORROWER_SECURITY_ADDRESS_CONTS_INS_PLCY"."active_to">={ts '2014-02-03 00:00:00'})) AND ("BORROWER_TRANSACTION"."transaction_code_id"=52 OR "BORROWER_TRANSACTION"."transaction_code_id"=77)
    Code:
    SELECT "BORROWER_TRANSACTION"."transaction_date", "BORROWER_TRANSACTION"."transaction_code_id", "BORROWER_TRANSACTION"."loan_amt", "BORROWER"."giv_name", "BORROWER"."surname", "HISTORY_BORROWER_SECURITY_ADDRESS_BLDGS_INS_PLCY"."active_from", "HISTORY_BORROWER_SECURITY_ADDRESS_BLDGS_INS_PLCY"."active_to", "HISTORY_BORROWER_SECURITY_ADDRESS_CONTS_INS_PLCY"."active_from", "HISTORY_BORROWER_SECURITY_ADDRESS_CONTS_INS_PLCY"."active_to", "HISTORY_BORROWER_SECURITY_ADDRESS_BLDGS_INS_PLCY"."bldgs_ins_plcy", "HISTORY_BORROWER_SECURITY_ADDRESS_CONTS_INS_PLCY"."conts_ins_plcy", "HISTORY_BORROWER_SECURITY_ADDRESS_BLDGS_INS_PLCY"."sec_no", "HISTORY_BORROWER_SECURITY_ADDRESS_CONTS_INS_PLCY"."sec_no", "BORROWER_TRANSACTION"."soc_no", "SOCIETY"."soc_type_id", "FINANCIEROVERRIDE"."fncr_name", "FINANCIER"."fncr_name", "BORROWER"."sub_fncr_id", "SOCIETY"."soc_fncr_1", "BORROWER_TRANSACTION"."mem_no", "BORROWER_TRANSACTION"."ID", "LU_TRANSACTION_CODE"."short_description", "BORROWER_DIRECT_DEBIT"."strt_date", "HISTORY_LOAN_STATUS"."active_from", "HISTORY_LOAN_STATUS"."active_to", "BORROWER"."shrs_curr", "BORROWER_DIRECT_DEBIT"."mem_no", "BORROWER_DIRECT_DEBIT"."amt", "BORROWER_DIRECT_DEBIT"."freq"
    FROM (((((((("Reporter"."dbo"."BORROWER_TRANSACTION" "BORROWER_TRANSACTION" LEFT OUTER JOIN "Reporter"."dbo"."BORROWER" "BORROWER" ON ("BORROWER_TRANSACTION"."soc_no"="BORROWER"."soc_no") AND ("BORROWER_TRANSACTION"."mem_no"="BORROWER"."mem_no")) LEFT OUTER JOIN "Reporter"."dbo"."LU_TRANSACTION_CODE" "LU_TRANSACTION_CODE" ON "BORROWER_TRANSACTION"."transaction_code_id"="LU_TRANSACTION_CODE"."id") LEFT OUTER JOIN "Reporter"."dbo"."SOCIETY" "SOCIETY" ON "BORROWER_TRANSACTION"."soc_no"="SOCIETY"."soc_no") LEFT OUTER JOIN "Reporter"."dbo"."HISTORY_BORROWER_SECURITY_ADDRESS_BLDGS_INS_PLCY" "HISTORY_BORROWER_SECURITY_ADDRESS_BLDGS_INS_PLCY" ON ("BORROWER_TRANSACTION"."soc_no"="HISTORY_BORROWER_SECURITY_ADDRESS_BLDGS_INS_PLCY"."soc_no") AND ("BORROWER_TRANSACTION"."mem_no"="HISTORY_BORROWER_SECURITY_ADDRESS_BLDGS_INS_PLCY"."mem_no")) LEFT OUTER JOIN "Reporter"."dbo"."HISTORY_BORROWER_SECURITY_ADDRESS_CONTS_INS_PLCY" "HISTORY_BORROWER_SECURITY_ADDRESS_CONTS_INS_PLCY" ON ("BORROWER_TRANSACTION"."soc_no"="HISTORY_BORROWER_SECURITY_ADDRESS_CONTS_INS_PLCY"."soc_no") AND ("BORROWER_TRANSACTION"."mem_no"="HISTORY_BORROWER_SECURITY_ADDRESS_CONTS_INS_PLCY"."mem_no")) LEFT OUTER JOIN "Reporter"."dbo"."HISTORY_LOAN_STATUS" "HISTORY_LOAN_STATUS" ON ("BORROWER_TRANSACTION"."soc_no"="HISTORY_LOAN_STATUS"."soc_no") AND ("BORROWER_TRANSACTION"."mem_no"="HISTORY_LOAN_STATUS"."mem_no")) LEFT OUTER JOIN "Reporter"."dbo"."FINANCIER" "FINANCIER" ON "SOCIETY"."soc_fncr_1"="FINANCIER"."fncr_id") LEFT OUTER JOIN "Reporter"."dbo"."FINANCIER" "FINANCIEROVERRIDE" ON
    "BORROWER"."sub_fncr_id"="FINANCIEROVERRIDE"."fncr_id") RIGHT OUTER JOIN
    "Reporter"."dbo"."BORROWER_DIRECT_DEBIT" "BORROWER_DIRECT_DEBIT" ON
    ("BORROWER"."soc_no"="BORROWER_DIRECT_DEBIT"."soc_no") AND
    ("BORROWER"."mem_no"="BORROWER_DIRECT_DEBIT"."mem_no")
    WHERE ("BORROWER_TRANSACTION"."transaction_date">={ts '2014-01-01 00:00:00'} AND
    "BORROWER_TRANSACTION"."transaction_date"<{ts '2014-02-01 00:00:00'}) AND
    (("HISTORY_LOAN_STATUS"."active_from" IS NULL ) OR
    ("HISTORY_LOAN_STATUS"."active_from"<{ts '2014-02-01 00:00:00'})) AND
    (("HISTORY_LOAN_STATUS"."active_to" IS NULL ) OR ("HISTORY_LOAN_STATUS"."active_to">={ts
    '2014-01-31 00:00:00'})) AND
    (("HISTORY_BORROWER_SECURITY_ADDRESS_BLDGS_INS_PLCY"."sec_no" IS NULL ) OR
    ("HISTORY_BORROWER_SECURITY_ADDRESS_BLDGS_INS_PLCY"."sec_no"=0)) AND
    (("HISTORY_BORROWER_SECURITY_ADDRESS_CONTS_INS_PLCY"."sec_no" IS NULL ) OR
    ("HISTORY_BORROWER_SECURITY_ADDRESS_CONTS_INS_PLCY"."sec_no"=0)) AND
    (("HISTORY_BORROWER_SECURITY_ADDRESS_BLDGS_INS_PLCY"."active_from" IS NULL ) OR
    ("HISTORY_BORROWER_SECURITY_ADDRESS_BLDGS_INS_PLCY"."active_from"<{ts '2014-02-01
    00:00:00'})) AND (("HISTORY_BORROWER_SECURITY_ADDRESS_BLDGS_INS_PLCY"."active_to" IS
    NULL ) OR ("HISTORY_BORROWER_SECURITY_ADDRESS_BLDGS_INS_PLCY"."active_to">={ts '2014-
    01-31 00:00:00'})) AND
    (("HISTORY_BORROWER_SECURITY_ADDRESS_CONTS_INS_PLCY"."active_from" IS NULL ) OR
    ("HISTORY_BORROWER_SECURITY_ADDRESS_CONTS_INS_PLCY"."active_from"<{ts '2014-02-01
    00:00:00'})) AND (("HISTORY_BORROWER_SECURITY_ADDRESS_CONTS_INS_PLCY"."active_to" IS
    NULL ) OR ("HISTORY_BORROWER_SECURITY_ADDRESS_CONTS_INS_PLCY"."active_to">={ts '2014-
    01-31 00:00:00'})) AND "SOCIETY"."soc_type_id" IS NOT NULL AND "SOCIETY"."soc_type_id"=7
    AND "SOCIETY"."soc_type_id" IS NOT NULL AND "BORROWER"."shrs_curr">0 AND
    "BORROWER_DIRECT_DEBIT"."mem_no" IS NOT NULL
    Last edited by gvee; 02-04-14 at 06:04.
    George
    Home | Blog

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •