I have Three Tables all of them have a column called RECORD_TYPE which is present as empty records in the Source file.

Table 1:

|RECORD_TYPE|
----------
|('null')   |

Table 2:

|RECORD_TYPE|
----------
|""         |

Table 3 :

|RECORD_TYPE|
 ----------
|""         |

The Column mapped has the same datatype throughout the three tables i.e. VARCHAR2(255)

Table 1 shows correct result --> ('null') but the other two are giving empty inverted commas.

DDL for Table 1 :

      CREATE TABLE "ODSSTAGE"."INXN_LA_RENEWAL_STG" 
       (    col1,
            col2,
            .
            .
    
        "RECORD_TYPE" VARCHAR2(255 BYTE) 
       )  

DDL for Table 2 :

  CREATE TABLE "ODSSTAGE"."INXN_LA_TERMINATION_STG" 
   (    col1,
       col2,
        .
        .
        .



    "RECORD_TYPE" VARCHAR2(255 BYTE)  
   ) 

DDL for Table 3 :

      CREATE TABLE "ODSSTAGE"."INXN_LA_NEW_STG" 
       (    col1,
            col2,
            ..
            ..
            ..
        "RECORD_TYPE" VARCHAR2(255 BYTE) 
       ) 

 

Source are same for all three

Please keep in mind : the "" is something the record is showing as empty but when i copy paste the record in notepad or somewhere is else i am getting ""

CodePudding user response：

What do you get if you use SELECT DUMP(record_type) FROM ODSSTAGE.INXN_LA_RENEWAL_STG or SELECT DUMP(record_type) FROM ODSSTAGE.INXN_LA_NEW_STG?

just plain NULL and for second I get Typ=1 Len=1: 13

You do not have a NULL (empty) value in the ODSSTAGE.INXN_LA_NEW_STG table you have a single carriage return (ASCII 13) character.

You can find those rows using:

SELECT *
FROM   ODSSTAGE.INXN_LA_NEW_STG
WHERE  record_type = CHR(13);

Or update them to a NULL value using:

UPDATE ODSSTAGE.INXN_LA_NEW_STG
SET   record_type = NULL
WHERE record_type = CHR(13);