Correct CASE
-
What is the proper way to write this CASE scenario. What I am trying to do is select the value from one column (in posted table), unless its NULL then select it from another column(the transaction table).
(CASE dbo.TableName.MReading
WHEN NOT NULL THEN SELECT dbo.TableName.MReading
WHEN NULL THEN SELECT dbo.TableName.MReading WHERE dbo.TableName.MGUID = dbo.TableName.MGUID
END)AS CurrentReading,
Thanks!
-
Try:
SELECT
CASE
WHEN dbo.PostedTable.MReading IS NULL THEN
)
SELECT dbo.TransactionTable.MReading
FROM dbo.TransactionTable
WHERE dbo.PostedTable.MGUID = dbo.TransactionTable.MGUID
)
ELSE dbo.PostedTable.MReading
END
FROM dbo.PostedTablealthough it is probably more efficient to do:
SELECT ISNULL(p.MReading, t.MReading)
FROM PostedTable p
INNER JOIN TransactionTable t
ON p.MGUID = t.MGUIDor this, depending on your design:
SELECT ISNULL(p.MReading, t.MReading)
FROM TransactionTable t
LEFT JOIN PostedTable p
ON p.MGUID = t.MGUID -
Thanks jpipes,
I agree, after my post I tried the ISNULL function which WOULD be more efficient in this case. It gives me back what I expect.
Thanks again,
Chris
-
Possible COALSCE might be slightly faster than ISNULL. Even if it is not, it does support more than than just two columns. Here is an example:
create table x (row int, a char, b char, c char)
insert into x(row,c) values(1,'c')
insert into x(row,b,c) values (2,'b','c')
insert into x(row, a,b,c) values (3,'a','b','c')
select row, COALESCE(a,b,c) from x
drop table x
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
-
IsNull and COALSCE are te same except for the number of options supported.
-
Thanks guys for your input.
I must try the COALSCE, I'm sure in the past I have been making things more difficult for myself than need be.
Thanks again!
-
Good gracious, I don't think I've ever seen the word coalesce spelled incorrectly by so many people so many times...
-
Oops, I copy and pasted it myself, and I thought something looked odd.
Slooowww dowwwnnn!
Have a great day guys!
-
Same here, copy and paste.
Just didn't catch it as was busy with something else at the same time.
-
Ok, I have to confess I spelled it wrong first, but the word just doesn't seem to be one that is part of my everyday communication.
Sure is amazing what cut/paste can accomplish and the trouble it can cause. This might be kind of like clicking yes to the license agreements without even reading them. Ok how many of you actually read those license agreements?
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
-
quote:
Sure is amazing what cut/paste can accomplish and the trouble it can cause. This might be kind of like clicking yes to the license agreements without even reading them. Ok how many of you actually read those license agreements?Who cares if you read 'em anyway. They can change the next day and as long as they are posted you are bound to them anyway. So unless you read 'em daily you ain't read 'em
-
quote:
IsNull and COALSCE are te same except for the number of options supported.COALESCE is ANSI standard while IsNull is specific to T-SQL. This is an issue in environments that do or might support multiple DB vendors.
Greg,
I recently (accidentally) ran the same query twice; once with IsNull and the other with Coalesce and was suprised that the IsNull version was quite a bit faster (4:1). I would have thought that they would have been essentially the same speed.
I was going to follow-up on this to see if there was any real performance impact but was wondering if anyone had any similar experiences.
Steve Hendricks
MCSD, MCDBA
AFS Consulting Group
(949) 588-9800 x15
Steve Hendricks
MCSD, MCDBA
Data Matrix
shendricks@afsconsulting.com
(949) 588-9800 x15 -
Here is the test I ran, where I thought it COALESCE might be slightly faster. Not the most scientific test, since I only used elapsed time. By the way I ran this on a standalone workstation. How did you do you performance testing?:
create table x (row int, a char, b char)
insert into x(row,b) values(1,'b')
insert into x(row,b) values (2,'b')
insert into x(row, a,b) values (3,'a','b')
declare @i int
declare @s-2 datetime
declare @C char
set @i = 0
set @s-2 = getdate()
while @I < 10000
begin
select @C=COALESCE(a,b) from x
set @i = @i + 1
end
print 'COALESCE ran for ' + cast(datediff(ms,@s,getdate()) as char)
set @i = 0
set @s-2 = getdate()
while @I < 10000
begin
select @C=isnull(a,b) from x
set @i = @i + 1
end
print 'ISNULL ran for ' + cast(datediff(ms,@s,getdate()) as char)
drop table x
Here is the output I got:
First Run:
COALESCE ran for 546
ISNULL ran for 576
Second Run:
COALESCE ran for 483
ISNULL ran for 533
Third Run:
COALESCE ran for 483
ISNULL ran for 516
Forth Run:
COALESCE ran for 500
ISNULL ran for 530
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
-
My results were strictly anecdotal. I had two different versions of the same script; the difference being that IsNull was replaced with COALESCE. We had decided to make our scripts more portable by using as few T-SQL specifics as convenient.
I had mistakenly used the older version, saw the IsNull's being used and then converted to the newer script. I was surprised by the results so I ran the COALESCE script first and then the IsNull script, thinking that perhaps the state of the data buffer was giving an advantage to one over the other.
At that point I made a mental note to look at this "sometime" and went about the task at hand. Your earlier response triggered my memory.
It still seems odd that your resiults show COALESCE being faster than IsNull. I would have thought that, if anything, IsNull might be slightly faster since there are a static number of parameters.
Live and learn...
Steve Hendricks
MCSD, MCDBA
AFS Consulting Group
(949) 588-9800 x15
Steve Hendricks
MCSD, MCDBA
Data Matrix
shendricks@afsconsulting.com
(949) 588-9800 x15
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply