Home » RDBMS Server » Server Utilities » Loading default value when exists null or blanks SQL Loader
Loading default value when exists null or blanks SQL Loader [message #188289] Thu, 17 August 2006 17:36 Go to next message
superoscarin@hotmail.com
Messages: 101
Registered: July 2006
Location: Mexico
Senior Member

Hi:

Are there any way with SQL Loader to load data by default in a specific field.

it's mean that if I have null or "", can i insert a defalut value for this field.

For example:

LOAD DATA
INFILE FILE1
into table A
APPEND

(
id integer external,
data1 boundfiller
data --and here insert a default value when exist a null or blank values??
)

Thanks to all for your answers
Alex
Re: Loading default value when exists null or blanks SQL Loader [message #188293 is a reply to message #188289] Thu, 17 August 2006 18:23 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
This should work for ya:

load data
infile file1
into table a
TRAILING NULLCOLS
(
id integer external,
data1 boundfiller,
data "nvl(:data,'default value')"
)
Re: Loading default value when exists null or blanks SQL Loader [message #188455 is a reply to message #188293] Fri, 18 August 2006 11:30 Go to previous messageGo to next message
superoscarin@hotmail.com
Messages: 101
Registered: July 2006
Location: Mexico
Senior Member

The value to insert is :data or :data1

load data
infile file1
into table a
TRAILING NULLCOLS
(
id integer external,
data1 boundfiller,
data "nvl(:data,'default value')"-- with this is not like apply a format to field data?? like a to_char or something similar??
)

and can i do something like this:

data "nvl(:data1||:data,:data1||'default value')"

*Concatenate the field data1 value plus default value or data field value??


Thanks ebrian
Alex.
Re: Loading default value when exists null or blanks SQL Loader [message #188461 is a reply to message #188455] Fri, 18 August 2006 12:37 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
Is this what your requirements are:

load data
infile *
into table a
fields terminated by ','
TRAILING NULLCOLS
(
id integer external,
data1 boundfiller,
data "nvl(:data,:data1||'default value')"
)
BEGINDATA
1,Test data for 1,
2,Test data for 2,NOTNULL

SQL> select * from a;

        ID DATA
---------- -------------------------------
         1 Test data for 1default value
         2 NOTNULL
Re: Loading default value when exists null or blanks SQL Loader [message #188480 is a reply to message #188461] Fri, 18 August 2006 13:20 Go to previous messageGo to next message
superoscarin@hotmail.com
Messages: 101
Registered: July 2006
Location: Mexico
Senior Member

Its very similar only that i always want to insert the second value (data).

The result should be...

ID DATA
---------- -------------------------------
1 Test data for 1default value
2 Test data for 2NOTNULL

Thanks chief
Alex
Re: Loading default value when exists null or blanks SQL Loader [message #188488 is a reply to message #188480] Fri, 18 August 2006 13:46 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
load data
infile *
into table a
fields terminated by ','
TRAILING NULLCOLS
(
id integer external,
data1 boundfiller,
data "nvl2(:data,:data1||:data,:data1||'default value')"
)
BEGINDATA
1,Test data for 1,
2,Test data for 2,NOTNULL

SQL> select * from a;

        ID DATA
---------- --------------------------------------
         1 Test data for 1default value
         2 Test data for 2NOTNULL
Re: Loading default value when exists null or blanks SQL Loader [message #188502 is a reply to message #188488] Fri, 18 August 2006 15:36 Go to previous message
superoscarin@hotmail.com
Messages: 101
Registered: July 2006
Location: Mexico
Senior Member

thanks my sensei...

You are really a very nice guy.

Greetings
Alex
Previous Topic: PLS-00103: Encountered the symbol ";" when expecting one of the following:
Next Topic: sqlldr how and where to execute
Goto Forum:
  


Current Time: Sun Dec 11 00:47:51 CST 2016

Total time taken to generate the page: 0.08726 seconds