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

    Question Unanswered: data duplication - while maping it gives twise the result

    greetings to all,
    i am mapping one table into 2 tabble to get the respective area name for the customer id. my parent table has customer no's and address table has area names, but there is no common coloumn between these two tables, so i am iserting a new table which has common coloumns for noth the table to fetch the dat, but while doing it it shows the datas twise as it has original. how can i rectify this issue. need valuable advice and idea. i am giving the coloumn names used for each table here..

    parent table which has to be mapped :

    CRN
    CUSTOMER_TYPE
    AFFILIATE_NAME
    DISTRICT_NAME
    STATE_NAME
    REGION_NAME
    DISTRIBUTER_ID
    EXECUTIVE
    PACKAGE_CODE
    PACKAGE_RATE
    PACKAGE_SUBS
    PACKAGE_REVENUE
    AGREEMENT_NO


    and the table i am inserting inbetween

    CUSTOMER_ID
    CUSTOMER_NBR
    CUSTOMER_TYPE
    CATEGORY
    NOTES
    BILL_TO_ID
    OPENTITY_TYPE
    OPENTITY_ID
    CREATED_BY
    CREATED_DATE
    LAST_UPD_BY
    LAST_UPD_DATE
    PREV_LINK
    TIMEZONE
    BILLING_MEDIA
    GROUP_ID
    REFERENCE_NO
    REFERENCE_TYPE
    STATUS


    and the table i have to fetch teh area name for the customer id for the parent tabel is



    ADDRESS_ID
    PARTY_ID
    ADDRESSTYPE_CODE
    ADDRESSTEMPLATE_ID
    LINE1_CODE
    ADDRLINE1
    LINE2_CODE
    ADDRLINE2
    STREET_CODE
    STREET
    AREA_CODE
    AREA
    CITY_CODE
    CITY
    DISTRICT_CODE
    DISTRICT
    STATE_CODE
    STATE
    ZIPCODE
    COUNTRY_CODE
    COUNTRY
    DELETED
    CREATED_BY
    CREATED_DATE
    LAST_UPD_BY
    LAST_UPD_DATE
    PREV_LINK



    this is the querry i have written..

    SELECT CT.CRN,CT.CUSTOMER_TYPE,CT.AFFILIATE_NAME,CT.STATE _NAME,CT.DISTRICT_NAME,P.AREA,CT.REGION_NAME,CT.DI STRIBUTER_ID,CT.PACKAGE_CODE,
    CT.PACKAGE_RATE,CT.PACKAGE_SUBS,CT.PACKAGE_REVENUE ,CT.AGREEMENT_NO FROM custom_tbl_rpt_active_db1 CT ,PARTY_ADDRESS P,CUSTOMER_TBL C
    WHERE C.CUSTOMER_ID = P.PARTY_ID AND CT.CRN = C.CUSTOMER_NBR

    pls help..

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    please post actual DDL (CREATE TABLE) for each table
    Code:
    SELECT ct.crn, 
           ct.customer_type, 
           ct.affiliate_name, 
           ct.state _name, 
           ct.district_name, 
           p.area, 
           ct.region_name, 
           ct.di    stributer_id, 
           ct.package_code, 
           ct.package_rate, 
           ct.package_subs, 
           ct.package_revenue, 
           ct.agreement_no 
    FROM   custom_tbl_rpt_active_db1 ct, 
           party_address p, 
           customer_tbl c 
    WHERE  c.customer_id = p.party_id 
           AND ct.crn = c.customer_nbr
    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.

  3. #3
    Join Date
    Feb 2012
    Posts
    14

    Question script for the tables..

    this is the parent table - script : CUSTOM_TBL_RPT_ACTIVE_DB1

    CREATE TABLE CUSTOM_TBL_RPT_ACTIVE_DB1
    (
    CRN NUMBER,
    CUSTOMER_TYPE VARCHAR2(64 BYTE),
    AFFILIATE_NAME VARCHAR2(128 BYTE),
    DISTRICT_NAME VARCHAR2(64 BYTE),
    STATE_NAME VARCHAR2(64 BYTE),
    REGION_NAME VARCHAR2(64 BYTE),
    DISTRIBUTER_ID VARCHAR2(128 BYTE),
    EXECUTIVE VARCHAR2(128 BYTE),
    PACKAGE_CODE VARCHAR2(64 BYTE),
    PACKAGE_RATE NUMBER,
    PACKAGE_SUBS NUMBER,
    PACKAGE_REVENUE NUMBER,
    AGREEMENT_NO VARCHAR2(128 BYTE)
    )


    and the Party_address table


    ALTER TABLE <schema>.PARTY_ADDRESS
    DROP PRIMARY KEY CASCADE;
    DROP TABLE <schema>.PARTY_ADDRESS CASCADE CONSTRAINTS;

    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
    )


    and the customer_tbl script

    ALTER TABLE <schema>.CUSTOMER_TBL
    DROP PRIMARY KEY CASCADE;
    DROP TABLE <schema>.CUSTOMER_TBL CASCADE CONSTRAINTS;

    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'
    )

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

    Cool

    Dear extremeashol,

    My guess would be that there may be multiple "address" per customer depending on the ADDRESSTYPE_CODE.

    Choose only a single address...
    Code:
    SELECT ct.crn,
           ct.customer_type,
           ct.affiliate_name,
           ct.state_name,
           ct.district_name,
           p.area,
           ct.region_name,
           ct.di stributer_id,
           ct.package_code,
           ct.package_rate,
           ct.package_subs,
           ct.package_revenue,
           ct.agreement_no
      FROM custom_tbl_rpt_active_db1 ct, party_address p, customer_tbl c
     WHERE c.customer_id = p.party_id
       AND ct.crn = c.customer_nbr
       AND p.ADDRESSTYPE_CODE = 'THIS ONE'
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

Posting Permissions

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