April 26, 2016 at 7:36 am
Hello,
I am trying to add multiple row data with ISnull in but I couldn't seem to make it work. My query is like below:
SELECT QueryID, DatasetID, ISNULL (SUM(mm.XMLResults.value ('(./MM)[1]','varchar(100)'),(mm.XMLResults.value ('(./MM)[1]','varchar(100)'))),0)
AS MEDICAL_MM from OVT_RESULTS CROSS APPLY XMLResults.nodes('/row[TITLE = "MM"]') as mm(XMLResults) where QueryID = 105
I know this is very wrong.. I'm new to SQL. thanks so much in advance.
April 26, 2016 at 7:40 am
Does this gets you what you need?
SELECT
QueryID,
DatasetID,
ISNULL (SUM(mm.XMLResults.value ('(./MM)[1]','varchar(100)')),0) AS MEDICAL_MM
FROM OVT_RESULTS
CROSS APPLY XMLResults.nodes('/row[TITLE = "MM"]') as mm(XMLResults)
WHERE QueryID = 105
EDIT: OMG, I just saw that you're converting to varchar. Use a numeric data type instead which is appropriate to your values.
April 26, 2016 at 7:42 am
As a general rule, it's better to put IsNulls inside Sums rather than outside. This avoids the warning message about NULL values being eliminated:
Select Sum(IsNull([Col],0))
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
April 26, 2016 at 9:08 am
Phil Parkin (4/26/2016)
As a general rule, it's better to put IsNulls inside Sums rather than outside. This avoids the warning message about NULL values being eliminated:
Select Sum(IsNull([Col],0))
+1. Agree with Phil.
April 26, 2016 at 9:20 am
Steve Jones - SSC Editor (4/26/2016)
Phil Parkin (4/26/2016)
As a general rule, it's better to put IsNulls inside Sums rather than outside. This avoids the warning message about NULL values being eliminated:
Select Sum(IsNull([Col],0))+1. Agree with Phil.
But isn't that the same as saying Select Sum(Col) except in the case where all values of Col are NULL, and so you may just as well say Select ISNULL(SUM(Col),0) OK, so you get the warning message, but don't you save on some processor ticks, especially if there are a lot of rows?
John
April 26, 2016 at 9:56 am
John Mitchell-245523 (4/26/2016)
Steve Jones - SSC Editor (4/26/2016)
Phil Parkin (4/26/2016)
As a general rule, it's better to put IsNulls inside Sums rather than outside. This avoids the warning message about NULL values being eliminated:
Select Sum(IsNull([Col],0))+1. Agree with Phil.
But isn't that the same as saying
Select Sum(Col)except in the case where all values of Col are NULL, and so you may just as well saySelect ISNULL(SUM(Col),0)OK, so you get the warning message, but don't you save on some processor ticks, especially if there are a lot of rows?John
Seems sensible, but I remember testing this once, and they were always very, very close, with the surprising twist that the SUM(ISNULL(... was typically the one with oh-so-slightly lower CPU and duration.
I'll see if I can whip up a test later to see if my memory's deceiving me. 🙂
Cheers!
April 26, 2016 at 9:58 am
Jacob Wilkins (4/26/2016)
John Mitchell-245523 (4/26/2016)
Steve Jones - SSC Editor (4/26/2016)
Phil Parkin (4/26/2016)
As a general rule, it's better to put IsNulls inside Sums rather than outside. This avoids the warning message about NULL values being eliminated:
Select Sum(IsNull([Col],0))+1. Agree with Phil.
But isn't that the same as saying
Select Sum(Col)except in the case where all values of Col are NULL, and so you may just as well saySelect ISNULL(SUM(Col),0)OK, so you get the warning message, but don't you save on some processor ticks, especially if there are a lot of rows?John
Seems sensible, but I remember testing this once, and they were always very, very close, with the surprising twist that the SUM(ISNULL(... was typically the one with oh-so-slightly lower CPU and duration.
I'll see if I can whip up a test later to see if my memory's deceiving me. 🙂
Cheers!
I did a test earlier today with similar results. I discarded it as I thought it wouldn't add much value.
April 26, 2016 at 10:20 am
Steve Jones - SSC Editor (4/26/2016)
Phil Parkin (4/26/2016)
As a general rule, it's better to put IsNulls inside Sums rather than outside. This avoids the warning message about NULL values being eliminated:
Select Sum(IsNull([Col],0))+1. Agree with Phil.
+1. Agree with Phil and Steve
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
April 27, 2016 at 12:30 am
Hello Everyone,
Thank you so much for all of your inputs. I managed to make the query work but doing this:
SELECT QueryID, DatasetID, SUM(MEDICAL_MM) FROM
(SELECT QueryID, DatasetID, convert (numeric,ISNULL (mm.XMLResults.value ('(./MM)[1]','varchar(100)'),mm.XMLResults.value ('(./MM)[1]','varchar(100)')))
AS MEDICAL_MM, ISNULL (mm.XMLResults.value ('(./TPERIOD)[1]','varchar(100)'),mm.XMLResults.value ('(./TPERIOD)[1]','varchar(100)'))AS T_PERIOD
from OVT_RESULTS CROSS APPLY XMLResults.nodes('/row[TITLE = "MM"]') as mm(XMLResults) where QueryID = 105) A
group by QueryID, DatasetID
Thanks again.. 🙂
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply