ShowResults(.null., 10) ShowResults(1, 10) function ShowResults lparameters expr1, expr2 ? expr1, expr2 if NOT IsNull(expr1) myVal = expr1 else myVal = expr2 endif ? myVal ? iif(NOT IsNull(expr1), expr1, expr2) ? NVL(expr1,expr2)For your SQL it says columnxx doesn't accept NULL because (don't really know how I could express this in plain English) :
20 - 10 = 10 30 - 5 = 25 +-------------- 50 - 15 = 35 sum(20,30) - sum(10,5) = sum(20-10,30-5)And one other expression in yours was very similar to this. Below is a test case. Check results. lnFix is there to workaround fieldsize if ever happens due to field type and VFP version (here wouldn't occur but anyway put as Hilmar pointed out).
Create Cursor stockbook ; (ddate d, citemname c(10), citemid i, nqtyin i, ; ncost i, nqtydel i) Create Cursor stockout ; (citemid i,nqtyout i,nqtyret i) For ix = 1 To 10 Insert Into stockbook Values ; (Date()-10+m.ix, 'Item1', 1, 100, m.ix*100,10) Insert Into stockbook Values ; (Date()-10+m.ix, 'Item2', 2, 100, m.ix*100,10) Endfor For ix=1 To 5 Insert Into stockout Values (2,60,10) Endfor lcItemName = '' && Get all lnFix = 0x7FFFFFFF Select bk.ddate,bk.citemname, bk.citemid, bk.nqtyin,; bk.ncost, Sum(Nvl(out.nqtyout-out.nqtyret,lnFix*0)) As qtyout ,; (bk.nqtyin-Sum(bk.nqtydel)) - ; (Sum(Nvl(out.nqtyout-out.nqtyret,lnFix*0))) As avlbal; FROM stockbook bk ; LEFT Outer Join stockout out ; ON bk.citemid = out.citemid; WHERE bk.citemname = lcItemName ; GROUP By bk.citemid; ORDER By bk.citemid; INTO Cursor curAvlStock1 Select bk.ddate,bk.citemname, bk.citemid, bk.nqtyin,bk.ncost, ; Sum(Nvl(out.nqtyout,lnFix*0))-Sum(Nvl(out.nqtyret,lnFix*0)) As qtyout ,; (bk.nqtyin-Sum(bk.nqtydel)) - ; (Sum(Nvl(out.nqtyout,lnFix*0)) - ; Sum(Nvl(out.nqtyret,lnFix*0))) As avlbal; FROM stockbook bk ; LEFT Outer Join stockout out ; ON bk.citemid = out.citemid; WHERE bk.citemname = lcItemName ; GROUP By bk.citemid; ORDER By bk.citemid; INTO Cursor curAvlStock Select bk.citemname, bk.citemid, ; sum(bk.nqtyin) As nqtyin, ; sum(bk.nqtydel) As nqtydel, ; sum(bk.nqtyin-bk.nqtydel) As qtyin, ; sum(bk.ncost) As ncost ; FROM stockbook bk ; WHERE bk.citemname = lcItemName ; GROUP By bk.citemid; ORDER By bk.citemid; INTO Cursor curIn nofilter Select bk.*, ; sum(out.nqtyout-out.nqtyret) As qtyout, ; bk.qtyin - Sum(Nvl(out.nqtyout-out.nqtyret,lnFix*0)) As avlbal ; from curIn bk ; left Join stockout out ; on bk.citemid = out.citemid ; group By bk.citemid ; into Cursor crsAvlStock2PS:Note that with a grouping like this including ddate is meaningless unless intention is to get last stockbook date. VFP8 wouldn't let it that way unless enginebehaviour is set to 70.