Author |
Topic |
jeff06
Posting Yak Master
166 Posts |
Posted - 2007-04-09 : 17:36:57
|
X !='NULL'does not work, what is right expression for " is not NULL" thanks.Jeff |
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-04-09 : 17:38:42
|
Well it is "IS NOT NULL"!...WHERE SomeColumn IS NOT NULL...Tara Kizerhttp://weblogs.sqlteam.com/tarad/ |
|
|
SQLUSA
Starting Member
28 Posts |
Posted - 2007-04-09 : 22:17:57
|
This is ONE good reason to avoid NULL! You can't use normal relational syntax!Kalman Toth, Database, DW & BI ArchitectSQL Server 2005 Training - http://www.sqlusa.com |
|
|
jezemine
Master Smack Fu Yak Hacker
2886 Posts |
Posted - 2007-04-10 : 00:30:36
|
not this debate again... www.elsasoft.org |
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2007-04-10 : 12:05:44
|
SPAMquote: Originally posted by SQLUSA This is ONE good reason to avoid NULL! You can't use normal relational syntax!Kalman Toth, Database, DW & BI ArchitectSQL Server 2005 Training - http://www.sqlusa.com
SPAMthe quoted post is not intended to be helpful, just to promote the website in the signature |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-04-10 : 12:13:26
|
Yeah, I noticed he has begun the promoting in this site too...Peter LarssonHelsingborg, Sweden |
|
|
MutantNinjaLoungeSinger
Starting Member
14 Posts |
Posted - 2007-04-10 : 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 - 2007-04-10 : 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 ArchitectSQL Server 2005 Training - http://www.sqlusa.com |
|
|
jezemine
Master Smack Fu Yak Hacker
2886 Posts |
Posted - 2007-04-10 : 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)
7020 Posts |
Posted - 2007-04-10 : 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 ArchitectSQL 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 - 2007-04-11 : 05:20:01
|
That way, you don't get all the inconsistencies you get with NULL.Example: you could writeand IsColumnX = 1instead of and ColumnX is nullAlso you don't get NULL result if ColumnV is NULL:SELECT ColumnA+' - '+ ColumnB +' : '+ ColumnVKalman Toth, Database, DW & BI ArchitectSQL Server 2005 Training - http://www.sqlusa.com |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-04-11 : 05:26:03
|
quote: Originally posted by SQLUSA That way, you don't get all the inconsistencies you get with NULL.Example: you could writeand IsColumnX = 1instead of and ColumnX ColumnV is nullAlso 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 LarssonHelsingborg, Sweden |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-04-11 : 05:27:21
|
If you know ColumnV accepts NULLs, writeSELECT ColumnA + ' - ' + ColumnB + COALESCE(' : ' + ColumnV, '')With your approach (with extra bit column) the query would look something likeSELECT ColumnA + ' - ' + ColumnB + CASE WHEN ColumnX = 1 THEN '' ELSE ' : ' + ColumnV ENDWhat is the benefit of that?Peter LarssonHelsingborg, Sweden |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2007-04-11 : 07:05:51
|
quote: Originally posted by SQLUSA That way, you don't get all the inconsistencies you get with NULL.Example: you could writeand IsColumnX = 1instead of and ColumnX is nullAlso you don't get NULL result if ColumnV is NULL:SELECT ColumnA+' - '+ ColumnB +' : '+ ColumnVKalman Toth, Database, DW & BI ArchitectSQL 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 = 1instead ofand ColumnX is nullI see nothing wrong with getting a null result when you add together two columns and one is null.CODO ERGO SUM |
|
|
harsh_athalye
Master Smack Fu Yak Hacker
5581 Posts |
Posted - 2007-04-11 : 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 AthalyeIndia."The IMPOSSIBLE is often UNTRIED" |
|
|
JimL
SQL Slinging Yak Ranger
1537 Posts |
Posted - 2007-04-11 : 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!JimUsers <> Logic |
|
|
jezemine
Master Smack Fu Yak Hacker
2886 Posts |
Posted - 2007-04-11 : 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 - 2007-04-11 : 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 ArchitectSQL Server 2005 Training - http://www.sqlusa.com |
|
|
SQLUSA
Starting Member
28 Posts |
Posted - 2007-04-11 : 21:53:59
|
>I see no advantage in coding this:>and IsColumnX = 1>instead of>and ColumnX is nullThe 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 ArchitectSQL Server 2005 Training - http://www.sqlusa.com |
|
|
jezemine
Master Smack Fu Yak Hacker
2886 Posts |
Posted - 2007-04-11 : 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
Master Smack Fu Yak Hacker
5581 Posts |
Posted - 2007-04-12 : 00:04:19
|
quote: Originally posted by SQLUSA >I see no advantage in coding this:>and IsColumnX = 1>instead of>and ColumnX is nullThe 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 ArchitectSQL 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 AthalyeIndia."The IMPOSSIBLE is often UNTRIED" |
|
|
Next Page
|