Home » RDBMS Server » Server Utilities » calculated column population using sql loader (oracle 10.2)
calculated column population using sql loader [message #562398] Wed, 01 August 2012 04:43 Go to next message
guddu_12
Messages: 227
Registered: April 2012
Location: UK
Senior Member
Hi,

I want to insert calculated data while loading the data from sql loader.
eg let say column INTERNAL_TRANSACTION_ID,WAIT_TIME,MESSAGE_GUID,TRS_SIZE,RETURN_MESSAGE_GUID are null in input file then i want to populate column DEV_FLAG as 'U' ELSE 'D'

My control file looks like below but doesn't have DEV_FLAG.

load data
        BADFILE '/backup/temp/rajesh/RIO/BadFiles/FILENAME'
        append into table TEMP_rio_RESP_TIME_LND
        TRAILING NULLCOLS
        (
        INSTALLATION_ID          CHAR
        TERMINATED BY "|" OPTIONALLY ENCLOSED BY '"',
        TRANSACTION_ID           CHAR
        TERMINATED BY "|" OPTIONALLY ENCLOSED BY '"',
        SERVER_ID                CHAR
        TERMINATED BY "|" OPTIONALLY ENCLOSED BY '"',
        CLINICAL_TRANSACTION_ID  CHAR
        TERMINATED BY "|" OPTIONALLY ENCLOSED BY '"',
        RESPONSE_TIME DECIMAL EXTERNAL
        TERMINATED BY "|" OPTIONALLY ENCLOSED BY '"',
        TRANSACTION_START_TIME   TIMESTAMP
        TERMINATED BY "|" OPTIONALLY ENCLOSED BY '"' "TO_TIMESTAMP(:TRANSACTION_START_TIME,'DD/MM/YYYY HH24:MI:SSXFF')",
        TRANSACTION_END_TIME     TIMESTAMP
        TERMINATED BY "|" OPTIONALLY ENCLOSED BY '"' "TO_TIMESTAMP(:TRANSACTION_END_TIME,'DD/MM/YYYY HH24:MI:SSXFF')",
        LOCATION_ID              CHAR
        TERMINATED BY "|" OPTIONALLY ENCLOSED BY '"',
        WAIT_TIME          DECIMAL EXTERNAL
        TERMINATED BY "|" OPTIONALLY ENCLOSED BY '"',
        INTERNAL_TRANSACTION_ID  CHAR
        TERMINATED BY "|" OPTIONALLY ENCLOSED BY '"',
        INTERNAL_TIME  DECIMAL EXTERNAL
        TERMINATED BY "|" OPTIONALLY ENCLOSED BY '"',
        EXTERNAL_SERVICE_ID      CHAR
        TERMINATED BY "|" OPTIONALLY ENCLOSED BY '"',
        EXTERNAL_SERVICE_TIME    DECIMAL EXTERNAL
        TERMINATED BY "|" OPTIONALLY ENCLOSED BY '"',
        LOCAL_SERVICE_ID         CHAR
        TERMINATED BY "|" OPTIONALLY ENCLOSED BY '"',
        LOCAL_SERVICE_TIME       DECIMAL EXTERNAL
        TERMINATED BY "|" OPTIONALLY ENCLOSED BY '"',
        MESSAGE_GUID             CHAR
        TERMINATED BY "|" OPTIONALLY ENCLOSED BY '"',
        RETURN_MESSAGE_GUID      CHAR
        TERMINATED BY "|" OPTIONALLY ENCLOSED BY '"',
        TRS_SIZE     DECIMAL EXTERNAL
        TERMINATED BY "|" OPTIONALLY ENCLOSED BY '"'  ,
        FILE_NAME    CONSTANT
        "FILENAME",
        DATE_LOADED              SYSDATE  ,
        ORIGINAL_DATE_LOADED     SYSDATE
        )


Thanks
Re: calculated column population using sql loader [message #562399 is a reply to message #562398] Wed, 01 August 2012 04:52 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Something like:
load data
        BADFILE '/backup/temp/rajesh/RIO/BadFiles/FILENAME'
        append into table TEMP_rio_RESP_TIME_LND
        TRAILING NULLCOLS
        (
        INSTALLATION_ID          CHAR
        ...,
        DEV_FLAG EXPRESSION "DECODE(:INTERNAL_TRANSACTION_ID||:WAIT_TIME||:MESSAGE_GUID||:TRS_SIZE||:RETURN_MESSAGE_GUID, NULL, 'U', 'D')"
        )

Regards
Michel
Re: calculated column population using sql loader [message #562400 is a reply to message #562399] Wed, 01 August 2012 04:55 Go to previous message
guddu_12
Messages: 227
Registered: April 2012
Location: UK
Senior Member
Fantastic, i salute you sir.

Many thanks
Previous Topic: sequence in sqlldr
Next Topic: 10g-32bit,developer 6i
Goto Forum:
  


Current Time: Thu Mar 28 11:58:44 CDT 2024