You need to control the length of numeric in your calculations. First determine the maximum length. Then try to trick sql-select.
Let us suppose the maximum length is 10, then this may help:
.....
0000000000+(kc.quantity*short_kitQty) AS qtyReq ...
>I am trying to debug a numeric overflow error, which occurs very intermittently. The code I am working with does a select into a temp dbf. One of the select list items is:
>
>(getCompAvailQty(p1.stock_id,kc.dEdition)) AS qtyAvail
>
>Then later:
>
> Replace qtyAvail WITH qtyAvail - otherQty
>
>where the numeric overflow occurs.
>
>The previous developer had harcoded the result set as:
>
>'into dbf kitcomponents'
>
>So I removed that code and changed it so that it would write into a temp table named with sys(2015), and then I erase the temp table.
>So now, I can't look at this result set file to see what the data looked like. Good change in terms of multi-user practice - bad for me trying to debug this. So, I looked at some old copies of the kitcomponents table lying around (in different folders for different contexts of the same app) and found that in some cases qtyavail was numeric(11), and in one, (with overflow data) it was numeric(1).
>
>In my tests from the command line, I found that if I issue:
>select sometable.*, 1 as one from sometable into dbf result
>Then sometable.one is numeric(1)
>
>If I:
>select sometable.*, 10 as ten from sometable into dbf result
>
>then sometable.ten is numeric(2) (and so on)
>
>It seems to figure out the length in a consistent way in the case of a literal.
>
>BUT, in the case when I passed in my own FUNCTION that returned a value, it always made the output field numeric(12) even when the largest return value was 1, or, it made the output field larger than numeric(12) if the largest output was larger than that
>
>Yet, I have a few of these old 'kitcomponent.dbf's 'lying around' that created qtyavail as numeric(1).
>
>So my question is, how does vfp determine the length of the output field, and, is it consistent in doing this?
>
>TIA
>
>
>
>
>
>
>
>
>
>
>
>
>
> lcKitComponentTempFile='t'+ sys(2015) +'.dbf'
> Select p1.stock_id, p1.item_id, p1.i_descrip, p1.pod, p1.pod_status, p1.shelf_loc, kc.quantity, kc.dEdition,;
> (kc.quantity*short_kitQty) AS qtyReq, ;
> (getCompAvailQty(p1.stock_id,kc.dEdition)) AS qtyAvail ;
> FROM pickinv1 p1, kit_child kc;
> WHERE p1.stock_id = kc.stock_id AND ;
> kc.parent_st = kitStock_id ;
> INTO DBF (lcKitComponentTempFile)
> Use
> Select 0
> Use (lcKitComponentTempFile) exclu alias kitComponents
> lnKCReccount=reccount('KitComponents')
>
> If lnKCReccount > 0
> Select kitComponents
> Go TOP
>
> Do WHILE NOT EOF("kitComponents")
> otherQty = getQtyReserved(item_id, stock_id)
> If otherQty > 0
> Replace qtyAvail WITH qtyAvail - otherQty
> Endif
> Skip in kitComponents
> Enddo
>
>
>
>
>
