Results 1 to 8 of 8
  1. #1
    Join Date
    Mar 2005
    Posts
    7

    Unanswered: Informix - NULL VALUE insertion, need HELP!

    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.

  2. #2
    Join Date
    Aug 2004
    Posts
    99
    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

  3. #3
    Join Date
    Mar 2005
    Posts
    7
    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

  4. #4
    Join Date
    Aug 2004
    Posts
    99
    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

  5. #5
    Join Date
    Aug 2004
    Posts
    99
    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

  6. #6
    Join Date
    Mar 2005
    Posts
    7
    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

  7. #7
    Join Date
    Mar 2005
    Posts
    7
    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!

  8. #8
    Join Date
    Aug 2004
    Posts
    99
    Cool, glad everything works fine

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •