ISNull giving problems 1

[UnsolvedCoding]

I am running into problems with the following line of code. I don't understand why, its a direct copy from a previous version of the same code and worked well for sometime.

I pulled the batches from the table this is using and there are nulls present.

This is the code -
where isnull(batch,'''') <> ''''

This is the error -
Syntax error converting the varchar value ''' to a column of data type int.

 

[gmmastros]

What is the data type for the batch column?

Run this and post the output.

Select Data_Type 
From   information_schema.columns 
where  table_Name = 'YourTableNameHere' 
       and column_name = 'batch'

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[markros]

Use single quotes, e.g.

WHERE ISNULL(Batch,'') <> ''

PluralSight Learning Library

 

[UnsolvedCoding]

It shows as INT and is inside a dynamic query.

 

[gmmastros]

Since it is an int, you should probably change the code to:

[tt]
Where batch is not null
[/tt]

With the code the way you had it, there was a lot of inefficiencies, and clearly an error.

You see, IsNull returns the value of the first parameter if the value is NOT NULL, otherwise it returns the value of the 2nd parameter.

You must also consider the fact that SQL Server will automatically do data type conversions for you. The IsNull function can take any data types as parameters, but will only return a single data type. If both parameters are integers, the output is integer. If both parameters as varchar, the output is varchar. If the data types are different, IsNull is [google]SQL Server Data Type Precedence[/google] to determine the output data type. In this case, you have an integer and a hard coded string. The integer has a higher precedence so the string will be converted to an integer. SQL Server will convert an empty string to the integer value 0. As you can see from this:

Select Convert(int, '')

So, when you have:

[tt]Where IsNull(IntegerColumn, '') <> ''[/tt]

This is exactly the same as:

[tt]Where IsNull(IntegerColumn, 0) <> 0[/tt]

Now take a close look at what is going on here. Clearly we have a where clause which is filtering out the rows to return. The only rows we will get back from the query are rows with an actual value and that actual value is not 0.

The interesting thing is that the code shown above is equivalent to:

[tt]Where IntegerColumn <> 0[/tt]

They are equivalent because NULL <> 0 so you will still get the same result set.

If you don't want to remove the rows that have a value of 0, then you should use this:

Where  Batch Is Not NULL

If you also want to remove the rows where the value of batch is 0, then use this:

Where  Batch <> 0

Let's think about the original code again but with the context of strings.

[tt]Where IsNull(VarCharColumn, '') <> ''[/tt]

Apparently, the intent is to only return rows with actual values. The same can be accomplished with this:

[tt]Where VarCharColumn > ''[/tt]



-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gmmastros]

In my last post, I said:

If the data types are different, IsNull uses SQL Server Data Type Precedence to determine the output data type.

This was wrong. Coalesce uses data type precedence. IsNull will convert the second argument to the data type of the first argument. Basically, the output data type if IsNull is always the same as the first argument.

In this particular case, the first parameter is an integer, so IsNull returns a integer. This is the compared to a string. SQL Server will use data type precedence to convert the string to an integer for comparison purposes.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[UnsolvedCoding]

Thanks, I have been trying to figure out why that particular piece of code was in there. The only thing I can figure out is the original programmer wanted to put in blanks where the word null existed before.

 

[SQLScholar]

UnsolvedCoding,

Sorry to butt in.

where the word null existed before

In database terms NULL is not a word. The best way i have heard it described (my favourite) is that it is an unknown value. Its not blank, its not the word null - it is unknown. NULL does not have a length (its not 0), NULL cannot be searched and cannot be equal to anything (including NULL). The only thing you can ask is is it NULL.

So for instance you cant say

where FIELD = "NULL"
or
Where FIELD = NULL

but you have to say

where FIELD is NULL

What you had was a function which tests for null an if its null puts something else. So in your case if the field is null then put blank.

I dont mean to pick - its just i hope to have given you a little insite to the workings of what you had. I know now people may come back saying my definiton is incorrect, but thats the way i explain it.

here is the microsoft defintion. Take a look if your interested:

http://msdn.microsoft.com/en-us/library/ms191504.aspx

Take care.

Dan

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

http://www.fountain.me.uk

Be who you are and say what you feel, because those who mind don't matter and those who matter don't mind - Bernard Baruch

Computer Science is no more about computers than astronomy is about telescopes - EW Dijkstra
----------------------------------------

 

[SQLScholar]

additonally what caused your problem was that originally the field was probably a varchar. So if its null then replace with blank. Which works fine.

However at some point it was change to an integer. So if its null then replace with an blank will fail because blank is not a valid integer.

HTH

Dan

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

http://www.fountain.me.uk

Be who you are and say what you feel, because those who mind don't matter and those who matter don't mind - Bernard Baruch

Computer Science is no more about computers than astronomy is about telescopes - EW Dijkstra
----------------------------------------

 

[gmmastros]

I know now people may come back saying my definiton is incorrect, but thats the way i explain it.

I usually say the null means "I don't know".

Imagine you have a table named People, with a column named "ShoeSize". Now suppose that you don't know what the ShoeSize is for some people, so there are NULL's in the data.

Should a person with a NULL (I Don't Know) ShoeSize be returned if you:

Select * From People Where ShoeSize = 7

Since you don't know if the ShoeSize is 7, you cannot include the row. But... What about this?

Select * From People Where ShoeSize [!]<>[/!] 7

Again, since you don't know the ShoeSize you cannot say that it is NOT 7 either.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom