Home » RDBMS Server » Server Utilities » SQL*Loader problem
SQL*Loader problem [message #140470] Tue, 04 October 2005 07:16 Go to next message
himang
Messages: 282
Registered: March 2005
Location: Bangalore
Senior Member
I am facing problem while loading data into multiple tables from same INPUT file through single control file using WHEN clause. In the first table "FPD_STG_Errors_Messages"
the data gets loaded correctly but for the other table "FPD_STG_LogDetails", it is inserting NULL into the log_Description field. The number of records are correctly loaded but the field value remains NULL.

If I put the <> condition first then it loads data into "FPD_STG_LogDetails" table and in other table the field description comes as NULL.

Where I am being wrong?

CTL File
Options ( Direct = True)
LOAD DATA
INFILE 'C:\Factory Planner Data\Log Files\fperr602.txt'
BADFILE 'C:\Factory Planner Data\Log Files\FPErr.bad'
DISCARDFILE 'C:\Factory Planner Data\Log Files\FPErr.dis'
TRUNCATE
INTO TABLE FPD_STG_Errors_Messages
WHEN (01:07) = 'Warning'
(
Log_Index SEQUENCE(1,1),
Log_Description Char(4000),
LogFile_ID Constant '1'
)
INTO TABLE FPD_STG_LogDetails
WHEN (01:07) <> 'Warning'
(
Log_Index SEQUENCE(1,1),
Log_Description Char(4000),
LogFile_ID Constant '1'
)

Data in file
START: read routing_data (NULL) (03:01:00)
END: read routing_data (NULL) (03:01:00): 0.000 sec
Warning: deleting mfg order 515763300_1-001 which is no longer pegged.
Warning: deleting mfg order 515885400_1-001 which is no longer pegged.
Warning: deleting mfg order 518833100_1-001 which is no longer pegged.
Warning: deleting mfg order 519245700_1-001 which is no longer pegged


Thanks in advance.

Regards
Himanshu Gupta

Report message to a moderator

Re: SQL*Loader problem [message #140646 is a reply to message #140470] Wed, 05 October 2005 01:25 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9100
Registered: November 2002
Location: California, USA
Senior Member
Adding POSITION(1) where shown should fix it.

Options ( Direct = True)
LOAD DATA
INFILE 'C:\Factory Planner Data\Log Files\fperr602.txt'
BADFILE 'C:\Factory Planner Data\Log Files\FPErr.bad'
DISCARDFILE 'C:\Factory Planner Data\Log Files\FPErr.dis'
TRUNCATE
INTO TABLE FPD_STG_Errors_Messages
WHEN (01:07) = 'Warning'
(
Log_Index SEQUENCE(1,1),
Log_Description Char(4000),
LogFile_ID Constant '1'
)
INTO TABLE FPD_STG_LogDetails
WHEN (01:07) <> 'Warning'
(
Log_Index SEQUENCE(1,1),
Log_Description POSITION(1) Char(4000),
LogFile_ID Constant '1'
)

Report message to a moderator

Re: SQL*Loader problem [message #140855 is a reply to message #140470] Wed, 05 October 2005 23:01 Go to previous messageGo to next message
himang
Messages: 282
Registered: March 2005
Location: Bangalore
Senior Member
Thanks a lot. It worked.

But I have one more question regarding CTL file. I want to use OR condition in WHEN clause while loading the data. Such as

WHEN (01:05) = 'START' OR (01:03) = 'END'
(
Log_Index SEQUENCE(1,1),
Log_Description Char(4000),
LogFile_ID Constant '1'
)
INTO TABLE FPD_STG_LogDetails
WHEN (01:05) != 'START' AND (01:03) != 'END'

(
Log_Index SEQUENCE(1,1),
Log_Description POSITION(1) Char(4000),
LogFile_ID Constant '1'
)

The AND clause works but the OR clause is not supported. Any syntax error or this is not supported? I have worked around it and splitted the OR condition into 2 when clause but just wanted to know whether the usage of 'OR' clause is allowed or not.

Thanks in advance.

Report message to a moderator

Re: SQL*Loader problem [message #141279 is a reply to message #140855] Sat, 08 October 2005 01:47 Go to previous message
Barbara Boehmer
Messages: 9100
Registered: November 2002
Location: California, USA
Senior Member
OR is not supported, as you suspected.

Report message to a moderator

Previous Topic: how to export only database information
Next Topic: data base conversion
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Sat Nov 09 00:58:38 CST 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.