Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2007

    Unanswered: Nullable Column Vs Spaces

    I have been trying to create a test database in DB2 V7 environment. In one table, am finding this error "The field value in a row X and column Y is missing, but the target column is not nullable." where row X and column Y correspond to a data value that is an empty space. I tried recreating the table with the NOT NULL clause in column Y removed and it loaded all rows. My problem is that the rows with no values for column Y should be recognized as spaces and not nulls, in that when I run
    select count(*) in tableZ where columnY =''
    I should get the number of all records with no values for column Y.
    There should be no nulls in any character field in my data model so that when I run the same query as below:
    select count(*) in tableZ where columnY is null
    I sholud get 0.

    How can I achieve this so that I load my database appropriately?


  2. #2
    Join Date
    Jun 2006
    create the table with not null and load at least 1 space in this column - as value is required
    this should resolve the problem
    select * from table where col=' ' (one or more spaces should return the same result)
    Best Regards, Guy Przytula
    DB2 UDB LUW certified V6/7/8

  3. #3
    Join Date
    Jan 2007
    Jena, Germany
    That doesn't make much sense to me. The empty string is a value. So if you want to indicate that no value is there, you use NULL in SQL.

    Anyway, what I think you want to do is to declare the column with DEFAULT '' to tell DB2 that whenever no value is supplied for this column in an INSERT statement, you will use the empty string as value. Since this may not cover all scenarios (IMPORT will try to insert the NULL if you don't exclude the column to begin with), add a trigger that maps a NULL to the empty string.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

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