Home » RDBMS Server » Server Utilities » SQLLDR - Deriving another field while loading
SQLLDR - Deriving another field while loading [message #421801] Thu, 10 September 2009 12:47 Go to next message
nidi_03
Messages: 10
Registered: May 2009
Location: US
Junior Member
Hi !

I have to implement the below scenario
If char1 of field_A is alpha and char2-4 are numeric, then Field_B ='X'
If char 1-2 are numeric and char4 is aplha, then Field_B='Y'
If char 1-4 are numeric then Field_C='Z'
Is it possible to derive Field_B while loading through sqlldr?

Field_A  Field_B
A123     X
123A     Y
1234     Z


Would appreciate any help.
Thanks!

Re: SQLLDR - Deriving another field while loading [message #421805 is a reply to message #421801] Thu, 10 September 2009 13:00 Go to previous messageGo to next message
joy_division
Messages: 4504
Registered: February 2005
Location: East Coast USA
Senior Member
Sure, you can either make a function and pass field_a to it or make a decode statement in the control file itself.

[added]

sorry, this may help http://download.oracle.com/docs/cd/B19306_01/server.102/b14215/ldr_control_file.htm#i1006645

[Updated on: Thu, 10 September 2009 13:05]

Report message to a moderator

Re: SQLLDR - Deriving another field while loading [message #421806 is a reply to message #421801] Thu, 10 September 2009 13:32 Go to previous messageGo to next message
nidi_03
Messages: 10
Registered: May 2009
Location: US
Junior Member
Thanks for the reply! My limitation is I can use only sqls and cannot create any functions. How can I use decode in this case, can you pls give me a pointer? Thanks!
Re: SQLLDR - Deriving another field while loading [message #421809 is a reply to message #421801] Thu, 10 September 2009 14:02 Go to previous messageGo to next message
Michel Cadot
Messages: 58861
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> create table t (fielda varchar2(10), fieldb varchar2(10));

Table created.

SQL> host type t.ctl
load data
infile *
into table t
fields terminated by ","  TRAILING NULLCOLS
(
fielda char,
fieldb char "case when substr(:fielda,1,1) between 'A' and 'Z' then 'X'
                  when substr(:fielda,4,1) between 'A' and 'Z' then 'Y'
                  else 'Z'
             end"
)
begindata
A123
123A
1234

SQL> host sqlldr michel/michel control=t.ctl log=t.log

SQL*Loader: Release 10.2.0.4.0 - Production on Jeu. Sept. 10 21:01:36 2009

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

Commit point reached - logical record count 3

SQL> select * from t;
FIELDA     FIELDB
---------- ----------
A123       X
123A       Y
1234       Z

3 rows selected.

Regards
Michel
Re: SQLLDR - Deriving another field while loading [message #421815 is a reply to message #421801] Thu, 10 September 2009 15:23 Go to previous messageGo to next message
nidi_03
Messages: 10
Registered: May 2009
Location: US
Junior Member
This is great!Thanks Michel!
Unfortunately, using all the conditions I need, I am getting an error of "Token longer than max allowable length of 258 chars".
Is there a possible workaround to this error?

Here is the piece I am using. I trimmed all the spaces and tried but still getting this error. Please let me know if there is a possible workaround to this error else I will have to process this in the database.
case when fielda is null then null 
         when regexp_like(substr(fielda, 1, 3), '[[:digit:]]') and
              (substr(fielda, 4, 2) is null 
              then 'W'
         when regexp_like(substr(fielda, 1, 4), '[[:digit:]]') and
              (substr(fielda, 5, 1)) is null 
              then 'W'
         when regexp_like(substr(fielda, 1, 5), '[[:digit:]]') and
              then 'X'
	 when regexp_like((substr(fielda, 1, 4), '[[:digit:]]') and
	      regexp_like(substr(fielda, 5, 1), '[[:digit:]]') 
              then 'X'
         when regexp_like(substr(fielda, 1, 1), '[[:digit:]]') and
              regexp_like(substr(fielda, 2, 4), '[[:digit:]]') and
              then 'Y'
              else 'Z'

[Updated on: Fri, 11 September 2009 00:25] by Moderator

Report message to a moderator

Re: SQLLDR - Deriving another field while loading [message #421841 is a reply to message #421815] Fri, 11 September 2009 00:30 Go to previous message
Michel Cadot
Messages: 58861
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You have to define the spefication in clear, exhaustive and simple rules. Also you have to define the field and values sizes.
What I gave you was the implementation of the rules you gave assuming there were exhaustive and values were always 4 characters as your example showed it.

Regards
Michel
Previous Topic: need to upload huge volume of AP and AR related transaction
Next Topic: Step-by-Step Recovering dump file using IMP
Goto Forum:
  


Current Time: Fri Aug 22 15:39:09 CDT 2014

Total time taken to generate the page: 0.18398 seconds