July 21, 2011 at 5:47 am
declare @A1 smallint
declare @A2 smallint
declare @B1 char(1) --could be null
declare @b2 char(1) --could be null
declare @C1 char(1)
declare @c2 char(1)
select @A1 = 1
select @A2 = 1
select @B1 = 'x'
select @b2 = null
select @C1 = 'y'
select @c2 = 'y'
select
case
when
@A1 = @A2 and
@B1 = @b2 and
@C1 = @c2
then 'True'
else 'False'
end
--Ans: False
select @A1 = 1
select @A2 = 1
select @B1 = null
select @b2 = null
select @C1 = 'y'
select @c2 = 'y'
select
case
when
@A1 = @A2 and
@B1 = @b2 and
@C1 = @c2
then 'True'
else 'False'
end
--Ans: False
The logic would normally be in a where clause and A and B would be tables joined on a key with 1, 2, and 3 as non-key coulmns. The first answer of False is ok, but in the second case when B1 and B2 are both null I want the answer to be True, i.e. yes, they both have missing values.
I could use coalesce to convert the nulls to a default and this will give me the answer True:
select
case
when
@A1 = @A2 and
coalesce(@B1, 'N/A') = coalesce(@B2, 'N/A') and
@C1 = @c2
then 'True'
else 'False'
end
Then I'm thinking why not use coalsece everywhere just in case any of the non-key columns are nullable, or never allow nulls in the first place. Is this right or am I missing something really important here.
July 21, 2011 at 6:33 am
zapouk (7/21/2011)
... but in the second case when B1 and B2 are both null I want the answer to be True, i.e. yes, they both have missing values.I could use coalesce to convert the nulls to a default and this will give me the answer True:
select
case
when
@A1 = @A2 and
coalesce(@B1, 'N/A') = coalesce(@B2, 'N/A') and
@C1 = @c2
then 'True'
else 'False'
end
Then I'm thinking why not use coalsece everywhere just in case any of the non-key columns are nullable, or never allow nulls in the first place. Is this right or am I missing something really important here.
Your findings are spot on !
Handling "I don't know"s is the real question.
Avoiding NULLs in the first place, should be aimed for. Creating tables with as much columns not null as possible, for many object creators is considered to be pure overhead, until they get bitten by their own negligence.
One of the top n guidelines with any RDBMS is "tell your system what you know". With data modeling that means tell your system the value of your column must always be provided or should have a default value.
With sqlserver you can use ANSI settings to the change behavior of null evaluation, but I don't advise to play around with it. It is better for everyone to just not have to pose the question which ANSI setting is used with a certain object or in a certain process. It will generate a bunch of overhead and may cost valuable time in case of emergency situations.
There are a number of NULL related articles at SSC.
My helper line is always: "does it matter to your data system if a column has a datatype related blank / zero if the actual value is unknown ? In other words: When handling the data does it make a difference for your data system if this specific column is null or blank ? ( for a (var)char column)" If the answer is "no" then define the column as "not null" !! Your data system will gain in the long run, because of the not-unknown values ! ( twisted with unknown on purpose, because that is the whole issue ! )
One remark: with object relationships, even non-nullable columns can become nullable because of outer joins (left/right/full) with non-matching on-clause conditions.
One can avoid nulls, but one cannot exterminate them.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
July 21, 2011 at 6:57 am
Thanks for that. Agree about avoiding null columns if at all possible - something I know but then didn't do and got caught out!
A default value is relatively easy to come up with for a char column (e.g. 'N/A') but what about a numeric one? Depends on the business, but zero very unlikely to be any good, so left with something like -999999? And what about datetime, '01/01/1900' I guess?
No defaults would fix the problem in my post, but sometimes you'd have:
where ColB <> 'N/A'
where ColC <> -999999
where ColD <> '01/01/1900'
for different column data types
whereas before it would always be:
where ColA is not null
irrespective of data type for ColA
July 21, 2011 at 8:51 am
zapouk (7/21/2011)
....
declare @B1 char(1) --could be null
declare @b2 char(1) --could be null
....
select @B1 = 'x'
select @b2 = null
....
@B1 = @b2
....
select @B1 = null
select @b2 = null
...
@B1 = @b2
--Ans: False
The first answer of False is ok, but in the second case when B1 and B2 are both null I want the answer to be True, i.e. yes, they both have missing values.
Logic is definitely the problem here. NULL means Unknown. It doesn't equal anything. So if both @B1 and @b2 are capable of being NULL, then you CANNOT use the @B1 = @b2 statement because it won't be true unless both variables have an assigned value. NULL is NOT equal to NULL. It doesn't happen. You cannot make a computer understand that Unknown equals Unknown because there's no way of telling what either unknown value is.
So the best way to handle the logic is to convert the NULL to a known value and compare on that.
A lot of people will tell you not to use functions in a WHERE clause. Unfortunately, it's not always avoidable. So here's my little trick. I convert using the ISNULL() function and set the value to a blank value, which is comparable.
[Code]
ISNULL(@B1,'') = ISNULL(@B2,'')
[/code]
It's that simple. Now you have two known values (blank) that you can compare to find your true or false values.
July 21, 2011 at 9:30 am
Brandie Tarvin (7/21/2011)
Logic is definitely the problem here. NULL means Unknown. It doesn't equal anything. So if both @B1 and @b2 are capable of being NULL, then you CANNOT use the @B1 = @b2 statement because it won't be true unless both variables have an assigned value. NULL is NOT equal to NULL. It doesn't happen. You cannot make a computer understand that Unknown equals Unknown because there's no way of telling what either unknown value is.So the best way to handle the logic is to convert the NULL to a known value and compare on that.
A lot of people will tell you not to use functions in a WHERE clause. Unfortunately, it's not always avoidable. So here's my little trick. I convert using the ISNULL() function and set the value to a blank value, which is comparable.
[Code]
ISNULL(@B1,'') = ISNULL(@B2,'')
[/code]
It's that simple. Now you have two known values (blank) that you can compare to find your true or false values.
Brandie,
I have been very wary of the ISNULL code you are using here, for this example it might be ok, but I have gotten bitten by it before..
Case in point, synchornizing data between databases on two seperate instances, the field is NULLable and once side is changed to be '' (blank) and the other is still NULL, when using that logic, barring any other difference in the record it would never update it if the comparison was done with ISNULL( localfield, '' ) != ISNULL( remotefieldvalue, '' ) because it would consider them the same.
I'm not saying never do this, just be aware of the possible effects.
CEWII
July 21, 2011 at 10:13 am
Yes, I'm aware of that issue. I should have been more clear in my earlier reply.
When using ISNULL() this way, you need to make sure you're using a new value that isn't used in your database(s). It's the only way the substitution will work properly.
My workplace doesn't use blank-only values in our dbs, so we haven't had a problem with substituting blank spaces.
July 21, 2011 at 10:34 am
It does. Just so long as you're aware of your particular needs and code it so that it doesn't use an existing value, you should be able to use it fine.
July 21, 2011 at 10:58 am
Is that really what you are going to be doing with your code?
July 21, 2011 at 11:10 am
Of course not, I just had no idea what it would do. There's nothing special about isnull(@A, ''), you're replacing a null with a value for comparison because you didn't have a default for the column.
You must be able to guarentee it's not going to appear in the column - empty string, 'N/A' etc. If you want to do this with a numeric column it's the same problem and you'll need something like isnull(@A, 999999).
July 21, 2011 at 11:18 am
Ah, okay. I thought your post before last was a "I still need help" kind of post. That's why I asked if you were going to do that in your code. If you were, then I would be researching a different solution for you.
July 21, 2011 at 11:49 am
Yes, I got caught out with nulls (not for the first time!) and started thinking about it. Maybe it's better to avoid isnull or coalesce and use:
@A1 = @A2 or @A1 is null and @A2 is null
for comparisons on null columns (if this is the logic you want). The problem is you've got to remember these are nullable columns...
July 22, 2011 at 4:30 am
declare @A1 smallint
declare @A2 smallint
declare @B1 char(1) --could be null
declare @b2 char(1) --could be null
declare @C1 char(1)
declare @c2 char(1)
select @A1 = 1
select @A2 = 1
select @B1 = 'x'
select @b2 = null
select @C1 = 'y'
select @c2 = 'y'
select
case
when
(@A1 = @A2 OR(COUNT(@A1)+COUNT(@A2))=0 ) and
(@B1 = @b2 OR(COUNT(@B1)+COUNT(@B2))=0 ) and
(@C1 = @c2 OR(COUNT(@C1)+COUNT(@C2))=0 )
then 'True'
else 'False'
end
--Ans: False
select @A1 = 1
select @A2 = 1
select @B1 = null
select @b2 = null
select @C1 = 'y'
select @c2 = 'y'
select
case
when
(@A1 = @A2 OR(COUNT(@A1)+COUNT(@A2))=0 ) and
(@B1 = @b2 OR(COUNT(@B1)+COUNT(@B2))=0 ) and
(@C1 = @c2 OR(COUNT(@C1)+COUNT(@C2))=0 )
then 'True'
else 'False'
end
--Ans: True
Regards,
Mitesh OSwal
+918698619998
July 22, 2011 at 4:58 am
Thanks for that - never thought of doing count(@A) to test for null. Learnt something new!
Viewing 15 posts - 1 through 15 (of 24 total)
You must be logged in to reply to this topic. Login to reply