Converting ForxPro code to PL/SQL

3) CODE table has only 20K records can be found in CUST table ( when CUST.agent = CODE.agent)
4) CUST table  has only 96K records that has the "agent" column being identified in CODE table

Did you try to compare without leading and trailing spaces? Means "when ALLTRIM(CUST.agent) = CODE.agent".


The code updates "agentgrp" values in CUST table based on "agent" values. The loop 8, 7, ... 2 is not necessary because it should be possible to do everything at once if the PL/SQL compares two value exactly and not in the length of the shorter string.

Your update command could look like this one:

update CUST set agentgrp = CODE.agentgrp
  from CUST
  inner join CODE ON CODE.agent = CUST.agent
  where CUST.agentgrp IS NULL

If the agent column contains leading spaces then you have to add trimming into the join expression (I am just not sure if Oracle allows it):

update CUST set agentgrp = CODE.agentgrp
  from CUST
  inner join CODE ON TRIM(both CODE.agent) = TRIM(both CUST.agent)
  where CUST.agentgrp IS NULL

Hi pcelba,

Firstly I need to understand the logic of Foxpro before converting to PL/SQL.

May be I should elborate more aboy the data in CUS, this table has 2 important columns needs to be referenced and update (agentgrp and agent), based on the foxpro code, the table will be update if agentgrp is null and agnet is not null. The key condition to have the agentgrp to be updated is (or case) :

1) CUS.agent = CODE.agent  
2) substr(CUS.agent,1,f_key) = COD.agent
    Why: I found that the CUS.agent might have the exact agent value as defined in CODE but somehow substr(agent,1,f_key) does have same value as in CODE.agent, that case, the agentgrp can be updated to the sameCODE.agentgrp..

     This is main reason for this update.

We have to assume the data in all tables are already imported into Oracle DB, leading/tailing space is not an issue at all.

I need to now if my goic is correct as what FoxPro is programmed.

I also found your inner join is interesting as you can see I have to loop 7 time in order to update the CUS table.

Many thanks

Now I see it, my single UPDATE command cannot work exactly as the FoxPro code. I'll try to explain the loop functionality.

Let suppose we have following values in COD.agent:
BB
BBBB
BBBBBB

and CUS.agent could be following:
BB
BBB
BBBB
BBBBB

The  substr(CUS.agent,1,f_key) just compares "f_key" characters from left against COD.agent.

The loop just groups different COD.agent lengths and splits the final REPLACE command into several parts, each one for given "f_key" length from left of the CUS.agent value. If it does not find a match for longer string then it attempts truncated variants.

It means BBBBB in CUS is processed when f_key = 4 because there is no BBBBB in COD.
BBBB is also processed when f_key = 4 and (it finds the record in COD).
BBB does not have matching record in COD, so it has to wait for truncation variant BB.
Etc.

Comments in your code would also help:

*-- Select CUST workarea
sele 1 && <--- cust_customer20110731 or CUST

* Restrict CUST records set to Empty agentgrp and defined agent
set filter to empt(agentgrp) AND !EMPTY(agent)  

*-- Open COD table in workarea 2
USE h:\common\codetable\agent_code share in 2

*-- loop for f_key values from 8 to 2 step -1
for f_key = 8 to 2 STEP -1
    *-- Extract all COD records having agent of the given length
    sele * from alia(2) where len(alltrim(agent))=f_key into curs u1
    *-- create an index to allow SEEK function
    index on agent tag agent
    *-- Select CUST workarea
    sele 1
    
    *-- Update CUS.agentgrp WITH COD.agentgrp if left f_key characters from CUS.agent match to the COD.agent of f_key length
    *-- SEEK returns True if the match is found
    replace agentgrp with u1.agentgrp for seek(left(alltrim(agent),F_key),sele('u1'))
    
next

Select allOpen in new window

So, the final PL/SQL UPDATE should also be processed in a loop from the longest agent to the shortest:

I don't know PL/SQL loop syntax and I also don't know how to use loop parameter in PL/SQL command...

FOR @i = 8 TO 2 STEP -1

