Null 2

[drallabs]

I just received a database full of tables from an outside source. These tables have multiple fields in each of them. My question is this database allowed nulls in all the fields. Is there a way/script that I can run that would automatically convert the nulls to not nulls. I would like to avoid having to manually convert all this data.

 

[TysonLPrice]

Yes VB 6.

"Invalid use of Nulls" when referencing them such as:

strClaim = RsTempRecordset.Fields("Claim")

It needs to be:

If not isnull(RsTempRecordset.Fields("Claim") then
strClaim = RsTempRecordset.Fields("Claim")
else
strClaim = ""
end if

 

[gmmastros]

Yes VB 6.

"Invalid use of Nulls" when referencing them such as:

strClaim = RsTempRecordset.Fields("Claim")

It needs to be:

If not isnull(RsTempRecordset.Fields("Claim") then
strClaim = RsTempRecordset.Fields("Claim")
else
strClaim = ""
end if

Or...

strClaim = [!]"" & [/!]RsTempRecordset.Fields("Claim")

intSomeNum = val([!]"" & [/!]RS.Fields("SomeNumericColumn"))

In VB6, when you concatenate an empty string will a NULL, the result is an empty string. I've done this about a million times and have never had a problem with it.


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[jbenson001]

Ok, thanks for the info.

 

[TysonLPrice]

strClaim = "" & RsTempRecordset.Fields("Claim")
intSomeNum = val("" & RS.Fields("SomeNumericColumn"))

Nice tip...In my example I generally wouldn't just set strClaim to "". It would, depending on the case:

"N/A"
"Not on file"
"etc."

I referring mostly to reporting though.

Thanks for all the input!

 

[ESquared]

If you want to have specific values that MEAN something and won't be decoded in the application by complex logic operating on context data, then design the database better. Sometimes I do use meta-values in the data such as 0 = not chosen, -1 = something else (often with these values in the lookup table, for example, but that has its own problems). But not always. One has to use experience and a keen mind in these situations to choose what will be the simplest, easiest to use & understand, and still maintain data integrity.

Okay, so you can't design the database better if you can't change the database. If you transform NULL into some value that one is "supposed" to interpret a certain way but otherwise doesn't mean what it looks like (e.g., 1/1/1900 means something and it's not the date January 1, 1900), then unless you have absolutely complete control over how the data is used, you're potentially introducing data quirks that could have disastrous consequences.

VB6 CAN accept Nulls in the Variant data type. There's nothing wrong with using Variants in most GUI situations. Tight loops that are executed thousands of times? Okay use a typed variable. But when working with columns from databases, just use variants. Don't taint your data by losing the "extra value" that NULL provides you. Now you're just risking someone somewhere getting confused. (E.g., does 0 mean that's the price or does it have no price? Heck, who knows? At least a null will be clear you didn't mean it was free.)

You can also use the built-in function Nz() if you want something more explicit than the null-defeating concatenation operator:

[tt]strClaim = Nz(RsTempRecordset.Fields("Claim"), "Not on file")
intSomeNum = Nz(RS.Fields("SomeNumericColumn")), 0)[/tt]

If that function is not enough for you, write your own function that encapsulates business logic and use the dratted thing.

In fact, now that I think about it, I suggest that you not go around interpreting the NULL in the middle of code that does something else. This isn't just about code re-use but also about good practices. When you need to modify or examine how nulls are handled, you shouldn't have to find it buried in some routine somewhere. It belongs in a data business logic module.

I've been learning about Agile Development and any desire for unit testing capability makes this a clear and easy requirement. How can you test your null logic if the only place it lives is in the middle of something else? And if you're not testing your code, well... go read up on Agile if you want to know what it says about not doing test-driven-development. (Yes, I'm a recent convert.)

P.S. The concatenation operator & converts to string and defeats nulls. But you can also concatenate strings using the + operator which propagates nulls. It may not be best practice since not everyone is aware of this, but you can exploit this really elegantly:

[tt]strName = LastName & (", " + FirstName)[/tt]

Be careful when doing implicit type conversion with +. Do you know off the top of your head what result and data type you'll get with this:

[tt]varResult = "1" + 1[/tt]

Is it "11" or 2?

 

[SQLDenis]

So E2, agile ha, you doing Scrums and planning poker yet?

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[ESquared]

no real team and no management buy-in = no scrums, no poker. But I can still learn what I can and make good use of it!

 

[TysonLPrice]

You can also use the built-in function Nz() if you want something more explicit than the null-defeating concatenation operator:"

I'm missing something here. Nz flags "Sub or Function not defined".

I guess if you are developing applications from the ground up many of these suggestions can be thought out and implemented. When you come into a shop that has dozens of large applications consisting of literally millions of lines of code and set ways of doing things you live with what you have. I usually don't have the luxury of time to re-write things.

I know that doesn't sound very good but it's a fact of life. My company was bought out by a larger one and the legacy systems are going away. Management doesn't see much point in improving something that is being trashed.

I'm bailing out of this thread now

Thanks all!

 

[ESquared]

You're right, there isn't much point in improving something that's being trashed.