Home » RDBMS Server » Server Utilities » SQL Loader using WHEN clause to selectively load into multiple tables
| SQL Loader using WHEN clause to selectively load into multiple tables [message #173542] |
Tue, 23 May 2006 01:46  |
sujatha_adki
Messages: 2
Registered: May 2006
|
Junior Member |
|
|
My data file is a pipe delimited file and has '1' or '2' in the first column. Depending on the value of 1st column i need to load the data into TABLE LOGO or TABLE LTR_LOGO. Also, I need to skip the 1st column while loading into tables.
data file sample-
1|P|AB|
1|A|ABCD|
2|1|P|A & B COMPANY|B1PR|NEW ONE COMPANY|P|168|92|
2|2|A|AAA LAND|B1PR|NEW ONE COMPANY|P|84|2|
My ctl file is as follows-
LOAD DATA
APPEND
INTO TABLE LOGO
WHEN (01)='1'
FIELDS TERMINATED BY '|'
OPTIONALLY ENCLOSED BY ' '
TRAILING NULLCOLS
(
INDICATOR FILLER,
LOGO_CD,
LOGO_NM,
LOGO_DESC_TX "decode(:LOGO_DESC_TX,NULL,:LOGO_NM,:LOGO_NM)",
LOGO_EFF_DT "trunc(SYSDATE)",
LOGO_TRMNT_DT FILLER,
CRE_USR_ID "USER",
CRE_TS SYSDATE,
UPDT_USR_ID "USER",
UPDT_TS SYSDATE
)
INTO TABLE LTR_LOGO
WHEN (01)='2'
FIELDS TERMINATED BY '|'
OPTIONALLY ENCLOSED BY ' '
TRAILING NULLCOLS
(
INDICATOR1 FILLER,
LTR_CD "TO_NUMBER(:LTR_CD)",
LOGO_CD,
LTR_LOGO_EFF_DT "trunc(SYSDATE)",
LTR_LOGO_TRMNT_DT FILLER,
CRE_USR_ID "USER",
CRE_TS SYSDATE,
UPDT_USR_ID "USER",
UPDT_TS SYSDATE
)
Though the records are loaded in Table LOGO, I get following error for all rows of table 2
Record 6: Rejected - Error on table "LTR_LOGO", column LTR_CD.
ORA-01400: cannot insert NULL into ("LTR_LOGO"."LTR_CD")
Also, the POSITION for INDICATOR1 in LTR_LOGO is displayed as NEXT in log whereas this should be FIRST.
Can anyone plz help as to why LTR_CD is being mapped to NULL field and how to resolve this?
|
|
|
|
|
|
| Re: SQL Loader using WHEN clause to selectively load into multiple tables [message #173571 is a reply to message #173559] |
Tue, 23 May 2006 04:22  |
sujatha_adki
Messages: 2
Registered: May 2006
|
Junior Member |
|
|
I got the solution for that
I need to mention position for the filler
LOAD DATA
APPEND
INTO TABLE LOGO
WHEN (01)='1'
FIELDS TERMINATED BY '|'
OPTIONALLY ENCLOSED BY ' '
TRAILING NULLCOLS
(
INDICATOR FILLER position(1),
LOGO_CD,
LOGO_NM,
LOGO_DESC_TX "decode(:LOGO_DESC_TX,NULL,:LOGO_NM,:LOGO_NM)",
LOGO_EFF_DT "trunc(SYSDATE)",
LOGO_TRMNT_DT FILLER,
CRE_USR_ID "USER",
CRE_TS SYSDATE,
UPDT_USR_ID "USER",
UPDT_TS SYSDATE
)
INTO TABLE LTR_LOGO
WHEN (01)='2'
FIELDS TERMINATED BY '|'
OPTIONALLY ENCLOSED BY ' '
TRAILING NULLCOLS
(
INDICATOR1 FILLER position(1),
ACCT_ORG_CD "TO_NUMBER(:ACCT_ORG_CD)",
LOGO_CD,
LTR_LOGO_EFF_DT "trunc(SYSDATE)",
LTR_LOGO_TRMNT_DT FILLER,
CRE_USR_ID "USER",
CRE_TS SYSDATE,
UPDT_USR_ID "USER",
UPDT_TS SYSDATE
)
I have another issue here.
Here LTR_LOGO.LOGO_CD is a foreign key to LOGO.LOGO_CD. Though the records are loaded in table LOG, I get the following error for table LTR_LOGO
Rejected - Error on table "LTR_LOGO".
ORA-02291: integrity constraint (OWNER.FK_ACCT_ORG_1) violated - parent key not found
How can I commit after each insertion in parent table LOGO?
|
|
|
|
|
|
Goto Forum:
Current Time: Mon Jul 01 00:25:35 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
