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.

 

[SQLSister]

What value would you make the data that is null? You will have to write update statements for each individual field because the value if there is a null will probably be differnt for each one. Normally I do such things in the pre processing stage of my imports where I load to a work table then do required cleanup. After that I load to my production tables.

"NOTHING is more important in a database than integrity." ESquared

 

[r937]

what's wrong with NULL?

r937.com | rudy.ca

 

[TysonLPrice]

You would not want a blood bank to match a sample with Null blood type (not yet entered) to a patient with Null blood type (not yet identified)

 

[r937]

ah, thanks, tyson

so, if my database doesn't have blood types, NULL is okay?

r937.com | rudy.ca

 

[TysonLPrice]

Personally, as a VB programmer, I hate NULLS. There are the extra considerations for queries and the contant checking if the value isNull in the applications before trying to use them.

It kind of reminds me of my old mainframe days where a program had to be under 512K and variables where named A = A1 - (A2 + A3). Then memory got cheap and you used Net-Pay = Gross-Pay - (Deductions + Taxes)

I say memory is cheaper then the extra programming time and expense.

Two things:

1. The COBOL syntax in my example isn't even close.
2. I feel the flames from DBAs already.

 

[r937]

huh? so both memory and programmer time are saved if i use a blank or a zero-length string for blood type rather than NULL?

r937.com | rudy.ca

 

[TysonLPrice]

Heh heh...

I switched directions on you. I meant using nulls in general. I could have wrote a novel by now with all the keystrokes I've used with:

If not isnull(value) then
use it
else
set it to something usable
end if

And the time spent fixing the programs where the check wasn't there and nulls slipped in.

It would be titled "Why programmers hate nulls".

 

[r937]

so what do you do? declare all columns as NOT NULL?

what value do you use for "unknown" and "not applicable"?

what if it's a numeric column?

r937.com | rudy.ca

 

[TysonLPrice]

I'm not in control of that and nulls are the norm here. Part of it is knowing the data your working with (numeric, dates, money, etc.). I'd prefer zeros, blank, or the default date.

I once wrote a system where I controlled what could be input into the DBs. It worked just fine until I had to start getting data from a system that didn't exist at the time and nulls were used. What a headache...

I don't know what all the performance issues are like having to the insert data versus leaving it null.

I realize there are odd exceptions like a termination date of 01/01/1900 that doesn't make sense. It's half a dozen of one and six of the other. Sadly, there are many variables to consider, so there is no one-size-fits-all solution. This is why we hire DBAs.

 

[ca8msm]

I'd prefer zeros, blank, or the default date.

But any of those values could be valid entries for a record and you would have no way of determining if the value was known, or whether it was just what you entered as the default. For example, say you have a product table and fields for:

1) unit price
2) description
3) stock due date

Using your preferred approach, if a new product was added, but no stock existed you would have a unit price of 0, a blank description and a due date of when the record was added (or some other default date if you chose to go down that route). What happens if someone then finds this product listed in your application by searching on the price or due date? These values would then be incorrect as the price isn't zero (you just don't know what it will be yet) and the same applies for both of the other fields.

Or do you have some other method in mind for dealing with this type of situation?




-------------------------------------------------------

Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]
[URL unfurl="true"]http://mdssolutions.co.uk[/url] - Delivering professional ASP.NET solutions
[URL unfurl="true"]http://weblogs.asp.net/marksmith[/url]

 

[djj55]

Hello,
TysonLPrice, I solved the problem in VB early on by creating a function. As both a programmer and a DBA I see it all.

As to drallabs original question, a program would have to be created based on the columns that need updated. Query on column list or filed lists. There is no quick fix that I know of, but I am no expert. Nulls have their place but I also see their down side.

Good luck,
djj

 

[LNBruno]

I've been on both sides of this - programmer and DBA. It might be easier to insist on non-nulls, but from a business standpoint, it's just plain wrong.

Consider the following:

declare @price1 decimal(4,2)
declare @price2 decimal(4,2)
declare @price3 decimal(4,2)

set @price1 = 6.25
set @price2 = 5.50
set @price3 = 0    --instead of NULL

select (@price1+@price2+@price3) / 3 AvgPrice

--AvgPrice
--3.916666

The answer is totally wrong. It works, but if the business is basing any financials on it....

Hello, auditors!





< M!ke >
[small]Holidays cause stress. Crazy people crack under stress. Consider yourself warned.[/small]

 

