Default value for NULL External table field

[bgreen]

Hi,

I want to place one fields value into anothers if it is NULL. I am not sure how to do this. Here is my code for the external table creation:

CREATE TABLE XXCPA_BANKFIF_EXT
(
BANK_NUMBER VARCHAR2(4),
BANK_NUM VARCHAR2(5),
INSTITUTION_NAME_ENG VARCHAR2(36),
INSTITUTION_NAME_FR VARCHAR2(36),
BRANCH_DOMICILE VARCHAR2(36),
CIVIC_ADDR VARCHAR2(36),
POSTAL_ADDR VARCHAR2(36),
CITY VARCHAR2(36),
PROV_CODE VARCHAR2(4),
POSTAL_CODE VARCHAR2(10),
BANK_BRANCH_NAME_ALT VARCHAR2(3)
)
ORGANIZATION EXTERNAL
( TYPE ORACLE_LOADER
DEFAULT DIRECTORY XXCLIC_INTF_DIR
ACCESS PARAMETERS
( RECORDS DELIMITED BY NEWLINE
LOGFILE 'CPA_BANKFIF.LOG'
BADFILE 'CPA_BANKFIF.BAD'
fields terminated by ',' optionally enclosed by '"'
MISSING FIELD VALUES ARE NULL
(
BANK_NUMBER POSITION(12-15),
BANK_NUM POSITION(16-20),
INSTITUTION_NAME_ENG POSITION(34-69),
INSTITUTION_NAME_FR POSITION(70-105),
BRANCH_DOMICILE POSITION(106-141) EXTERNAL "NVLBRANCH_DOMICILE,:BANK_NUMBER)",
CIVIC_ADDR POSITION(142-177),
POSTAL_ADDR POSITION(178-213),
CITY POSITION(214-249),
PROV_CODE POSITION(250-253),
POSTAL_CODE POSITION(254-263),
BANK_BRANCH_NAME_ALT POSITION(106-108)
) )
LOCATION (XXCLIC_INTF_DIR:'bankfif.dat')
)
REJECT LIMIT UNLIMITED
NOPARALLEL
NOMONITORING;

What I want is if BRANCH_DOMICILE field is NULL (EMPTY) then I want to populate it with the value of fields BANK_NUMBER and BANK_NUM. How can I do this?????

 

[SantaMufasa]

BGreen,

Sorry, but you cannot effect INSERTs, UPDATEs, or DELETEs against an external table. You can/may, however, create a VIEW against your external table that does the transformation you propose with code such as this:

CREATE VIEW XXCPA_BANKFIF_VIEW as
SELECT
 BANK_NUMBER
,BANK_NUM
,INSTITUTION_NAME_ENG
,INSTITUTION_NAME_FR
,nvl(BRANCH_DOMICILE,bank_number||’/’||bank_num) BRANCH_DOMICILE
,CIVIC_ADDR
,POSTAL_ADDR
,CITY
,PROV_CODE
,POSTAL_CODE
,BANK_BRANCH_NAME_ALT
from XXCPA_BANKFIF_EXT;

Let us know how that works for you.




Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[bgreen]

Thanks for the help. I think I found a way around this. I am inserting into another table anyway. I will use NVL statements when I am inserting.