Adding numbers from a nvarchar field

[JayG]

Hi all,

Would really appreciate any help on a problem I'm having with an SQL
database and web 'front end' used to display the information.

Basically I have a field in an SQL db which data gets fed into
automatically. It is called 'NumberOfUsers'. This field can contain
integers or a set text so it was initially set to be nvarchar type and
everything was fine.

Now it is required that the numbers from all these fields be added
together to form a total. Does anyone know how I could go about, in my
ASP script (VBScript), adding all the numbers up from each recordset but
IGNORE the text that is displayed?

Apologies if this sounds a bit vague - please let me know if more
detailed information is required.

Many thanks,


JayG

*** Sent via Developersdex [url]http://www.developersdex.com[/url] ***
Don't just participate in USENET...get rewarded for it!

[Bob Barrows]

JayG wrote:

> Hi all,
>
> Would really appreciate any help on a problem I'm having with an SQL
> database and web 'front end' used to display the information.
>
> Basically I have a field in an SQL db which data gets fed into
> automatically. It is called 'NumberOfUsers'. This field can contain
> integers or a set text so it was initially set to be nvarchar type and
> everything was fine.
>
> Now it is required that the numbers from all these fields be added
> together to form a total. Does anyone know how I could go about, in
> my ASP script (VBScript), adding all the numbers up from each
> recordset but IGNORE the text that is displayed?
>
> Apologies if this sounds a bit vague - please let me know if more
> detailed information is required.
>
> Many thanks,
>

Use a CASE expression in your query (Don't do it in vbscript - very
inefficient - always use SQL to do your aggregations). I can't get too
specific because you did not provide a few rows of sample data but something
like this:

select sum(case NumberOfUsers when '<text to be ignored>' then 0
else NumberOfUsers end) UserTotal ...

HTH,
Bob Barrows

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

[JayG]

Thanks for the reply. I think I'm almost there now - but the thing is
there are 2 seperate instances of text to check for. At the moment I
have:

strQ = "SELECT sum(case when NumberOfUsers = 'Not Set' then 0 else
NumberOfUsers end) as UserTotal FROM Main "

Set objRS = objConn.Execute(strQ)


But I get an error back because some records have the text 'Unlimited'
in the NumberOfUsers field. How do I check for this as well?


Thanks,


Jay



*** Sent via Developersdex [url]http://www.developersdex.com[/url] ***
Don't just participate in USENET...get rewarded for it!

[Bob Barrows]

JayG wrote:

> Thanks for the reply. I think I'm almost there now - but the thing is
> there are 2 seperate instances of text to check for. At the moment I
> have:
>
> strQ = "SELECT sum(case when NumberOfUsers = 'Not Set' then 0 else
> NumberOfUsers end) as UserTotal FROM Main "
>
> Set objRS = objConn.Execute(strQ)
>
>
> But I get an error back because some records have the text 'Unlimited'
> in the NumberOfUsers field. How do I check for this as well?
>

I goofed. You cannot use CASE for this. The problem is, CASE does not use
short-circuit evaluation. It evaluates both result expressions for every
row, throwing an error on the rows containing the alpha characters.

You need to explicitly cast the data into a numeric datatype, using the
WHERE clause to prevent errors. Like this:

strQ = "SELECT sum(CAST(NumberOfUsers as int)) as UserTotal " & _
"FROM Main WHERE NumberOfUsers != 'Not Set' AND " & _
"NumberOfUsers != 'Unlimited'

In this case, you could get away with this:
strQ = "SELECT sum(CAST(NumberOfUsers as int)) as UserTotal " & _
"FROM Main WHERE ISNUMERIC(NumberOfUsers) = 1"


Better yet, if there is an index on NumberOfUsers, this will perform better
(the criteria in this case is SARGable:

strQ = "SELECT sum(CAST(NumberOfUsers as int)) as UserTotal " & _
"FROM Main WHERE NumberOfUsers) < 'a'"

I prefer using stored procedures vs. dynamic sql:

CREATE PROCEDURE GetUserCount AS
SELECT sum(CAST(NumberOfUsers AS int)) as UserTotal
FROM Main
WHERE NumberOfUsers < 'a'

In ASP:

Set objRS = createobject("adodb.recordset")
objConn.GetUserCount objRS

HTH,
Bob Barrows
PS. Why is this column nvarchar? You don't seem to be storing any
internation characters in it. You could probably save space by making it a
varchar column.
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

[JayG]

"Bob Barrows" <reb01501@NOyahoo.SPAMcom> wrote in message
news:%23ZkDhU33DHA.556@TK2MSFTNGP11.phx.gbl...

> JayG wrote:

> > Thanks for the reply. I think I'm almost there now - but the thing is
> > there are 2 seperate instances of text to check for. At the moment I
> > have:
> >
> > strQ = "SELECT sum(case when NumberOfUsers = 'Not Set' then 0 else
> > NumberOfUsers end) as UserTotal FROM Main "
> >
> > Set objRS = objConn.Execute(strQ)
> >
> >
> > But I get an error back because some records have the text 'Unlimited'
> > in the NumberOfUsers field. How do I check for this as well?
> >

>
> I goofed. You cannot use CASE for this. The problem is, CASE does not use
> short-circuit evaluation. It evaluates both result expressions for every
> row, throwing an error on the rows containing the alpha characters.
>
> You need to explicitly cast the data into a numeric datatype, using the
> WHERE clause to prevent errors. Like this:
>
> strQ = "SELECT sum(CAST(NumberOfUsers as int)) as UserTotal " & _
> "FROM Main WHERE NumberOfUsers != 'Not Set' AND " & _
> "NumberOfUsers != 'Unlimited'
>
> In this case, you could get away with this:
> strQ = "SELECT sum(CAST(NumberOfUsers as int)) as UserTotal " & _
> "FROM Main WHERE ISNUMERIC(NumberOfUsers) = 1"
>
>
> Better yet, if there is an index on NumberOfUsers, this will perform

better

> (the criteria in this case is SARGable:
>
> strQ = "SELECT sum(CAST(NumberOfUsers as int)) as UserTotal " & _
> "FROM Main WHERE NumberOfUsers) < 'a'"
>
> I prefer using stored procedures vs. dynamic sql:
>
> CREATE PROCEDURE GetUserCount AS
> SELECT sum(CAST(NumberOfUsers AS int)) as UserTotal
> FROM Main
> WHERE NumberOfUsers < 'a'
>
> In ASP:
>
> Set objRS = createobject("adodb.recordset")
> objConn.GetUserCount objRS
>
> HTH,
> Bob Barrows
> PS. Why is this column nvarchar? You don't seem to be storing any
> internation characters in it. You could probably save space by making it a
> varchar column.
> --

Problem solved - thanks very much. Used the dynamic SQL query method in the
end.


Thanks,


Jay