[gmmastros]

Ok. So, you don't like all the extra typing.

[tt]If not isnull(value) then
use it
else
set it to something usable
end if[/tt]

You would prefer to use a default date of '1/1/1900', right. Well, what do you show a user? Do you want them to see the 'bogus date that really means (I don't know)'? For example, suppose you are collecting birthday. Also suppose that it is not a mandatory field. So, if the user does not enter a birthday, you use 1/1/1900. When another user pulls up this record, do you want to show them 1/1/1900? Probably not. So, instead, you would have code like...

[tt][blue]
If cDate(DatabaseColumnValue) = cDate("1/1/1900") Then
txtBirthday.Text = ""
Else
txtBirthday.Text = Format(DatabaseColumnValue, "Short Date")
End If
[/blue][/tt]

I don't see that as less typing. Of course, you could always take the Quick & easy approach.

txtBirthday.Text = "" & RS("Birthday")

When you concatenate a string with NULL, you end up with the string. So, if RS("Birthday") is null, the text box would be set to an empty string. If it's not null, the textbox would take the value of the recordset column's value.

So, what's my point?

Well, if you are going to all 'unknowns', it no less (and no more) work than dealing with default values that represent unknown. But, if you use NULL, there is no ambiguity. NULL is NULL. That's it.

Now, you might think that it's a good idea to not allow unknown. This is a conceptual requirement that would cause a lot of headaches with your users. Often times, databases hold certain unnecessary information (I like to call the 'Eye Color' columns). If you don't allow unknowns for these fields, you will end up getting bogus data. Your users may end up fabricating data just to satisfy the 'no unknowns' requirement.

Face it. Your stuck writing the code no matter how you look at it.

-George

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

 

[TysonLPrice]

I'm just voicing my personal preference. I know for a fact you can live without nulls and have a happy well adjusted life. Of course there are quirks. Most application programmers know what data they are working with and make the adjustemnts. I've been Googling around and I see arguments on both sides. I was ready for some "flames" when I posted

"Face it. Your stuck writing the code no matter how you look at it."

That's why we get the big bucks...

 

[ca8msm]

I'm just voicing my personal preference. I know for a fact you can live without nulls and have a happy well adjusted life

I understand it's just what you prefer, and perhaps it's just that we see potential problems that you've not needed to consider before, but you've not answered what you would do in the various scenario's that people have posted (i.e. myself, LNBruno and gmmastros), so can you explain what you would do in each of these cases? After all, I think it is a fairly unusual remark you've made (as I see many pitfalls that would otherwise be avoided) so it would be good if you could back this theory up with how you handle the problems that we've pointed out.


-------------------------------------------------------

Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]
[URL unfurl="true"]http://mdssolutions.co.uk[/url] - Delivering professional ASP.NET solutions
[URL unfurl="true"]http://weblogs.asp.net/marksmith[/url]

 

[AlexCuse]

I know someone who likes null's a bit too much

http://sqlblog.com/blogs/denis_gobo/archive/2007/10/26/3099.aspx

(No, not Denis ;-) )

[small]----signature below----[/small]
Numbers is hardly real and they never have feelings but you push too hard, even numbers got limits
Why did one straw break the camel's back? Here's the secret: the million other straws underneath it

My Crummy Web Page

 

[drallabs]

I realize there is a debate about the validity of whether or not nulls should be used. There are valid debates to support either argument. My original question has nothing to do with the debate on nulls. I have already figured that out, now I want to get rid of them. Is there a way, specific examples please no vague responses, on how I could efficiently remove nulls from a lot of fields without using the update statement and a ton of typing. Thanks.

 

[ca8msm]

The only way I can think of doing this in one go is to use dynamic sql. You would have to use the information schema to get a list of table and column (including it's type) and then update the column to whatever you want for that type. Overall, it's a bad idea in my opinion but there you go.


-------------------------------------------------------

Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]
[URL unfurl="true"]http://mdssolutions.co.uk[/url] - Delivering professional ASP.NET solutions
[URL unfurl="true"]http://weblogs.asp.net/marksmith[/url]

 

[SQLSister]

Since the value you might want to replace null with should be defined separately for each column, the safest way to do this would be to create the update statements. Plus dynamic SQl is a very bad habit to get into and should never be used to save a little typing. If you tell us what you ant to replace null with we could be more specific. But that was my orginal question before the whole debate started.

"NOTHING is more important in a database than integrity." ESquared