How can I convert Visual-Foxpro to Oracle

Question

How can I convert this Visual-Foxpro code to oracle SQL please?

PUBLIC CO
co=0
SELECT 1
USE fxt
INDEX ON act TO aa

SELECT 2
USE bl_sum
INDEX ON act TO bb
GO TOP
DO WHILE NOT eof ()
   STORE act To aci
   SELECT 1
   GO top
   fatcost=0
   DO WHILE NOT eof() 
      IF fxt->act=ac1 then 
         fatcost = fatcost + fxt->cost
      ENDIF
      SELECT 1
      skip
   ENDDO

   SELECT 2
   REPLACE Scost WITH fatcost
   SKIP
enddo

Answer 1

Wow... old code, but ok. What it appears you are doing is getting a sum of all costs from the fxt table on a per "acl" value from the bl_sum table and storing that total in the bl_sum "scost" column.

This would be a correlated update and would look something like...

UPDATE bl_sum bl
   SET bl.Scost = ( select SUM( f.cost )
                       from fxt f
                       where bl.acl = f.act )

Looking at the right-side of the = is a simple select query getting the sum of the "cost" column from fxt table (alias f) where the act is the same as whatever the acl is from the bl_sum table (alias bl).

So, think of it like this from VFP. For each outer record in your do while loop of the BL_Sum table, inside that you are getting each record from fxt and getting the sum of the cost. Once finished, you put that total back on the bl_sum table and move to the next bl_sum record.

FEEDBACK

@oldarf, thats the point of writing queries. This single query DOES everything that your code does. I've written in Fox database since it was FoxBASE back in the 80's, all the way through the life product of VFP9. So I know the language. I also know SQL and querying. Otherwise, you would have to do an entire stored procedure. Learning more efficient ways will help you grow too. If you need assistance with converting this and other FoxPro / Visual FoxPro code, I can help, but would do that via other chat means, or you providing me an email to get back to you on.