IQ Server - load table and NULLs

Author

load table and NULLs

Sherlock, Kevin

2006-02-14, 8:26 pm

Simple issue.

I have an ascii (not binary) file with 3 columns delimited by commas:

jack,t,black,100
john,,doe,200
jane, ,doe,300

I want to load this data into a table :

create table mytable( fname char(30) not null, middle_init char(1) null, lname
char(30) not null, items int not null)

When I load the table, I want "middle_init" to be NULL for "john doe". I want
"jane doe" to have a space " " for middle_init.

Here is my load script:

set temporary option escape_character = 'on'

load table mytable ( firstname ','
, middleinit ','
, lastname ','
,items '\x0a')
from '/data2/myfile'
preview on
quotes off
escapes off
format ASCII

However, I get space for middle_init on "john doe". How can I get IQ to
interpret two adjoining column delimiters as a NULL value?

Roland

2006-02-17, 9:24 am

Add NULL(BLANKS) to the column specification (See reference
manual LOAD table statement).

Maybe set this option as well when you use varchar columns:
SET TEMPORARY OPTION NON_ANSI_NULL_VARCHA
R='ON'

> Simple issue.
>
> I have an ascii (not binary) file with 3 columns delimited
> by commas:
>
> jack,t,black,100
> john,,doe,200
> jane, ,doe,300
>
> I want to load this data into a table :
>
> create table mytable( fname char(30) not null, middle_init
> char(1) null, lname char(30) not null, items int not null)
>
> When I load the table, I want "middle_init" to be NULL for
> "john doe". I want "jane doe" to have a space " " for
> middle_init.
>
> Here is my load script:
>
> set temporary option escape_character = 'on'
>
> load table mytable ( firstname ','
> , middleinit ','
> , lastname ','
> ,items '\x0a')
> from '/data2/myfile'
> preview on
> quotes off
> escapes off
> format ASCII
>
> However, I get space for middle_init on "john doe". How
> can I get IQ to interpret two adjoining column delimiters
> as a NULL value?
>
>
>

Badri

2006-03-15, 11:24 am

Had a similar behavior with 12.6... the DBA worked with
tech. support and set the parm "Load_Asnul... " which gave a
server wide behavior similar to " SET TEMPORARY OPTION
NON_ANSI_NULL_VARCHA
R='ON'" .

This parm was not in the manuals(think).. found in the
release bulletin for solaris.
[color=darkred]
> Add NULL(BLANKS) to the column specification (See
> reference manual LOAD table statement).
>
> Maybe set this option as well when you use varchar
> columns: SET TEMPORARY OPTION NON_ANSI_NULL_VARCHA
R='ON'
>
> int not null) >

Sponsored Links