Cannot insert the value NULL into column

15 Sep 2005 6:18 PM

Jeff Metcalf

I have a view - vw_dispatch_owner

SELECT DISTINCT CALLNBR, USERID, MIN(CREATDDT) AS C_date, MIN(CREATETIME) AS
C_Time, FRMSTAT
FROM         dbo.SVC00210
WHERE     (FRMSTAT = '')
GROUP BY CALLNBR, USERID, FRMSTAT
HAVING      (USERID IS NOT NULL)

that I am trying to use to update a matching record. And even though the
view is created to filter out nulls, I am still getting the error

Cannot insert the value NULL into column 'USERDEF1', table
'CPLU.dbo.SVC00200'; column does not allow nulls. UPDATE fails.
The statement has been terminated.

the update statement is:

update svc00200
set userdef1=(select userid from vw_dispatch_owner
where svc00200.callnbr=vw_dispatch_owner.callnbr and userid is not null)
where userdef1=''

Userdef1 in svc00200 is setup to not allow nulls (and I can't change it).
However, when I run a select statement against the view, there are no null
values in the userid field.

Any help is appreciated.

15 Sep 2005 6:29 PM

Alejandro Mesa

Jeff,

Put that filter in the where clause.

> HAVING (USERID IS NOT NULL)

....
where (USERID IS NOT NULL)
....

AMB

Show quote

"Jeff Metcalf" wrote:

> I have a view - vw_dispatch_owner
>
> SELECT DISTINCT CALLNBR, USERID, MIN(CREATDDT) AS C_date, MIN(CREATETIME) AS
> C_Time, FRMSTAT
> FROM         dbo.SVC00210
> WHERE     (FRMSTAT = '')
> GROUP BY CALLNBR, USERID, FRMSTAT
> HAVING      (USERID IS NOT NULL)
>
> that I am trying to use to update a matching record. And even though the
> view is created to filter out nulls, I am still getting the error
>
> Cannot insert the value NULL into column 'USERDEF1', table
> 'CPLU.dbo.SVC00200'; column does not allow nulls. UPDATE fails.
> The statement has been terminated.
>
> the update statement is:
>
> update svc00200
> set userdef1=(select userid from vw_dispatch_owner
> where svc00200.callnbr=vw_dispatch_owner.callnbr and userid is not null)
> where userdef1=''
>
> Userdef1 in svc00200 is setup to not allow nulls (and I can't change it).
> However, when I run a select statement against the view, there are no null
> values in the userid field.
>
> Any help is appreciated.

15 Sep 2005 6:35 PM

John Bell

Hi

I am not sure why you have excluded nulls in the having clause instead of
the where clause?

On your update if
select userid
from vw_dispatch_owner
where svc00200.callnbr=vw_dispatch_owner.callnbr
and userid is not null

does not return anything then it will try be NULL used to update the value

Maybe you want something like:

update s
set userdef1=v.userid
FROM svc00200 s
JOIN vw_dispatch_owner v on s.callnbr=v.callnbr and v.userid is not null
where s.userdef1=''

John

Show quote

"Jeff Metcalf" <JeffMetc***@discussions.microsoft.com> wrote in message
news:95E1075B-7F52-4977-B6D1-0D370D7B0876@microsoft.com...
>I have a view - vw_dispatch_owner
>
> SELECT DISTINCT CALLNBR, USERID, MIN(CREATDDT) AS C_date, MIN(CREATETIME)
> AS
> C_Time, FRMSTAT
> FROM         dbo.SVC00210
> WHERE     (FRMSTAT = '')
> GROUP BY CALLNBR, USERID, FRMSTAT
> HAVING      (USERID IS NOT NULL)
>
> that I am trying to use to update a matching record. And even though the
> view is created to filter out nulls, I am still getting the error
>
> Cannot insert the value NULL into column 'USERDEF1', table
> 'CPLU.dbo.SVC00200'; column does not allow nulls. UPDATE fails.
> The statement has been terminated.
>
> the update statement is:
>
> update svc00200
> set userdef1=(select userid from vw_dispatch_owner
> where svc00200.callnbr=vw_dispatch_owner.callnbr and userid is not null)
> where userdef1=''
>
> Userdef1 in svc00200 is setup to not allow nulls (and I can't change it).
> However, when I run a select statement against the view, there are no null
> values in the userid field.
>
> Any help is appreciated.

15 Sep 2005 6:43 PM

Alexander Kuznetsov

update svc00200
set userdef1=(select userid from vw_dispatch_owner
where svc00200.callnbr=vw_dispatch_owner.callnbr and userid is not
null)
where userdef1=''
--------- add this:
AND EXISTS(select userid from vw_dispatch_owner
where svc00200.callnbr=vw_dispatch_owner.callnbr and userid is not
null)

15 Sep 2005 7:01 PM

David Portas

If there isn't a matching callnbr in the view then the subquery will
return NULL. Try this:

UPDATE svc00200
SET userdef1 =
(SELECT userid
   FROM vw_dispatch_owner
   WHERE svc00200.callnbr=vw_dispatch_owner.callnbr
    AND userid IS NOT NULL)
WHERE userdef1=''
AND EXISTS
(SELECT *
   FROM vw_dispatch_owner
   WHERE svc00200.callnbr=vw_dispatch_owner.callnbr
    AND userid IS NOT NULL) ;

--
David Portas
SQL Server MVP
--

15 Sep 2005 7:24 PM

Jeff Metcalf

thx gang. Got it to work

Show quote

"Jeff Metcalf" wrote:

> I have a view - vw_dispatch_owner
>
> SELECT DISTINCT CALLNBR, USERID, MIN(CREATDDT) AS C_date, MIN(CREATETIME) AS
> C_Time, FRMSTAT
> FROM         dbo.SVC00210
> WHERE     (FRMSTAT = '')
> GROUP BY CALLNBR, USERID, FRMSTAT
> HAVING      (USERID IS NOT NULL)
>
> that I am trying to use to update a matching record. And even though the
> view is created to filter out nulls, I am still getting the error
>
> Cannot insert the value NULL into column 'USERDEF1', table
> 'CPLU.dbo.SVC00200'; column does not allow nulls. UPDATE fails.
> The statement has been terminated.
>
> the update statement is:
>
> update svc00200
> set userdef1=(select userid from vw_dispatch_owner
> where svc00200.callnbr=vw_dispatch_owner.callnbr and userid is not null)
> where userdef1=''
>
> Userdef1 in svc00200 is setup to not allow nulls (and I can't change it).
> However, when I run a select statement against the view, there are no null
> values in the userid field.
>
> Any help is appreciated.