| Author |
 Topic  |
|
jeff06
Posting Yak Master
166 Posts |
 Posted - 04/09/2007 : 17:36:57
|
X !='NULL'
does not work, what is right expression for " is not NULL" thanks.
Jeff |
|
|
tkizer
Almighty SQL Goddess
USA
35007 Posts |
|
|
SQLUSA
Starting Member
28 Posts |
Posted - 04/09/2007 : 22:17:57
|
This is ONE good reason to avoid NULL! You can't use normal relational syntax!
Kalman Toth, Database, DW & BI Architect
SQL Server 2005 Training - http://www.sqlusa.com |
 |
|
|
jezemine
Flowing Fount of Yak Knowledge
USA
2871 Posts |
|
|
russell
Pyro-ma-ni-yak
USA
4968 Posts |
Posted - 04/10/2007 : 12:05:44
|
SPAM
quote:
Originally posted by SQLUSA
This is ONE good reason to avoid NULL! You can't use normal relational syntax!
Kalman Toth, Database, DW & BI Architect
SQL Server 2005 Training - http://www.sqlusa.com
SPAM
the quoted post is not intended to be helpful, just to promote the website in the signature
|
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 04/10/2007 : 12:13:26
|
Yeah, I noticed he has begun the promoting in this site too...
Peter Larsson
Helsingborg, Sweden |
|
|
|
MutantNinjaLoungeSinger
Starting Member
14 Posts |
Posted - 04/10/2007 : 14:32:49
|
quote:
This is ONE good reason to avoid NULL! You can't use normal relational syntax!
And avoiding it dis-allows you to represent an unedited or unspecified field.
(short of using "magic" numbers or values, I suppose)
Just my opinion, but it's hard to take training from a guy seriously who has an irrational fear of null values. |
|
|
|
SQLUSA
Starting Member
28 Posts |
Posted - 04/10/2007 : 20:07:49
|
NULLS are good for nothing!
Most columns just can be set to a default like 0 or empty space.
If you really need to know if a cell contain something, you can add the IsColumnX bit flag.
What do you think what is SQL Server doing internally? It has a bit flag!
If you have your own, you can use NORMAL RELATIONAL LOGIC!
Kalman Toth, Database, DW & BI Architect
SQL Server 2005 Training - http://www.sqlusa.com |
|
|
|
jezemine
Flowing Fount of Yak Knowledge
USA
2871 Posts |
Posted - 04/10/2007 : 23:27:45
|
NULL is not the same as 0 or empty space.
I have two boxes. One contains nothing with certainty. Another contains an unknown item: perhaps nothing, perhaps a diamond necklace worth $100k, perhaps a dog turd. The point is, it's unknown what, if anything, the box contains.
which box would you rather have? Can you see there's a difference?
www.elsasoft.org |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
USA
6997 Posts |
Posted - 04/10/2007 : 23:54:29
|
quote:
Originally posted by SQLUSA
NULLS are good for nothing!
Most columns just can be set to a default like 0 or empty space.
If you really need to know if a cell contain something, you can add the IsColumnX bit flag.
What do you think what is SQL Server doing internally? It has a bit flag!
If you have your own, you can use NORMAL RELATIONAL LOGIC!
Kalman Toth, Database, DW & BI Architect
SQL Server 2005 Training - http://www.sqlusa.com
So if a column cannot be set to 0 or empty space, what do you suggest?
Why would I want to add a bit column that basically just tells me that a column is null, when I could just set the column null? What advantage would that have? It just seems like extra work to me.
CODO ERGO SUM |
|
|
|
SQLUSA
Starting Member
28 Posts |
Posted - 04/11/2007 : 05:20:01
|
That way, you don't get all the inconsistencies you get with NULL.
Example: you could write
and IsColumnX = 1
instead of
and ColumnX is null
Also you don't get NULL result if ColumnV is NULL:
SELECT ColumnA+' - '+ ColumnB +' : '+ ColumnV
Kalman Toth, Database, DW & BI Architect
SQL Server 2005 Training - http://www.sqlusa.com |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 04/11/2007 : 05:26:03
|
quote:
Originally posted by SQLUSA
That way, you don't get all the inconsistencies you get with NULL.
Example: you could write
and IsColumnX = 1
instead of
and ColumnX ColumnV is null
Also you don't get NULL result if ColumnV is NULL:
SELECT ColumnA+' - '+ ColumnB +' : '+ ColumnV
With that approach, you can't concatenate the string with your IsColumnX = 1 anyway!
Peter Larsson
Helsingborg, Sweden |
Edited by - SwePeso on 04/11/2007 05:30:05 |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 04/11/2007 : 05:27:21
|
If you know ColumnV accepts NULLs, write
SELECT ColumnA + ' - ' + ColumnB + COALESCE(' : ' + ColumnV, '')
With your approach (with extra bit column) the query would look something like
SELECT ColumnA + ' - ' + ColumnB + CASE WHEN ColumnX = 1 THEN '' ELSE ' : ' + ColumnV END
What is the benefit of that?
Peter Larsson
Helsingborg, Sweden |
Edited by - SwePeso on 04/11/2007 05:29:48 |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
USA
6997 Posts |
Posted - 04/11/2007 : 07:05:51
|
quote:
Originally posted by SQLUSA
That way, you don't get all the inconsistencies you get with NULL.
Example: you could write
and IsColumnX = 1
instead of
and ColumnX is null
Also you don't get NULL result if ColumnV is NULL:
SELECT ColumnA+' - '+ ColumnB +' : '+ ColumnV
Kalman Toth, Database, DW & BI Architect
SQL Server 2005 Training - http://www.sqlusa.com
I see you managed to not answer my first question:
"So if a column cannot be set to 0 or empty space, what do you suggest?"
So once again, what value would you put in a column if it cannot be set to 0 or empty space?
I see no advantage in coding this:
and IsColumnX = 1
instead of
and ColumnX is null
I see nothing wrong with getting a null result when you add together two columns and one is null.
CODO ERGO SUM |
|
|
|
harsh_athalye
Flowing Fount of Yak Knowledge
India
5509 Posts |
Posted - 04/11/2007 : 07:19:55
|
The only benefit of not having NULL columns is that SQL Server will not create NULL bitmap for each row. But that advantage is easily nullified by your suggestion of adding bit column plus other complexities introduced in the code. The code becomes unreadable and difficult to maintain, at the best.
I don't see any reason to fear about NULLs if you know how to handle them and if you don't know how to handle them, then......I guess you are in the wrong field.
Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED" |
|
|
|
JimL
SQL Slinging Yak Ranger
USA
1530 Posts |
Posted - 04/11/2007 : 07:44:33
|
quote:
I don't see any reason to fear about NULLs if you know how to handle them and if you don't know how to handle them, then......I guess you are in the wrong field.
Can you say Smack!
Jim
Users <> Logic |
|
|
|
jezemine
Flowing Fount of Yak Knowledge
USA
2871 Posts |
Posted - 04/11/2007 : 09:38:41
|
But that advantage is easily nullified...
how can it be nullified if he doesn't allow NULL? 
www.elsasoft.org |
|
|
|
SQLUSA
Starting Member
28 Posts |
Posted - 04/11/2007 : 21:48:54
|
What if the column cannot be defaulted to 0 or empty space?
If you set the IsColumnX flag 0, that means it is not populated, so you can set it to anything.
Oh yeah, "ABC" + NULL + "XYZ" = NULL !!! Very logical! The triumph of science and technology! Even a 2nd grader would know that it does not make sense! IT IS ILLOGICAL!!!
Kalman Toth, Database, DW & BI Architect
SQL Server 2005 Training - http://www.sqlusa.com |
|
|
|
SQLUSA
Starting Member
28 Posts |
Posted - 04/11/2007 : 21:53:59
|
>I see no advantage in coding this:
>and IsColumnX = 1
>instead of
>and ColumnX is null
The first is normal algorithmic coding, the second is GARBAGE! What does "is" mean? A fruit is blue, a fruit is red, a fruit is NULL?
Kalman Toth, Database, DW & BI Architect
SQL Server 2005 Training - http://www.sqlusa.com |
|
|
|
jezemine
Flowing Fount of Yak Knowledge
USA
2871 Posts |
Posted - 04/11/2007 : 22:42:20
|
quote:
Originally posted by SQLUSA
Oh yeah, "ABC" + NULL + "XYZ" = NULL !!! Very logical! The triumph of science and technology! Even a 2nd grader would know that it does not make sense! IT IS ILLOGICAL!!!
such pronouncements on your part demonstrate that your understanding of NULL is severely lacking. NULL means UNKNOWN. And you are a "trainer" in SQL? yikes.
so tell me, what does 1 + UNKNOWN equate to? you think it's 1? That would only be true if the unknown value happened to be 0. But it's not. It's unknown. Because it's unknown, the sum must also be unknown, and therefore NULL, because that's what NULL means.
NULL means UNKNOWN. got it?
www.elsasoft.org |
|
|
|
harsh_athalye
Flowing Fount of Yak Knowledge
India
5509 Posts |
Posted - 04/12/2007 : 00:04:19
|
quote:
Originally posted by SQLUSA
>I see no advantage in coding this:
>and IsColumnX = 1
>instead of
>and ColumnX is null
The first is normal algorithmic coding, the second is GARBAGE! What does "is" mean? A fruit is blue, a fruit is red, a fruit is NULL?
Kalman Toth, Database, DW & BI Architect
SQL Server 2005 Training - http://www.sqlusa.com
Thank GOD!!
You were not part of ANSI-92 committee...what would have been life without NULLS?
Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED" |
|
|
|
Topic |
|
not null
