Author |
Topic |
nic
Posting Yak Master
209 Posts |
Posted - 2004-07-16 : 15:21:59
|
I have a procedure used for reports where I use the coalesce statement to see if a value was provided. If no value was provided, I do not include the column in the where clause. This gets me around the scenario of having to use dynamic sql (http://www.sqlteam.com/item.asp?ItemID=2077). BUT I just noticed if the field in question contains a null value, the COALESCE statement fails.if myField contains a null value, the record is not returned.select * From myTablewhere myField = COALESCE(@field, myField) I would think no matter what was in the field column, field = field would always be true. just as 1=1, you would think null=null, but that isn't true. If this is the case, I don't see any way to avoid using dynamic sql. hmmmmmm. create table myTable ( tableID int null, myField varchar(10) null)insert into myTable Values (1,null)--declare @field varchar(10)--set @field = null--returns 1 recordselect * from myTable--returns 1 recordselect * from myTable where tableID = tableID-- returns nothing??Select * from myTable where myField = myFielddrop table myTable Nic |
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2004-07-16 : 15:39:24
|
Question for you, Nic:True or False: what I earn per hour = What Brett earns per hourWhat would your answer to that question be, if you had to answer it right now? you can't say that it's true, since you don't know our salaries. To you, both of our salaries equal Null -- unknown. You can't even really say that it's false, either -- all you really determine for sure is that the answer to that question is "unknown"; certainly not a 100% positive TRUE.Remember, Null = unknownso is Null = Null, or 0 =Null, or Null = anything? the answer: Unknown !however, if YOUR particular use of Null has a meaning -- i.e., for "Termination Date" you might say Null means someone who has not terminated -- then in your implementation of a query you might wish to equate a Null with a Null. But it would be illogical for SQL Server to make that assumption, based on the reasons I gave you. Thus, SQL requires that you need to explicitly tell it you wish for that comparision to return true either with ANSI NULLS OFF or by saying:(A = B) or (A is null AND B is null)does that help a little?- Jeff |
|
|
drymchaser
Aged Yak Warrior
552 Posts |
Posted - 2004-07-16 : 15:39:29
|
Not sure about performance, but you could:select * From myTablewhere COALESCE(myField, 1) = COALESCE(@field, myField, 1) -- data type matching needed of course |
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2004-07-16 : 16:10:08
|
You get paid?Nope...don't like it....Just because you don't know something doesn't mean NULLI like the temrination thing...For an employee who is currently working there does not exists a termination date for their current job.If something doesn't even exists, it can't be comparred to anything....not even its self, because it doesn't exists.Null is not really data, but is often confused as a type of data...which it's not....becasue...well I hope get it....Brett8-) |
|
|
SamC
White Water Yakist
3467 Posts |
Posted - 2004-07-16 : 16:15:38
|
Here's one that I like. What's wrong with this?CASE ColA WHEN NULL THEN 'ColA is NULL' ELSE 'ColA is NOT NULL' ENDalways returns 'ColA is NOT NULL' |
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2004-07-16 : 17:56:29
|
I hate NULLs. I avoid them when possible to try and avoid odd results.-ec |
|
|
Kristen
Test
22859 Posts |
Posted - 2004-07-17 : 04:00:19
|
I like Celko's definition of NULL:The hair colour of a bald man.Kristen |
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-07-17 : 09:36:47
|
He better be completely bald, or maybe that's the point. hmmmMeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
Arnold Fribble
Yak-finder General
1961 Posts |
Posted - 2004-07-17 : 10:19:18
|
quote: Originally posted by derrickleggett He better be completely bald, or maybe that's the point. hmmm
Remember the Disney version of Aladdin? Imagine the Genie without a topknot. And less blue. |
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-07-17 : 11:09:24
|
Ahhhh, everything now becomes clear. I feel the horizon.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
SamC
White Water Yakist
3467 Posts |
Posted - 2004-07-17 : 11:20:23
|
And yet... noone has corrected the CASE statement I posted. |
|
|
Arnold Fribble
Yak-finder General
1961 Posts |
Posted - 2004-07-17 : 11:38:38
|
Oh alright then...You can't use CASE with NULL like that because it does the equivalent of an equality check. You'd have to do something like:CASE WHEN ColA IS NULL THEN 'ColA is NULL' ELSE 'ColA is NOT NULL' END |
|
|
Kristen
Test
22859 Posts |
Posted - 2004-07-17 : 12:26:31
|
Might work with one of those silly SET AnsiNull thingamajigsKristen |
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-07-17 : 12:51:10
|
Well, I'll be twinkered. Yep, it works alright. Syntax is a kewl thing to know ya know. So are tests. I like tests. They seem to prove things years of babble just ummmm, well, ummmmmmmm babbles about.IF (SELECT OBJECT_ID('Northwind..djl_test')) IS NOT NULL BEGIN DROP TABLE djl_test END--Create the table to fix this awful nightmare of a situationCREATE TABLE djl_test( int1 INT, int2 INT, value NVARCHAR(55), int3 INT)INSERT djl_test(int1, int2, value, int3) SELECT 1,2,'Here''s the problem',4 UNION ALL SELECT 1,3,'What????',5 UNION ALL SELECT 1,5,N'Coño',99 UNION ALL SELECT 2,5,NULL,99SELECT * FROM djl_test--Testing. If this was a real emergency, you'd be dead so don't worry about it.SELECT int1, int2, value, CASE WHEN value IS NULL THEN 'value is NULL' ELSE 'value is NOT NULL' END AS value_testFROM djl_test--Clean up after yourself. gheeshDROP TABLE djl_test MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
Arnold Fribble
Yak-finder General
1961 Posts |
Posted - 2004-07-17 : 13:08:39
|
quote: I like tests. They seem to prove things years of babble just ummmm, well, ummmmmmmm babbles about.
Don't talk to me about tests. I could cry, I really could. We interviewed this week. Seven candidates. Simple SQL test. Well, we thought so. All of them failed produce a solution. I simply could not fathom the thought processes of at least two of them.I'd post the test, but you never know who might be lurking... |
|
|
Kristen
Test
22859 Posts |
Posted - 2004-07-17 : 13:11:34
|
Are you worried your answer isn't the right one then Arnold?!Kristen |
|
|
Arnold Fribble
Yak-finder General
1961 Posts |
Posted - 2004-07-17 : 13:27:34
|
If only. One of them, it was like that old, much misattributed, Wolfgang Pauli quote, "This isn't right, it isn't even wrong." |
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2004-07-17 : 14:27:14
|
quote: Originally posted by Arnold Fribble
quote: I like tests. They seem to prove things years of babble just ummmm, well, ummmmmmmm babbles about.
Don't talk to me about tests. I could cry, I really could. We interviewed this week. Seven candidates. Simple SQL test. Well, we thought so. All of them failed produce a solution. I simply could not fathom the thought processes of at least two of them.I'd post the test, but you never know who might be lurking...
post the test! |
|
|
Kristen
Test
22859 Posts |
Posted - 2004-07-17 : 14:35:58
|
pass the test? |
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-07-17 : 15:08:41
|
Yes please. I would like to see it so I know if I ever interview with you. lolMeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
Arnold Fribble
Yak-finder General
1961 Posts |
Posted - 2004-07-17 : 15:35:37
|
Sorry, but I won't post it. It's not the potential reusability, more that it would make the foregoing remarks identifiable. |
|
|
Next Page
|