locked
Runing Total RRS feed

  • Question

  • Dear Experts There are three numeric fields, fld1,fld2,fld3 and data in fields as Fld1   Fld2
    1      2
    6      8
    3      7
    0      3



    I want to get runing total of fld1+fld2 in fld3 as 3 17 27 30 Please help 






    Monday, November 21, 2005 10:38 AM

Answers

  • >> I want to get runing total of fld1+fld2 in fld3 as 3 17 27 30

    Actually, the easiest way to do this is to scan the table:

    SELECT (table_name)
    STORE 0 TO lnRunTotal
    SCAN
      lnRunTotal = lnRunTotal + fld1 + fld2
      REPLACE fld3 WITH lnRunTotal
    ENDSCAN
    Wednesday, November 23, 2005 11:40 AM

All replies

  • Is there some reason that:

    REPLACE ALL fld3 WITH (fld1 + fld2)

    does not work for you? Or is this a read-only SQL Cursor? If so then you can do it in the SQL like this:

    SELECT fld1, fld2, (fld1 + fld2) AS fld3 FROM wherever INTO CURSOR summary
     


    Monday, November 21, 2005 12:15 PM
  • Tariq,
    Running totals are generally calculated things but do require a 'sorting' field. ie:

    Create cursor myCursor (Fld1 i, Fld2 i)
    Insert into myCursor values (1,2)
    Insert into myCursor values (6,8)
    Insert into myCursor values (3,7)
    Insert into myCursor values (0,3)

    Select Sys(2015) as sorter,fld1,fld2 ;
      from myCursor ;
      into cursor crsRT nofilter

    Select
    a.fld1,a.fld2,;
      sum(Nvl(b.fld1+b.fld2,0))+a.fld1+a.fld2 as runTot ;
    from crsRT a ;
    left join crsRT b on a.sorter > b.sorter ;
    group by a.Sorter,a.Fld1,a.Fld2

    Monday, November 21, 2005 2:36 PM
  • >> I want to get runing total of fld1+fld2 in fld3 as 3 17 27 30

    Actually, the easiest way to do this is to scan the table:

    SELECT (table_name)
    STORE 0 TO lnRunTotal
    SCAN
      lnRunTotal = lnRunTotal + fld1 + fld2
      REPLACE fld3 WITH lnRunTotal
    ENDSCAN
    Wednesday, November 23, 2005 11:40 AM