CREATE TABLE acmast (accode n(6), acname c(50),opamt n(10,2)) SELECT acmast INSERT INTO acmast (accode,acname,opamt) VALUES (1, 'party name', 54200) CREATE TABLE sales (idno n(6), billno c(6), billdt d, accode n(6), qty n(7,2), amt n(10,2)) INSERT INTO sales (idno, billno , billdt , accode , qty , amt ) ; VALUES (1, 'aa/101', DATE(2011,4,04),1,22,509) INSERT INTO sales (idno, billno , billdt , accode , qty , amt ) ; VALUES (1, 'aa/102', DATE(2011,4,14),1,28,852) INSERT INTO sales (idno, billno , billdt , accode , qty , amt ) ; VALUES (1, 'aa/103', DATE(2011,4,24),1,18,654) if .t. SELECT sl.billno,sl.billdt, sl.accode; , ac.acname, ac.opamt; , sl.qty, sl.amt; , cast(0 as I) as idTemp ; FROM sales sl; LEFT JOIN acmast ac ON sl.accode=ac.accode INTO CURSOR tempcurs READWRITE update tempcurs set idTemp = recno() select acname, min(idTemp) as idtemp from tempCurs ; into cursor filtCurs ; group by 1 update tempcurs set opamt = 0 where idTemp not in ; (Select idTemp from FiltCurs) else SELECT sl.billno,sl.billdt, sl.accode; , ac.acname, cast (0 as n(15,2)) as opamt; , sl.qty, sl.amt; , cast(0 as I) as idTemp ; FROM sales sl; LEFT JOIN acmast ac ON sl.accode=ac.accode INTO CURSOR tempcurs READWRITE update tempcurs set idTemp = recno() select accode, min(idTemp) as idtemp from tempCurs ; into cursor filtCurs ; group by 1 update ts set opamt = ac.opamt; from acmast ac ; join tempcurs ts on ts.accode=ac.accode ; join filtcurs fs on ts.idtemp=fs.idTemp endif