COALESCE an integer?

Hi guys
I've got a table with a field called PMon_Total. At the moment, a lot have
rows have this set to null.
I want to substitute this NULL for a 0 so that I can count all the
PMon_Total fields, however, I can't get coalesce to work.
Here's my select...
SELECT ProjectID, SessionID, SessionDate, PMon_Total FROM Sessions WHE...
Works fine if I try and grab PMon_Total values from C#, as long as they
arent null! However, if I put
SELECT ProjectID, SessionID, SessionDate, COALESCE(PMon_Total,0) FROM
Sessions WHE...
my code give me a System.IndexOutOfRange exception. How can I coalesce
PMon_Total, so I know that any nulls will show as 0?!
Cheers
Dan> I want to substitute this NULL for a 0 so that I can count all the
> PMon_Total fields, however, I can't get coalesce to work.
I'm not sure what this means. Why would counting require you to change
nulls to zero? COUNT(*) will count rows whether NULL or not.

> my code give me a System.IndexOutOfRange exception.
That doesn't look like a SQL Server error. Can you tell us the exact
error message and error number please. Did you try the query in Query
Analyzer? Post some code to reproduce the problem if you can. I suspect
this is a client-side error.
David Portas
SQL Server MVP
--|||try
SELECT ProjectID, SessionID, SessionDate, isnull(PMon_Total,0) FROM
Sessions WHE...
Regards
R.D
--Knowledge gets doubled when shared
"dhnriverside" wrote:

