Hi, I use informix 9.4.

I use a file named file.data to insert data in my table myDBTable.

My file is non positional and the colomn delimiter is a '|' character.

Here is my current source code:
File file.data delimiter '|' 3;
Insert into myDBTable ( field1, field2, fieldMoney)
Values (f01, f02, f03);

I would like to have my 'fieldMoney' column f03, which is set to NOT NULL and of type money, having a '0' when I add nothing into it. For the moment, I am having an error because it converts my blank into null and cannot insert it.

I am new to this forum, hope it is working well. I greatly thank you for your help.

Hi Jacques,

The easiest way I can think of is to alter your table and add the DEFAULT clause to that specific column, for example:

CREATE TABLE test (
column1 INTEGER DEFAULT 0 NOT NULL)

This will replace with a 0 every null value that is tried to be inserted into that column.

Hope this helps

Luis Torres

First of all thank you Luis for your help!

My table already has a default value. The problem is : when you select data, like my value(f01, f02, f03) , it will put a null value if the field contains a null. It will take the default case only if you don't specify a value. The problem is that I have to specify a value because it is a script running and only in rare case that the column is null.

And when it is null...OUCH

Hi,

unfortunately the only way I can think of solving this problem is to go to the source file and change all those
<value>|<value>|| to <value>|<value>|0|.

If someone else has a better idea please go ahead, I'm also curious if it can be done via any instruction or command within informix.

Luis Torres

Another way is to load the file into a temp table that accepts nulls on the money field and then reload them to myDBTable using:

insert into myDBTable
select f1,
f2,
CASE
WHEN f3 IS NULL
THEN 0
ELSE f3
END
from Temp_Table

Hope this helps

Luis Torres

I guess it would work. I just find it a little heavy to convert null into 0
Making the || go into |0| is a good idea. The problem is that I have other fields and changing the pipes will affect all the other fields (including text fields, I don't need a 0 in them).

Wonder why it is not just like a positionnal way where you can put a value when null.

Jack

FYI

The way I did it is with a awk at the beginning. It just changes the || to | | or |0| in the case of a money field. It works!

Cool, glad everything works fine