>Sorry Cetin,
>
>Not getting the desired results. Could you highlight to me on setting null commands, and the implication and meaning of 'nvl', please.
>
>Thanks in advance.
>
>Regards,
>
>Steve.

Steve,
Rechecking your code it sounds you'd never get desired results correctly. I might be mistaken as you know original production data better than me. First what is NVL().

NVL() function is very similar to iif() and it returns second expression's value if first one is null. IOW it's short form of :

if Expr1 is NOT NULL give me Expr1 else Expr2

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) :

Consider your expression :

Sum(out.nqtyout)-Sum(out.nqtyret) As qtyout


There are 2 aggregate functions here. If one of them returns a NULL then the whole result should be NULL, right ? That's why VFP wouldn't let it.
OTOH consider this one :

Sum(out.nqtyout-out.nqtyret) As qtyout

A particular row(s) might have null causing it not to be included in sum but result is NULL only if all rows return a NULL value.

First one would raise the error while the second doesn't and assuming both didn't error (no nulls) their result is same :

  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).
I wasn't sure if your real intent is to get results like in your SQL (seemed wrong to me or as a result of actual tables' nature it might be working right)

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 crsAvlStock2

PS: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.
Cetin