>>I was doing a simple SQL with SUMs and got a numeric overflow error at random intervals. The code was:
>>
>>SELECT sum(lineitem) as linetotal, sum(iif(taxable, lineitem, 0)) as taxtotal from MyTable into cursor MyCursor
>>
>>The problem was the sum(iif(taxable, lineitem,0)) which worked about 95% of the time and gave me the numeric overflow error the remaining times. I presume I have to set the size of the numeric field, as must be done with variable length character fields, but don't know how.
>>
>>All help gratefully accepted.
>>
>>TIA
>>Barbara
>
>Hi Barbara,
>
> I'm not 100% sure, but I beleive that, using your example, MyCursor "inherits" it's structure from MyTable. Since you are SUMming in your SQL the SUM in any instance could be larger than the field size in MyCursor causing this error. You could direct your results to an array. You could also try extending your IIF to have the 0 be longer than the field in MyTable. ie. lineitem = N5 sum(iif(taxable, lineitem,0000000)) (7 0's).

I have had similar experiences with FoxPro. I agree with the zeros in the IIF(...). When generating a calculated field, FoxPro (and apparently also VFP) bases the field length on the first record calculated during the SQL Select. If you need decimals you will also need to add that to the zeroes (i.e. 000000.00)

Hope this helps!
Larry