Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2014
    Posts
    294

    Unanswered: Problem on INSERT due to NULLS

    Hi,

    I am trying to insert few values into a table it is throwing some error,as unable to insert NULL values to a not null columns.
    This is my query

    INSERT INTO M105(SELECT A.SERVER,A.CREATOR,A.TABLE_NAME,A.COLUMN_NAME,A.CO LTYPE,A.NULLS,A.LENGTH,A.IDENTITY ,B.TABLE_COUNT
    FROM M_DATA A full OUTER JOIN M102 B ON A.TABLE_NAME=B.TABLE_NAME);

    error:
    Assignment of a NULL value to a NOT NULL column "TBSPACEID=2, TABLEID=20, COLNO=0" is not allowed.. SQLCODE=-407, SQLSTATE=23502, DRIVER=3.67.28


    I found the column name from the query as

    select colname,colno from syscat.columns where tabname='MENTIS_META_DATA' and COLNO=0;

    The column name is SERVER here from my table M_DATA.

    description of my table M_DATA is:

    Column name schema Data type name Length Scale Nulls
    ------------------------------- --------- ------------------- ---------- ----- ------
    SERVER SYSIBM CHARACTER 6 0 No
    CREATOR SYSIBM VARCHAR 128 0 No
    TABLE_NAME SYSIBM VARCHAR 128 0 No
    COLUMN_NAME SYSIBM VARCHAR 128 0 No
    COLTYPE SYSIBM CHARACTER 8 0 No
    NULLS SYSIBM CHARACTER 1 0 No
    LENGTH SYSIBM SMALLINT 2 0 No
    IDENTITY SYSIBM CHARACTER 1 0 No



    Kindly help me how to resolve this.

    thanks in advance

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Which part of "Assignment of a NULL value to a NOT NULL column" seems unclear?
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    All of the column in that table are defined in a way that prohibits NULL values. You will need to provide values that isn't NULL for those columns.

    Based on the syntax you provided, the problem is more likely to be in M105 than in M_DATA. Unless there is a trigger of some sort, you are only getting data from M_DATA and that error message only applies to the table where you are putting new data.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  4. #4
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    Actually Pat, I thought he was getting nulls due to the full outer join with M102
    Dave

Tags for this Thread

Posting Permissions

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