> Hi guys
> I've got a table with a field called PMon_Total. At the moment, a lot have
> rows have this set to null.
> I want to substitute this NULL for a 0 so that I can count all the
> PMon_Total fields, however, I can't get coalesce to work.
> Here's my select...
> SELECT ProjectID, SessionID, SessionDate, PMon_Total FROM Sessions WHE...
> Works fine if I try and grab PMon_Total values from C#, as long as they
> arent null! However, if I put
> SELECT ProjectID, SessionID, SessionDate, COALESCE(PMon_Total,0) FROM
> Sessions WHE...
> my code give me a System.IndexOutOfRange exception. How can I coalesce
> PMon_Total, so I know that any nulls will show as 0?!
> Cheers
>
> Dan|||It's weird. I've just tried isnull() and that gave me the error as well.
Here's the C# code I'm using (where sDate = '2005-04-01' and eDate =
'2005-10-13')...
sql = "SELECT ProjectID, SessionID, SessionDate, PMon_Total FROM Sessions
WHERE (SessionDate > '" + sDate + "') AND (SessionDate < '" + eDate + "')
ORDER BY ProjectID";
SqlConnection dbConn = new
SqlConnection(ConfigurationSettings.AppSettings.Get("System_ConnectionString
"));
dbConn.Open();
SqlCommand prjCmd = new SqlCommand(sql, dbConn);
SqlDataReader prjDr = prjCmd.ExecuteReader();
try
{
while(prjDr.Read())
{
Response.Write(prjDr["ProjectID"].ToString() + ", " +
prjDr["SessionID"].ToString() + ", " + prjDr["SessionDate"].ToString() + "|"
);
if(Convert.ToInt32(prjDr["ProjectID"].ToString()) > projID)
{
projC++;
projID = Convert.ToInt32(prjDR["ProjectID"].ToString());
Response.Write("new!");
}
seshC++;
Response.Write(", " + prjDr["PMon_Total"].ToString() + "<br>");
}
}
finally
{
prjDr.Close();
prjDr = null;
dbConn.Close();
dbConn = null;
}
The response.write statements are purely for debugging :)
Basically, with the code like that, it works perfectly. However, If I try
sql = "SELECT ProjectID, SessionID, SessionDate, COALESCE(PMon_Total,0) FROM
Sessions WHERE (SessionDate > '" + sDate + "') AND (SessionDate < '" + eDate
+ "') ORDER BY ProjectID";
or isnull() - i get the following error...
Server Error in '/dartsIntranetV2.1' Application.
----
--
PMon_Total
Description: An unhandled exception occurred during the execution of the
current web request. Please review the stack trace for more information abou
t
the error and where it originated in the code.
Exception Details: System.IndexOutOfRangeException: PMon_Total
Source Error:
An unhandled exception was generated during the execution of the current web
request. Information regarding the origin and location of the exception can
be identified using the exception stack trace below.
The error message identifies line 223, which is effectively the last line in
the loop...
Response.Write(", " + prjDr["PMon_Total"].ToString() + "<br>");
Any help much appreciated!
Cheers
Dan
----
"David Portas" wrote:

> I'm not sure what this means. Why would counting require you to change
> nulls to zero? COUNT(*) will count rows whether NULL or not.
>
> That doesn't look like a SQL Server error. Can you tell us the exact
> error message and error number please. Did you try the query in Query
> Analyzer? Post some code to reproduce the problem if you can. I suspect
> this is a client-side error.
> --
> David Portas
> SQL Server MVP
> --
>|||If you print the SQL statement you send to SQL Server (or use Profiler to ca
tch it), you can do two
things:
1. Execute in Query Analyzer to see whether the problem is in the client or
in SQL Server.
2. Post it here so we can have a look at it.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"dhnriverside" <dan@.musoswire.com> wrote in message
news:EE59E12B-A568-4482-95C0-D6E7C67CFE86@.microsoft.com...
> It's weird. I've just tried isnull() and that gave me the error as well.
> Here's the C# code I'm using (where sDate = '2005-04-01' and eDate =
> '2005-10-13')...
> sql = "SELECT ProjectID, SessionID, SessionDate, PMon_Total FROM Sessions
> WHERE (SessionDate > '" + sDate + "') AND (SessionDate < '" + eDate + "')
> ORDER BY ProjectID";
>
> SqlConnection dbConn = new
> SqlConnection(ConfigurationSettings.AppSettings.Get("System_ConnectionStri
ng"));
> dbConn.Open();
> SqlCommand prjCmd = new SqlCommand(sql, dbConn);
> SqlDataReader prjDr = prjCmd.ExecuteReader();
> try
> {
> while(prjDr.Read())
> {
> Response.Write(prjDr["ProjectID"].ToString() + ", " +
> prjDr["SessionID"].ToString() + ", " + prjDr["SessionDate"].ToString() + "
|");
> if(Convert.ToInt32(prjDr["ProjectID"].ToString()) > projID)
> {
> projC++;
> projID = Convert.ToInt32(prjDR["ProjectID"].ToString());
> Response.Write("new!");
> }
> seshC++;
> Response.Write(", " + prjDr["PMon_Total"].ToString() + "<br>");
> }
> }
> finally
> {
> prjDr.Close();
> prjDr = null;
> dbConn.Close();
> dbConn = null;
> }
> The response.write statements are purely for debugging :)
> Basically, with the code like that, it works perfectly. However, If I try
> sql = "SELECT ProjectID, SessionID, SessionDate, COALESCE(PMon_Total,0) FR
OM
> Sessions WHERE (SessionDate > '" + sDate + "') AND (SessionDate < '" + eDa
te
> + "') ORDER BY ProjectID";
> or isnull() - i get the following error...
> Server Error in '/dartsIntranetV2.1' Application.
> ----
--
> PMon_Total
> Description: An unhandled exception occurred during the execution of the
> current web request. Please review the stack trace for more information ab
out
> the error and where it originated in the code.
> Exception Details: System.IndexOutOfRangeException: PMon_Total
> Source Error:
> An unhandled exception was generated during the execution of the current w
eb
> request. Information regarding the origin and location of the exception ca
n
> be identified using the exception stack trace below.
> The error message identifies line 223, which is effectively the last line
in
> the loop...
> Response.Write(", " + prjDr["PMon_Total"].ToString() + "<br>");
> Any help much appreciated!
> Cheers
>
> Dan
> ----
> "David Portas" wrote:
>|||This is a C# error so you may have more luck in a C# forum.
Why aren't you using the parameters collection to pass your parameters?
That at least would eliminate the possibility of a corrupt command
string due to invalid input. It also avoids any SQL injection
vulnerabilities. Example at:
http://msdn.microsoft.com/library/d.../>
ersTopic.asp
You should also consider putting the SQL in a proc since the query
appears to be static anyway.
David Portas
SQL Server MVP
--|||You need to give the column an alias,
SELECT ProjectID, SessionID, SessionDate, COALESCE(PMon_Total,0) AS
PMon_Total FROM
Sessions WHE...
That should fix it - it was being returned to c# with no column name.
"dhnriverside" <dan@.musoswire.com> wrote in message
news:13A0B95A-CBB3-40D3-9FF6-4B6762CB8A6D@.microsoft.com...
> Hi guys
> I've got a table with a field called PMon_Total. At the moment, a lot have
> rows have this set to null.
> I want to substitute this NULL for a 0 so that I can count all the
> PMon_Total fields, however, I can't get coalesce to work.
> Here's my select...
> SELECT ProjectID, SessionID, SessionDate, PMon_Total FROM Sessions WHE...
> Works fine if I try and grab PMon_Total values from C#, as long as they
> arent null! However, if I put
> SELECT ProjectID, SessionID, SessionDate, COALESCE(PMon_Total,0) FROM
> Sessions WHE...
> my code give me a System.IndexOutOfRange exception. How can I coalesce
> PMon_Total, so I know that any nulls will show as 0?!
> Cheers
>
> Dan|||> Basically, with the code like that, it works perfectly. However, If I try
> sql = "SELECT ProjectID, SessionID, SessionDate, COALESCE(PMon_Total,0)
> FROM
> Sessions WHERE (SessionDate > '" + sDate + "') AND (SessionDate < '" +
> eDate
> + "') ORDER BY ProjectID";
> or isnull() - i get the following error...
This SQL statement doesn't include an alias for COALESCE(PMon_Total,0). You
get an IndexOutOfRangeException exception when you reference
prjDr["PMon_Total"] because no column with that name exists in the result.
Try specifying PMon_Total as the alias for the COALESCE expression:
sql = "SELECT ProjectID, SessionID, SessionDate,
COALESCE(PMon_Total,0) AS PMon_Total
FROM Sessions
WHERE (SessionDate > '" + sDate + "') AND (SessionDate < '" + eDate
+ "') ORDER BY ProjectID";
Hope this helps.
Dan Guzman
SQL Server MVP
"dhnriverside" <dan@.musoswire.com> wrote in message
news:EE59E12B-A568-4482-95C0-D6E7C67CFE86@.microsoft.com...
> It's weird. I've just tried isnull() and that gave me the error as well.
> Here's the C# code I'm using (where sDate = '2005-04-01' and eDate =
> '2005-10-13')...
> sql = "SELECT ProjectID, SessionID, SessionDate, PMon_Total FROM Sessions
> WHERE (SessionDate > '" + sDate + "') AND (SessionDate < '" + eDate + "')
> ORDER BY ProjectID";
>
> SqlConnection dbConn = new
> SqlConnection(ConfigurationSettings.AppSettings.Get("System_ConnectionStri
ng"));
> dbConn.Open();
> SqlCommand prjCmd = new SqlCommand(sql, dbConn);
> SqlDataReader prjDr = prjCmd.ExecuteReader();
> try
> {
> while(prjDr.Read())
> {
> Response.Write(prjDr["ProjectID"].ToString() + ", " +
> prjDr["SessionID"].ToString() + ", " + prjDr["SessionDate"].ToString() +
> "|");
> if(Convert.ToInt32(prjDr["ProjectID"].ToString()) > projID)
> {
> projC++;
> projID = Convert.ToInt32(prjDR["ProjectID"].ToString());
> Response.Write("new!");
> }
> seshC++;
> Response.Write(", " + prjDr["PMon_Total"].ToString() + "<br>");
> }
> }
> finally
> {
> prjDr.Close();
> prjDr = null;
> dbConn.Close();
> dbConn = null;
> }
> The response.write statements are purely for debugging :)
> Basically, with the code like that, it works perfectly. However, If I try
> sql = "SELECT ProjectID, SessionID, SessionDate, COALESCE(PMon_Total,0)
> FROM
> Sessions WHERE (SessionDate > '" + sDate + "') AND (SessionDate < '" +
> eDate
> + "') ORDER BY ProjectID";
> or isnull() - i get the following error...
> Server Error in '/dartsIntranetV2.1' Application.
> ----
--
> PMon_Total
> Description: An unhandled exception occurred during the execution of the
> current web request. Please review the stack trace for more information
> about
> the error and where it originated in the code.
> Exception Details: System.IndexOutOfRangeException: PMon_Total
> Source Error:
> An unhandled exception was generated during the execution of the current
> web
> request. Information regarding the origin and location of the exception
> can
> be identified using the exception stack trace below.
> The error message identifies line 223, which is effectively the last line
> in
> the loop...
> Response.Write(", " + prjDr["PMon_Total"].ToString() + "<br>");
> Any help much appreciated!
> Cheers
>
> Dan
> ----
> "David Portas" wrote:
>|||Fixed!
I had a look at the query code running from Web Data Administrator, and it
was passing the results of COALESCE as a new column, named Column 1.
I changed it to COALESCE(PMon_Total, 0) AS MonTotal, and pulled that from my
code, which works perfectly.
Thanks for everyones help!
"David Portas" wrote:

> This is a C# error so you may have more luck in a C# forum.
> Why aren't you using the parameters collection to pass your parameters?
> That at least would eliminate the possibility of a corrupt command
> string due to invalid input. It also avoids any SQL injection
> vulnerabilities. Example at:
> http://msdn.microsoft.com/library/d...
etersTopic.asp
> You should also consider putting the SQL in a proc since the query
> appears to be static anyway.
> --
> David Portas
> SQL Server MVP
> --
>