INsert 0 instead of NULL
-
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
Kindest Regards,Web programmer
-
Try:
strSql11=" insert into TempItem (intId,intWebId, intDataType, intNavid ) values (" & intId & ", " & intWebId & ", " & intDataType & ", COALESCE(" & intNavId & ",0) ) "
-
I would use the isnull function which checks to see if the value is null and lets you specify the replacement value.
strSql11=" insert into TempItem (intId,intWebId, intDataType, intNavid ) values (" & intId & ", " & intWebId & ", " & intDataType & ", isnull(intNavID,0) )
-
ISNULL will work just as well, but it is specific to SQL.
-
If you have access to the tables, I would change the default for that field to be 0 - and stop it from allowing nulls - hence fixing the problem without needing this workaround.
You will first need to update all nulls to 0 otherwise your table changes wont be able to be saved.
Hope that helps,
Kindest Regards,Catherine Eibner
cybner.com.au -
Hate to say it Catherine but that is probably a really bad suggestion. Removing the ability to accept NULL as a value can DRASTICALLY affect the logic of the data and result in unintended information extraction out of said data. No one should do this without a VERY thorough review if their system, design and applications.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service -
Catherine:
0 is not the same thing as NULL. 0 means 0. NULL means UNKNOWN.
I have two boxes. one box contains nothing with certainty. The other has unknown contents. It may be nothing, it may be a diamond ring worth $100k, it may be a dog turd.
which box would you rather have? Can you see there's a difference?
---------------------------------------
elsasoft.org -
I would go with Matt's suggestions as COALESCE is the ANSI preferred method as opposed to ISNULL.
-
The problem is already solved Thanks to everybody who participated in the discussion
if isNULL(intOrder) Then
intOrder="NULL"
else
intOrder=rs.Fields("intOrder").value
end if
This will insert NULL into the table. I already tested this and it works like a charm
Kindest Regards,Web programmer
-
Fair enough in alot of cases. NULL is a valid field content - but in some circumstances I think that Null is allowed in fields that shouldnt warrant it - and if you continually find yourself doing all sorts of weird queries to return another default instead of NULL - then perhaps NULL shouldnt exist in the table at all?? If that is the case - setup the defaults on the table definition and do not allow nulls in the field. This way the business layer and Stored procs etc dont need to have complex/confusing select statements to get around the NULL. If you do want NULL to exist as a valid field in the table - then ignore everything I have said!
Catherine
Kindest Regards,Catherine Eibner
cybner.com.au -
I personally DESPISE having NULLs in a database, and try to always design them out if possible!
Sometimes you just gotta have them tho - and then deal with the wicked trinary logic they bring along with them.Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply