Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2012
    Posts
    14

    Red face Unanswered: giveing dupliaction while in mapping of two tables ??? - very urgent

    while i am mapping two tables it shows duplication of data. please correct me..

    i am going to fetch the area details for respective customer no in customer table. i am tabking custoemr no from customer_tbla nd mappe the same with party_address table. but it shows double the data, please correct me..


    select * from customer_tbl ct,party_address p where P.PARTY_ID=CT.CUSTOMER_ID

    customer_tbl scrip

    CREATE TABLE <schema>.CUSTOMER_TBL
    (
    CUSTOMER_ID NUMBER(10),
    CUSTOMER_NBR VARCHAR2(16 BYTE),
    CUSTOMER_TYPE VARCHAR2(10 BYTE),
    CATEGORY VARCHAR2(10 BYTE),
    NOTES VARCHAR2(256 BYTE),
    BILL_TO_ID NUMBER(10),
    OPENTITY_TYPE VARCHAR2(10 BYTE),
    OPENTITY_ID NUMBER(10),
    CREATED_BY NUMBER(8),
    CREATED_DATE DATE,
    LAST_UPD_BY NUMBER(8),
    LAST_UPD_DATE DATE,
    PREV_LINK NUMBER(10) DEFAULT 0,
    TIMEZONE VARCHAR2(15 BYTE),
    BILLING_MEDIA VARCHAR2(16 BYTE),
    GROUP_ID VARCHAR2(16 BYTE),
    REFERENCE_NO VARCHAR2(16 BYTE),
    REFERENCE_TYPE VARCHAR2(10 BYTE),
    STATUS CHAR(1 BYTE) DEFAULT 'N'
    )


    and party_address schema


    CREATE TABLE <schema>.PARTY_ADDRESS
    (
    ADDRESS_ID NUMBER(10),
    PARTY_ID NUMBER(10),
    ADDRESSTYPE_CODE VARCHAR2(4 BYTE),
    ADDRESSTEMPLATE_ID NUMBER(10),
    LINE1_CODE VARCHAR2(10 BYTE),
    ADDRLINE1 VARCHAR2(128 BYTE),
    LINE2_CODE VARCHAR2(10 BYTE),
    ADDRLINE2 VARCHAR2(128 BYTE),
    STREET_CODE VARCHAR2(10 BYTE),
    STREET VARCHAR2(255 BYTE),
    AREA_CODE VARCHAR2(10 BYTE),
    AREA VARCHAR2(64 BYTE),
    CITY_CODE VARCHAR2(10 BYTE),
    CITY VARCHAR2(64 BYTE),
    DISTRICT_CODE VARCHAR2(10 BYTE),
    DISTRICT VARCHAR2(64 BYTE),
    STATE_CODE VARCHAR2(10 BYTE),
    STATE VARCHAR2(64 BYTE),
    ZIPCODE VARCHAR2(32 BYTE),
    COUNTRY_CODE VARCHAR2(10 BYTE),
    COUNTRY VARCHAR2(80 BYTE),
    DELETED CHAR(1 BYTE),
    CREATED_BY NUMBER(8),
    CREATED_DATE DATE,
    LAST_UPD_BY NUMBER(8),
    LAST_UPD_DATE DATE,
    PREV_LINK NUMBER(9) DEFAULT 0
    )


    pls help . very urgent..

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    WHERE clause isn't restrictive enough - see if you can add another condition(s).

    Or, try with "SELECT DISTINCT".

  3. #3
    Join Date
    Mar 2007
    Posts
    623
    Just curious: with duplication you mean "doubling" values in all columns in the result set or just "doubling" the values from CUSTOMER_TBL table?

    Anyway, the current table definition does not represent data model at all. Now, both tables do not have any constrains, so they may contain duplicates in the column(s) which should represent a row. Also their relationship is not defined - there may be orphans (addresses of the non-existent customers). You should fix it by adding these primary/foreign key constraints. For their introduction, you may need to delete duplicate/orphan rows from both tables. Also they may help you in redefining of the joining condition(s).

    As you did not post the INSERT statements for sample data and the required result set, it is impossible to propose anything. Maybe just one thing - if you really do not join on all conditions, do not use DISTINCT as a "workaround" or that queries will be extremely slow.

  4. #4
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool

    Quote Originally Posted by extremeashol View Post
    while i am mapping two tables it shows duplication of data. please correct me..
    ... Etc ...
    As both Littlefoot and flyboy indicate, the duplicates may be due to each customer having multiple address'

    You must restrict your query to choose only one address, for example something like this:
    Code:
    SELECT ct.*, p.*
      FROM customer_tbl ct, party_address p
     WHERE p.party_id = ct.customer_id
       AND p.deleted = 'N'
       AND p.last_upd_date = (
           SELECT MAX( last_upd_date )
             FROM party_address c
            WHERE c.party_id = ct.customer_id);
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  5. #5
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    duplicates can be avoided by utilizing UNIQUE index
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

Posting Permissions

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