Convert to numeric and check for null values???? 1

[cfcProgrammer]

Hi,

I have been playing around with this for a little while now... can someone help me try to figure out if this is even possible and if it is... the correct syntax...


I need to do a sum of a field that is defined as varchar... the field does hold a number so I am converting to numeric... however the field also can hold null.. this is causing me issues... is it even possible to do this??

The error I am getting is
[highlight][COLOR=white blue]
Server: Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to numeric.
[/color] [/highlight]

select  sum(convert(numeric(5),(isnull(call,0)))) 
from edt e join mission m on e.mseq=m.mseq join flt f on e.mseq=f.mseq
where (e.tcc = 'VFG')
and convert(datetime,(convert(varchar(2),f.mn) + '/' + convert(varchar(2), f.dy) + '/' + convert(varchar(4),f.yr)))
between '01-apr-2001' and '31-mar-2008'
and left(m.mid,4)like 'BKS%'
and (vtype <> 'AFFS' and vtype <> 'SPRT')

Thanks so much any help would be greatly appreciated.

cfcProgrammer

 

[bborissov]

What you mean with can hold NULL?
You mean that the field value is NULL or you just have "NULL" putted in that field?
Also you can have troubles if the field can't be converted to numeric type, so try:

select  sum(convert(int, CASE WHEN ISNUMERIC(call+'e0') = 1
                                   THEN ISNULL(call,0)
                              ELSE 0 END)
.....

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[r937]

the field holding NULL should not cause any issue

aggregate functions like SUM simply ignore NULLs

i would use SUM(CAST(call AS INTEGER))

r937.com | rudy.ca

 

[gmmastros]

The 'call' column is varchar, but holds numerics?

I would probably write it this way...

[tt][blue]Sum(Case When IsNumeric(Call) = 1
Then Convert(Numeric(5), Call)
Else 0 End)[/blue][/tt]

IsNumeric returns 0 for NULL, but since you are summing, that's what you want anyway. This also protects you from other weird data in the column.

Ex:

Declare @Temp Table(Call VarChar(20))

Insert Into @Temp Values(NULL)
Insert Into @Temp Values('2')
Insert Into @Temp Values('two')

Select Sum(Case When IsNumeric(Call) = 1 
                Then Convert(Numeric(5), Call)
                Else 0 End)
From   @Temp

Technically, you can remove the ELSE part of case because without it, you will get NULL, which is ignored by the SUM aggregate. I usually leave it in because it makes the code a little more clearer for me.

Make sense?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[cfcProgrammer]

Thank you so much... I went with the last suggestion...

Your prompt responses are greatly appreciated...

Colleen

cfcProgrammer