TRIM() IN Merge Statement NOT WORKS

Mr.Vivek · #1 (**permalink**) 01-23-12, 05:50

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

Littlefoot · #2 (**permalink**) 01-23-12, 06:43

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>

Mr.Vivek · #3 (**permalink**) 01-23-12, 07:56

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.