update CUST set agentgrp = CODE.agentgrp
  from CUST
  inner join CODE ON CODE.agent = LEFT(CUST.agent, @i)
  where CUST.agentgrp IS NULL
    and LENGTH(CODE.agent) = @i

NEXT

Select allOpen in new window

>>agent is unique in CODE
agent + agentgrp is unique in CODE table<<
I assume that one of these should be CUST?

>>I need someone in FoxPro to convert the FoxPro code into a SQL<
I think what you need is for someone familiar with FoxPro to define what the code accomplishes, then someone familiar with PL/SQL to provide code that accomplishes the same thing. Perhaps you could provide some sample data before the update and what the FoxPro results are.

One quick question:

What is the difference bewteen:

use "&abcd"

AND

user &abcd

There has been a foxpro program that use:

use "&abcd"  but the abced is not defined in the program, i Guess it might be an input value

Correct ?

& character menas macro substitution and  &abcd  is substituted by the abcd variable content in your command.

Thus  use "&abcd" allows spaces in the filename which should be open by this command,
use &abcd  does not allow spaces in the filename because they are handled as separators in xBase language.

The recommended way is to use name expression instead of macro:

use (abcd)

And yes, abcd could be an input value but th einput command should be visible somewhere (not necessarily in the PRG file where is the use &abcd command).

Hi Pcelba,

As FoxPro expert,  thank you for your advise,  Before closing this question, one more syntax to ask for your help  )as to me it is very hard to understand the FoxPro code.)  for example

When I see code like these (more oe less close to original code):

***************************************************************
use &datecode_db share in 1
datecode = gomonth(a.targetdt,0)
use in 1

customerbase  = customerpath + "customer" + dtos(datecode-1)
osbase        = ospath + 'os' + left(dtos(datecode),6)
fosbase       = ospath + 'osbase'
ostemp              = temppath + "os_temp"


use "&f_segment_db"
repl start_dt with datetime() for program = alltrim(tprg(r,1))
close table all

tablename = "DW3G.SUP_MEM_MONTHLY_" + left(dtos(datecode),6) + " "
h=sqlconnect('ABCD','XYZX','XYZX')

if h>0
      clear
      @ 20, 3 say "Server connected ..."  
      Action = "SELECT SMM_ACCT_NO as ac, "+;
                   "SMM_EXPIRY_DATE as expdate, "+;
                   "SMM_PE_CODE as pe_code "+;
                   "FROM "+tablename

      result=sqlexec(h,Action,"supremedata")
      @ 22, 2 say result

      if result<1
            =sqldisconnect(h)
            messagebox("Datawarehouse NOT Updated.  Try Again Later!",+0+48)
            return .f.
      endif

else

      =sqldisconnect(h)
      messagebox("Connection Error!",+0+48)
      return .f.

endif

=sqldisconnect(h)

sele supremedata
copy to &ostemp

close table all


use "&customerbase" share in 1

sele ac, "  " as pe_code, {} as expdate, status from alia(1) where inlist(subs(class,2,2),"PP","PB","NP","NB") ;
       into curs u1
copy to &osbase
close table all

sele 2                                 [ Question 1: What table is this SELE 2 refer to ?]
use &ostemp excl
index on ac tag ac
repl all pe_code with iif(isnull(pe_code),'',pe_code), ;
             expdate with iif(isnull(expdate),'',expdate)

sele 1                                [Question 2:  I gues SELE 1 is refer to &customerbase Correct ?]
use &osbase excl
index on ac tag ac
repl pe_code with b.pe_code, expdate with TTOD(b.expdate) for seek(ac,2)

DELETE ALL FOR EMPTY(expdate) AND status='P'   [ Question 3 :I guess the table to be deleted is &osbase, Correct]
PACK

*****************************************************************
Now I want to know what is table for "sele 2" and looks like &customerbase is  for "sele 1"

Am I correct on the [Question ??: ] questions ?

Thank you,

David

Even the response is nort full answering my original question, but helpful for me to solve my understanding of FoxPro a bit.