Home » RDBMS Server » Server Utilities » Replacing Zero
Replacing Zero [message #270037] Tue, 25 September 2007 08:45 Go to next message
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 #270045 is a reply to message #270037] Tue, 25 September 2007 08:58 Go to previous messageGo to next message
Michel Cadot
Messages: 64131
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use an external table, then you can have the power of SQL to select what you want as you want.

Regards
Michel
Re: Replacing Zero [message #270070 is a reply to message #270045] Tue, 25 September 2007 10:29 Go to previous messageGo to next message
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 #270088 is a reply to message #270070] Tue, 25 September 2007 12:22 Go to previous messageGo to next message
Michel Cadot
Messages: 64131
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If you use 10g you can use regular expression but I still think external table is a better way and it is maintenable and can easely be changed if your client changes his mind.

Regards
Michel
Re: Replacing Zero [message #270103 is a reply to message #270088] Tue, 25 September 2007 14:19 Go to previous messageGo to next message
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 #270106 is a reply to message #270070] Tue, 25 September 2007 14:28 Go to previous messageGo to next message
joy_division
Messages: 4641
Registered: February 2005
Location: East Coast USA
Senior Member
decode the third character. If it's a zero, substring the rest of the string.
Re: Replacing Zero [message #270112 is a reply to message #270103] Tue, 25 September 2007 15:01 Go to previous messageGo to next message
Michel Cadot
Messages: 64131
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
A user can access a table only if you grant him the privilege to do it!

Regards
Michel
Re: Replacing Zero [message #270156 is a reply to message #270106] Tue, 25 September 2007 23:40 Go to previous messageGo to next message
poratips
Messages: 345
Registered: April 2005
Location: IL
Senior Member
Thanks.
Re: Replacing Zero [message #270327 is a reply to message #270106] Wed, 26 September 2007 09:57 Go to previous messageGo to next message
poratips
Messages: 345
Registered: April 2005
Location: IL
Senior Member
Thanks.
Could you please show me how to use DECODE with SUBSTR in sql*loader?

Thanks,
Re: Replacing Zero [message #270339 is a reply to message #270327] Wed, 26 September 2007 10:20 Go to previous messageGo to next message
Michel Cadot
Messages: 64131
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The same way you use REPLACE.

Regards
Michel
Re: Replacing Zero [message #270345 is a reply to message #270339] Wed, 26 September 2007 10:34 Go to previous messageGo to next message
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 #270349 is a reply to message #270345] Wed, 26 September 2007 10:42 Go to previous messageGo to next message
Michel Cadot
Messages: 64131
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I tried to but throwing me error.

Error? Which error?

Regards
Michel
Re: Replacing Zero [message #270351 is a reply to message #270345] Wed, 26 September 2007 10:45 Go to previous messageGo to next message
Michel Cadot
Messages: 64131
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Oh I see, you can't use DECODE there only in SQL.
It would be easier with external table as I always said.
Change DECODE to CASE.

Regards
Michel
Re: Replacing Zero [message #270359 is a reply to message #270351] Wed, 26 September 2007 11:21 Go to previous messageGo to next message
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 #270362 is a reply to message #270359] Wed, 26 September 2007 11:32 Go to previous messageGo to next message
Michel Cadot
Messages: 64131
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I you don't mind can you please post the error.

Regards
Michel
Re: Replacing Zero [message #270365 is a reply to message #270362] Wed, 26 September 2007 11:37 Go to previous messageGo to next message
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 Go to previous message
joy_division
Messages: 4641
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.
Previous Topic: Need help to write SQL*LOADER control file.
Next Topic: Opera Schema Management Tool for Oracle
Goto Forum:
  


Current Time: Wed Dec 07 12:27:01 CST 2016

Total time taken to generate the page: 0.10604 seconds