May 17, 2007 at 8:58 am
I am trying to insert 0 into target table. The source table contains NULL value and the script stops its execution when it hits NULL . I am trying to do the following
strSql11=" insert into TempItem (intId,intWebId, intDataType, intNavid ) values (" & intId & ", " & intWebId & ", " & intDataType & ", IsEmpty(" & intNavId & ",0) ) "
Also I tryed to do
CASE WHEN " & intNAVid & " is NULL THEN 0 ELSE " & intNavID & "
But neither one of the working
Web programmer
May 17, 2007 at 9:55 am
I assume that data already exists in the table so you must use and update statement not an insert statement. For example:
update t
set intNavId = 0
from TempItem t
where intNavId is null
For new entries you might want to put a default on the table of 0
Good Luck
May 17, 2007 at 9:58 am
I can not update the data. I have to copy the data as is. The only thing I can do is to change NULL value to 0
Web programmer
May 17, 2007 at 11:32 am
the solution is following
if isNULL (intNavId) or isNULL(intSubID) or isNULL (intTAbid) or isNULL(intOrder) Then
intNavId="NULL"
intSubID="NULL"
intTAbid="NULL"
intOrder="NULL"
else
intNavid= rs.Fields("intNavId").value
intSubID=rs.Fields("intSubID").value
intTAbid=rs.Fields("intTAbid").Value
intOrder=rs.Fields("intOrder").Value
end if
Dim strSql11
strSql11=" insert into TempItem (intId,intWebId, intDataType, intNavid , intSubID, intTAbid, intOrder) values (" & intId & ", " & intWebId & ", " & intDataType & ", " & intNavId & ", " & intSubID & ", " & intTAbid & ", " & intOrder & ") "
Web programmer
May 17, 2007 at 11:40 am
strSql11=" insert into TempItem (intId,intWebId, intDataType, intNavid ) values (coalesce(" & intId & ",0), coalesce(" & intWebId & ",0), coalesce(" & intDataType & ",0), coalesce(" & intNavId & ",0) ) "
May 17, 2007 at 11:45 am
MY solution is working Thanks everybody for your responses
Web programmer
May 17, 2007 at 11:45 pm
you ought to read about coalesce. it's very useful for handling nulls as it can take mupltiple args. That is:
select coalesce(col1, col2, col3, col4, ...) from mytable
will return the value of the first column listed that is not null.
---------------------------------------
elsasoft.org
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply