OraFAQ Forum: Server Utilities » using decode in SQL*Loader

Home » RDBMS Server » Server Utilities » using decode in SQL*Loader

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

using decode in SQL*Loader [message #447334]

Sat, 13 March 2010 14:28

ziggy25
Messages: 206
Registered: July 2005

Senior Member

For some reason, sql loader is raising an error for this line

CNT_CODE	CHAR "decode(:CNT_CODE,'GB',:CNT_CODE,RPAD(:CNT_CODE,3)",

Basically what i want to do is, if the country code is 'GB' then dont change anything but if its not append a space to the country code and return a 3 character value.

The error that is generated is

Record 3: Rejected - Error on table WORLD_COUNTRIES.
ORA-00917: missing comma

There is nothing wrong with the source data because i have tested these different combinations both work fine

CNT_CODE	CHAR "RPAD(:CNT_CODE,3)",

CNT_CODE	CHAR,

its only when i use the DECODE function that it complains. Is it something obvious im missing?

[Updated on: Sat, 13 March 2010 14:36]

Report message to a moderator

Re: using decode in SQL*Loader [message #447336 is a reply to message #447334]

Sat, 13 March 2010 14:40

ziggy25
Messages: 206
Registered: July 2005

Senior Member

I knew it was something obvious. There was a comma missing in teh decode function.

Thanks Smile

Report message to a moderator

Re: using decode in SQL*Loader [message #447345 is a reply to message #447336]

Sat, 13 March 2010 15:28

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

ziggy25 wrote on Sat, 13 March 2010 21:40

I knew it was something obvious

Obviously, as SQL*Loader told you so.

ORA-00917: missing comma

Report message to a moderator

Re: using decode in SQL*Loader [message #447617 is a reply to message #447345]

Tue, 16 March 2010 12:34

Barbara Boehmer
Messages: 9102
Registered: November 2002
Location: California, USA

Senior Member

The error about a missing comma is somewhat misleading, since what is missing is a right parenthesis, not a comma. I guess it can't tell if there is another decode value missing which should be preceded by a comma or a parenthesis missing to close it.

[Updated on: Tue, 16 March 2010 12:37]

Report message to a moderator

Previous Topic:	Copy data from one DB to another
Next Topic:	SQLLDR decimal field problem

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Jan 14 07:35:15 CST 2025