Skip to Content
0
May 06, 2016 at 03:00 PM

behaviour of default with load table command in IQ 16.

325 Views

Default value on primary key column with varchar data type that's where it is failing.

Also if I drop primary key, it inserts null value instead of default value. even though defaults on table is defined and load table contains defaults on.

If I convert it to Char then it inserts all spaces, but doesn't insert default value.

create table testa ( a int, b varchar(5) default 'Def', c varchar(10), constraint PK_testa primary key ( a,b) );

load table testa ( a '|', b '|', c '\x0a' ) from 'testa.dat'

with checkpoint on

quotes off

escapes off

defaults on ;

-- Could not execute statement.

-- Column 'b' in table 'testa' cannot be NULL

-- SQLCODE=-195, ODBC 3 State="23000"

data

1|0|1

2||1

3|1|1

4||1

5||1

6|2|1

7||1

8|2|1

9||1

10|2|1

11||1

12||1

13|2|1

14||1

15|2|1

16||1

Message was edited by: Salim Vahora

I try to specify default in load script with delimiter, it doesn't work.

load table testa (

a '|',

b '|' default 'Efg',

c '\x0a' )

from 'testa.dat'

with checkpoint on

quotes off

escapes off

defaults on ;

-- Could not execute statement.

-- Syntax error near 'default' on line 3

-- SQLCODE=-131, ODBC 3 State="42000"

If I put delimited by and assign default,

load table testa (

a , b default 'Efg', c '\x0a' )

from 'testa.dat'

with checkpoint on

quotes off

escapes off

defaults on delimited by '|' ;

it totally skipped field from file and added it to next field.

I don't know it is by design to insert as just some constant value with defaults?.

a b c

----------- ----- ----------

1 Efg 0|1

2 Efg |1

3 Efg 1|1

4 Efg |1

5 Efg |1

6 Efg 2|1

7 Efg |1

8 Efg 2|1

9 Efg |1

10 Efg 2|1

11 Efg |1

12 Efg |1

13 Efg 2|1

14 Efg |1

15 Efg 2|1

16 Efg |1

(16 rows affected)