Level Extreme platform
Subscription
Corporate profile
Products & Services
Support
Legal
Français
Sum and replace
Message
 
To
22/11/2006 09:26:04
General information
Forum:
Visual FoxPro
Category:
Coding, syntax & commands
Title:
Environment versions
Visual FoxPro:
VFP 9 SP1
OS:
Windows XP
Database:
Visual FoxPro
Miscellaneous
Thread ID:
01171725
Message ID:
01171749
Views:
18
It looks like you can do this in one SQL statement, but will keep your flow.
lnStockNumber = Thisform.text1.value
lnStockClassFrom = Thisform.text4.value
lnStockClassTo = Thisform.Text5.value

*/ First, get qualified stock items and their respective opening balances
*/ I am adding a few "place-holder" columns that will be updated during
*/ the scan loop.  Not all 3 are required, but will allow for you to see
*/ what I am getting values from.
SELECT ;
      str_nmbr,;
      stk_name,;
      stk_clas,;
      stk_code,;
      stk_opbl,;
      000000000 as Qty_In,;
      000000000 as Qty_Out,;
      000000000 as NewBalance;
   FROM ;
      stk_clas ;
   WHERE ;
          str_nmbr = lnStockNumber;
      AND between( stk_clas, lnStockClassFrom, lnStockClassTo );
   INTO ;
      CURSOR C_QualifiedStockItems READWRITE

*/ Now, use THIS cursor to loop and get sum of inventory in/out
select C_QualifiedStockItems
scan
    use in select( "C_Subtotal" )
    select ;
          sum( Trn_Qnty * iif( trn_typ = 1, 1, 0 )) as Qty_In,;
          sum( Trn_Qnty * iif( trn_typ= 44 or trn_typ=55, 1, 0 )) as Qty_Out;
       from ;
          trn_rqb3;
       where ;
              trn_rqb3.str_nmbr = C_QualifiedStockItems.str_nmbr;
          AND trn_rqb3.stk_code = C_QualifiedStockItems.stk_Code;
       into ;
          cursor C_Subtotal

    select C_QualifiedStockItems
    replace Qty_In with C_Subtotal.Qty_In,;
            Qty_Out with C_Subtotal.Qty_Out,;
            NewBalance with Stk_Opbl + C_Subtotal.Qty_In - C_Subtotal.Qty_Out

endscan 

*/ You should be good to go from here... with whatever adjustments you need.
Previous
Next
Reply
Map
View

Click here to load this message in the networking platform