| Author |
 Topic  |
|
|
GavinD1977
Yak Posting Veteran
United Kingdom
83 Posts |
 Posted - 08/10/2006 : 11:14:12
|
Hi all, can anyone explain to me why i am getting this error:
Cannot insert the value NULL into column 'WORKPATTERN_END', table 'MockDownload_V52.dbo.EMPLOYEE_WORKPATTERN'; column does not allow nulls. INSERT fails.
The code i am using is:
DELETE EMPLOYEE_WORKPATTERN
INSERT INTO EMPLOYEE_WORKPATTERN(
EMPLOY_REF)
SELECT CAST(EMPLOY_REF AS VARCHAR(10))
FROM EMPLOYEE
|
|
|
spirit1
Cybernetic Yak Master
Slovenia
11741 Posts |
Posted - 08/10/2006 : 11:15:50
|
you have a null value in EMPLOY_REF column.
Go with the flow & have fun! Else fight the flow 
blog thingie: http://weblogs.sqlteam.com/mladenp |
 |
|
|
GavinD1977
Yak Posting Veteran
United Kingdom
83 Posts |
Posted - 08/10/2006 : 11:20:31
|
I had thought that myself............however when I do:
SELECT * FROM EMPLOYEE WHERE EMPLOY_REF IS NULL
I get nothing back. Also if i do:
SELECT EMPLOY_REF FROM EMPLOYEE
and manually go through the list there are no nulls shown. Also on the EMPLOYEE table is will not allow inserts of null values into the EMPLOY_REF column?????
Cheers people. |
|
|
|
Srinika
Flowing Fount of Yak Knowledge
Sri Lanka
1378 Posts |
Posted - 08/10/2006 : 11:23:49
|
If u have a null value in the data of EMPLOY_REF column which is selected, to be inserted to EMPLOYEE_WORKPATTERN, u should decide on what needs to be inserted. (Ur EMPLOYEE_WORKPATTERN's EMPLOY_REF column is created without allowing Nulls)
So u can allow nulls to that or Can use ISNULL function to change the data to whatever u want
Srinika
|
|
|
|
GavinD1977
Yak Posting Veteran
United Kingdom
83 Posts |
Posted - 08/10/2006 : 11:24:48
|
Hi.
There are definately no nulls in the EMPLOY_REF column????
Cheers. |
|
|
|
Srinika
Flowing Fount of Yak Knowledge
Sri Lanka
1378 Posts |
Posted - 08/10/2006 : 11:28:36
|
Just check :
SELECT EMPLOY_REF , CAST(EMPLOY_REF AS VARCHAR(10))
FROM EMPLOYEE where CAST(EMPLOY_REF AS VARCHAR(10)) IS NULL
Srinika
|
|
|
|
harsh_athalye
Flowing Fount of Yak Knowledge
India
5509 Posts |
Posted - 08/10/2006 : 11:44:08
|
better do this...
DELETE EMPLOYEE_WORKPATTERN
INSERT INTO EMPLOYEE_WORKPATTERN(
EMPLOY_REF)
SELECT CAST(EMPLOY_REF AS VARCHAR(10))
FROM EMPLOYEE WHERE EMPLOY_REF IS NOT NULL
Harsh Athalye
India.
"Nothing is Impossible" |
|
|
|
spirit1
Cybernetic Yak Master
Slovenia
11741 Posts |
Posted - 08/10/2006 : 11:51:05
|
try recreating an index/check constraint on EMPLOYEE_WORKPATTERN
I don't know if it's possible, but maybe it got corrupt somehow and it has nulls.
Go with the flow & have fun! Else fight the flow
blog thingie: http://weblogs.sqlteam.com/mladenp |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
USA
6997 Posts |
Posted - 08/10/2006 : 13:55:52
|
This answer looks simple to me.
You are inserting a new row, and are not including WORKPATTERN_END in the insert list. Column WORKPATTERN_END does not allow nulls and has no default value, so the insert is giving an error.
You need something like this:
INSERT INTO EMPLOYEE_WORKPATTERN
(EMPLOY_REF,
WORKPATTERN_END)
SELECT
CAST(EMPLOY_REF AS VARCHAR(10)) as EMPLOY_REF,
some-value-here AS WORKPATTERN_END
FROM
EMPLOYEE
CODO ERGO SUM |
|
|
|
Srinika
Flowing Fount of Yak Knowledge
Sri Lanka
1378 Posts |
Posted - 08/10/2006 : 14:07:26
|
quote:
Originally posted by Michael Valentine Jones
This answer looks simple to me.
You are inserting a new row, and are not including WORKPATTERN_END in the insert list. Column WORKPATTERN_END does not allow nulls and has no default value, so the insert is giving an error.
You need something like this:
INSERT INTO EMPLOYEE_WORKPATTERN
(EMPLOY_REF,
WORKPATTERN_END)
SELECT
CAST(EMPLOY_REF AS VARCHAR(10)) as EMPLOY_REF,
some-value-here AS WORKPATTERN_END
FROM
EMPLOYEE
CODO ERGO SUM
   
Srinika
|
Edited by - Srinika on 08/10/2006 14:08:03 |
|
|
|
spirit1
Cybernetic Yak Master
Slovenia
11741 Posts |
Posted - 08/10/2006 : 14:35:49
|
have to agree with Srinika 100% on this one 
Go with the flow & have fun! Else fight the flow
blog thingie: http://weblogs.sqlteam.com/mladenp |
|
|
| |
Topic |
|
Dont undertsand this error
