Home » RDBMS Server » Server Utilities » using decode in SQL*Loader
using decode in SQL*Loader [message #447334] Sat, 13 March 2010 14:28 Go to next message
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 Go to previous messageGo to next message
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
Re: using decode in SQL*Loader [message #447345 is a reply to message #447336] Sat, 13 March 2010 15:28 Go to previous messageGo to next message
Littlefoot
Messages: 20901
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
Re: using decode in SQL*Loader [message #447617 is a reply to message #447345] Tue, 16 March 2010 12:34 Go to previous message
Barbara Boehmer
Messages: 8636
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:
  


Current Time: Fri Dec 09 13:49:45 CST 2016

Total time taken to generate the page: 0.14856 seconds