| Replacing Zero [message #270037] |
Tue, 25 September 2007 08:45  |
poratips
Messages: 345
Registered: April 2005
Location: IL
|
Senior Member |
|
|
Hi,
I am running my sql*loader and i am having problem now that i was replacing(removing) leading zero and it was fine but now user wants to leave zero after the code as it is as they have added new format code.
example:
AB01 should be replace as AB1
AB02 should be replace as AB2
AB03 should be replace as AB3
AX01 should be replace as AX1
BZ09 should be replace as BZ9
But
FH01 should stays as FH01
FH02 should stays as FH02
FH10 should stays as FH10
EX10 should stays as EX10
I am using following sql*loader and it's replacing zero but we need to avoid like FX10 to to be trimmed.
LOAD DATA
INFILE 'code'
TRUNCATE
INTO TABLE CODE
TRAILING NULLCOLS
(SEQ_NUM RECNUM,
s_code POSITION (41:44) "REPLACE(:S_code, '0','')",
TITLE POSITION(11:40),
J_CODE POSITION(1:6)
)
Thanks,
[Updated on: Tue, 25 September 2007 08:56] by Moderator Report message to a moderator |
|
|
|
|
|
| Re: Replacing Zero [message #270070 is a reply to message #270045] |
Tue, 25 September 2007 10:29  |
poratips
Messages: 345
Registered: April 2005
Location: IL
|
Senior Member |
|
|
I am sorry that i mis-typed.
It should read like:
AB01 should be replace as AB1
AB02 should be replace as AB2
AB03 should be replace as AB3
AX01 should be replace as AX1
BZ09 should be replace as BZ9
FH10 should stays as FH10
FH20 should stays as FH20
FH10 should stays as FH10
EX10 should stays as EX10
It means leading zero should be trimmed but trailing zero will remain as it is.
Thanks.
|
|
|
|
|
|
| Re: Replacing Zero [message #270103 is a reply to message #270088] |
Tue, 25 September 2007 14:19 |
poratips
Messages: 345
Registered: April 2005
Location: IL
|
Senior Member |
|
|
Thanks, We have still 9i.
I don't want to use ext table as user will be accessing this table. Is it any other way in sql*loader?
Thanks,
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Replacing Zero [message #270345 is a reply to message #270339] |
Wed, 26 September 2007 10:34 |
poratips
Messages: 345
Registered: April 2005
Location: IL
|
Senior Member |
|
|
Thanks.
I tried to but throwing me error.
Original was:
s_code POSITION (41:44) "REPLACE(:S_code, '0','')",
I tried with:
s_code POSITION (41:44) "DECODE(SUBSTR(:S_code,3,1),'0',SUBSTR(:S_code,1,2),:S_code)
What's worng here?
thank you.
|
|
|
|
|
|
|
|
| Re: Replacing Zero [message #270359 is a reply to message #270351] |
Wed, 26 September 2007 11:21 |
poratips
Messages: 345
Registered: April 2005
Location: IL
|
Senior Member |
|
|
Thanks.
I can use DECODE in sql*loader but i am having trouble with using SUBSTR with DECODE in sql*loader.
If you don't mind, could you please show me an example what you suggesting?
Thanks and appreciated your help!
|
|
|
|
|
|
| Re: Replacing Zero [message #270365 is a reply to message #270362] |
Wed, 26 September 2007 11:37 |
poratips
Messages: 345
Registered: April 2005
Location: IL
|
Senior Member |
|
|
SQL*Loader-350: Syntax error at line 9.
Expecting valid column specification, "," or ")", found "(".
DECODE(SUBSTR(:S_code,3,1),'0',SUBSTR(:S_code,1,2),:S_code
^
|
|
|
|
| Re: Replacing Zero [message #270373 is a reply to message #270345] |
Wed, 26 September 2007 12:52 |
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
|
Senior Member |
|
|
| poratips wrote on Wed, 26 September 2007 11:34 | Thanks.
I tried to but throwing me error.
Original was:
s_code POSITION (41:44) "REPLACE(:S_code, '0','')",
I tried with:
s_code POSITION (41:44) "DECODE(SUBSTR(:S_code,3,1),'0',SUBSTR(:S_code,1,2),:S_code)
What's worng here?
|
You are missing a close double quote and a comma.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
