locked
Field somefield does not accept null values. RRS feed

  • Question

  • Hi;

    I have 2 applications which just recently began producing this error message in various places including my error trapping routine..
    I must have done something to affect this but I'm not sure what could have caused it.
    I understand that set null command affects only sql commands - I'm getting this error in other places as well.

    I might add that set null is actually ON when I get this error.

    Any idea what could be the cause?

    Thanks.
    Monday, July 21, 2008 9:25 PM

Answers



  • This problem occurs when you try to insert NULL value in a field which does not accept null values.

    (Mostly the case when you use joins to gather data from multiple tables and try to insert the resulted data into some other table or cursor.)

    so you need to make changes in table's/cursor's structure to make these fields to accept null values.

    or second option is to insert empty values to those fields who have null values.




    Tuesday, July 22, 2008 4:39 AM
  • The error indicates that you are trying to insert a value into a column that does not accept NULL.

     

    The setting of SET NULL has nothing to do with this as it affects only how tables are created (or modified) and whether or not a SQL INSERT will insert a NULL when a column name is NOT specified. It does not do anything if a column name is

    specified in an INSERT and the value is NULL but the target table does not support NULLs - which is probably what is happening here.

     

    You get NULL values in result sets when you use OUTER JOINS in queries and matching records are missing. If the data from such a result set is then used to update a table that does not allow NULL values then you will get this error. The solution is always to use the NVL() function to ensure that NULLs do not propagate beyond the result set.

     

    This may, of course, indicate that you have errors in your data Iif there are supposed to be matching records that are missing) so you should probablycheck for that too.

    Tuesday, July 22, 2008 9:42 AM

All replies



  • This problem occurs when you try to insert NULL value in a field which does not accept null values.

    (Mostly the case when you use joins to gather data from multiple tables and try to insert the resulted data into some other table or cursor.)

    so you need to make changes in table's/cursor's structure to make these fields to accept null values.

    or second option is to insert empty values to those fields who have null values.




    Tuesday, July 22, 2008 4:39 AM
  • The error indicates that you are trying to insert a value into a column that does not accept NULL.

     

    The setting of SET NULL has nothing to do with this as it affects only how tables are created (or modified) and whether or not a SQL INSERT will insert a NULL when a column name is NOT specified. It does not do anything if a column name is

    specified in an INSERT and the value is NULL but the target table does not support NULLs - which is probably what is happening here.

     

    You get NULL values in result sets when you use OUTER JOINS in queries and matching records are missing. If the data from such a result set is then used to update a table that does not allow NULL values then you will get this error. The solution is always to use the NVL() function to ensure that NULLs do not propagate beyond the result set.

     

    This may, of course, indicate that you have errors in your data Iif there are supposed to be matching records that are missing) so you should probablycheck for that too.

    Tuesday, July 22, 2008 9:42 AM
  • OK - let me make this simpler:

    The command is 'append from somefile'

    This wasn't generating an error. Now it does. Yes, I solved the problem by setting the fields in the file to null but I just don't understand what could have caused an application that was running for 2 years to suddenly start generating this error. This had to be something in my code. And again, the errors are not just in this command or this file they even happen in my error trapping routine.
    I also looked at versions of these tables from a year ago and non had null enabled.
    I'm just wondering what could cause this error to sudenly start poping all over the place.

    Thanks.
    Monday, July 28, 2008 9:27 PM
  • We have the same problem

    Everything worked fine, but since friday we have the same error

    We didn't change our code

    Has anyone an idea?

     

    Monday, October 27, 2008 9:29 AM