Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2011
    Posts
    25

    Unanswered: TRIM() IN Merge Statement NOT WORKS

    --------------------------------------------------------------------------------------------------------
    CREATE TABLE SORCETABLE ( FNAME VARCHAR2(20 BYTE), LNAME VARCHAR2(20 BYTE), CITY VARCHAR2(20 BYTE) );

    CREATE TABLE TARGETTABLE ( ID NUMBER, FNAME VARCHAR2(20 BYTE), LNAME VARCHAR2(20 BYTE), CITY VARCHAR2(20 BYTE) );

    INSERT INTO SORCETABLE (FNAME,LNAME,CITY) VALUES (NULL,'Rajesh','abc','R_city');
    INSERT INTO SORCETABLE (FNAME,LNAME,CITY) VALUES (NULL,'Vivek',NULL,'V_city');
    INSERT INTO SORCETABLE (FNAME,LNAME,CITY) VALUES (NULL,'Sanjay',NULL,'S_city');
    INSERT INTO SORCETABLE (FNAME,LNAME,CITY) VALUES (NULL,'Ishan','xyz','I_city');
    Insert into SORCETABLE (FNAME,LNAME,CITY) values (null,'Bhavesh',null,'B_city');




    MERGE INTO TARGETTABLE TRG
    USING SORCETABLE SRC
    ON
    (
    TRG.FNAME = SRC.FNAME AND
    TRIM(TRG.LNAME) = TRIM(SRC.LNAME )
    )
    WHEN MATCHED THEN
    UPDATE SET
    TRG.CITY = SRC.CITY
    WHEN NOT MATCHED
    THEN
    INSERT (TRG.ID , TRG.FNAME , TRG.LNAME , TRG.CITY )
    VALUES (SEQ_TEST.NEXTVAL ,SRC.FNAME ,SRC.LNAME , SRC.CITY );


    COMMIT;
    -------------------------------------------------------------------------------------------------------------
    IF the Above code EXECUTE THEN THE ROWS HAVING NULL "LNAME" ARE INSERTED MULTIPLE TIMES
    IF USED NVL(TRIM(TRG.LNAME),'*') = NVL( TRIM(SRC.LNAME,'*') THEN WORK FINE
    BUT IT IS NOT STD SOLUTIONS?.
    Plz give the Alternate to Used TRIM() in merge Statement

    Thanks

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    First of all, your test case statements are invalid - SORCETABLE contains 3 columns, while INSERT INTO statements contain 4 values. What is correct? I suppose that the first NULL (in INSERT INTO) shouldn't be there.

    Anyway: I can't reproduce what you are saying; there are no "multiple records for rows having null LNAME):
    Code:
    SQL> select * from v$version;
    
    BANNER
    ----------------------------------------------------------------
    Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
    PL/SQL Release 10.2.0.1.0 - Production
    CORE    10.2.0.1.0      Production
    TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
    NLSRTL Version 10.2.0.1.0 - Production
    
    SQL> select * from sorcetable;
    
    FNAME                LNAME                CITY
    -------------------- -------------------- --------------------
    Rajesh               abc                  R_city
    Vivek                                     V_city
    Sanjay                                    S_city
    Ishan                xyz                  I_city
    Bhavesh                                   B_city
    
    SQL> select * from targettable;
    
    no rows selected
    
    SQL> merge into targettable trg
      2       using sorcetable src
      3          on (trg.fname = src.fname and trim (trg.lname) = trim (src.lname))
      4  when matched
      5  then
      6     update set trg.city = src.city
      7  when not matched
      8  then
      9     insert     (trg.id,
     10                 trg.fname,
     11                 trg.lname,
     12                 trg.city)
     13         values (seq_test.nextval,
     14                 src.fname,
     15                 src.lname,
     16                 src.city);
    
    5 rows merged.
    
    SQL> select * from targettable;
    
            ID FNAME                LNAME                CITY
    ---------- -------------------- -------------------- --------------------
            11 Rajesh               abc                  R_city
            12 Bhavesh                                   B_city
            13 Ishan                xyz                  I_city
            14 Vivek                                     V_city
            15 Sanjay                                    S_city
    
    SQL>

  3. #3
    Join Date
    Dec 2011
    Posts
    25

    run that merge script Many times to recreated

    What is correct? I suppose that the first NULL (in INSERT INTO) shouldn't be there.
    There is correction in the "INSERT INTO" the first Null shouldn't be there.

    If you run the Same Merge Statement Again then the Result Should be "5 records Merged" and the Target Table should Contain only 5 Records as from Source table ,
    But the Target Table has Duplicate Entry for the LNAME having Null value.
    Plz run that merge script Many times.

  4. #4
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713
    Quote Originally Posted by Mr.Vivek View Post
    ... Etc ...
    But the Target Table has Duplicate Entry for the LNAME having Null value.
    Plz run that merge script Many times.
    It is NOT due to the Null LNAME or TRIM() function, the issue is that you do not have PRIMARY KEY constraint to prevent duplicates.

    PS: Null values always compare "undetermind" value.
    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
  •