Generate Serial No.

[alisaif]

Hi,

It is possible to create serial no. as a field in SQL-Select from 1 to the last record along with balance.

      Select actran.Reference, actran.cdate, actran.ccode, Code.Description,;
         actran.debit, actran.credit, Code.chead;
         FROM ;
         village!actran ;
         inner Join village!Code ;
         ON  actran.ccode = Code.ccode;
         WHERE  Code.chead == "PARTYC" And ;
         cdate<=dt1 Into Cursor tmpec Readwrite nofilter

      Sele cCode As cCode,;
         description as description,;
         sum(Debit) As Debit, ;
         sum(Credit) As Credit ;
         from Tmpec ;
         group By cCode, Description ;
         into Curs tempec Readwrite

*CAST(debit-credit AS N(10,2)) AS Balance

Thanks

Saif

 

[Mike Lewis]

When you say "serial number", do you mean a unique consecutive integer?

If so, one way to do that would be to do a separate pass of your result cursor, adding the serial number for each record.

Do your SELECT as normal, but include an extra integer field in the field list:

SELECT actran.Reference, actran.cdate, actran.ccode, Code.Description,;
   actran.debit, actran.credit, Code.chead, [b]CAST(0 AS INTEGER) AS SerialNo[/b] ;
  FROM ;
  ...
  Into Cursor tmpec Readwrite nofilter

and then:

SELECT tmpec
REPLACE ALL SerialNo WITH Recno()

Mike



__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[alisaif]

Thanks Mike, Perfect!!

 

[Olaf Doschke]

sum(Debit) As Debit, ;
sum(Credit) As Credit, ;
CAST(debit-credit AS N(10,2)) AS Balance

That can't work, because it would take each single debit and credit, while you group by some fields.

You should do

CAST(sum(Debit)-sum(Credit) AS N(10,2)) AS Balance

Or in the first query select actran.debit - actran.credit as balance, then in the second query sum(balance) as Balance. You don't necessarily need to cast, unless you want more decimal places.

Bye, Olaf.