Solved

Converting ForxPro code to PL/SQL

Posted on 2011-09-23
9
345 Views
Last Modified: 2012-06-27
I have this code needs to be converted into an efficient PL/SQL code. Below is the description of both and need FoxPro expert to tell me if my understanding of FoxPro logic is correct or not.

Background:

1) say CUST is a table CUST_CUSTOMER20110731 has 4.6M record consist column of (agent, agentgrp and others)
2) say CODE is a table CODE_AGENT_CODE has 59K distinct records, consist columns of (agent and agentgrp)
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

distinct length for agent in CUST table are only 4,5,6,7,8
distinct length for agent in CODE table are only 2,3,4,5,6,7,8

agent is unique in CODE
agent + agentgrp is unique in CODE table

##################  original FoxPro
sele 1 <--- cust_customer20110731 or CUST
set filter to empt(agentgrp) AND !EMPTY(agent)
USE h:\common\codetable\agent_code share in 2
for f_key = 8 to 2 STEP -1
    sele * from alia(2) where len(alltrim(agent))=f_key into curs u1
    index on agent tag agent
    sele 1
    replace agentgrp with u1.agentgrp for seek(left(alltrim(agent),F_key),sele('u1'))
next
##########  end original FoxPro code ###############
 

!!!!!! My New understanding of the logic is:

Loop f_key when f_key = 8,7,6,5,4,3,2
     update CUS set agentgrp = CODE.agentgrp if substr(CUS.agent,f_key) = CODE.agent and CUS.agentgrp is not null
End loop
 
I need someone in FoxPro to convert the FoxPro code into a SQL (preferable PL/SQL)

THank you,


0
Comment
Question by:tindavid
  • 4
  • 4
9 Comments
 
LVL 41

Expert Comment

by:pcelba
ID: 36592016
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
0
 

Author Comment

by:tindavid
ID: 36596712
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
0
 
LVL 41

Expert Comment

by:pcelba
ID: 36597944
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

Open 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

Open in new window

0
 
LVL 31

Expert Comment

by:awking00
ID: 36599926
>>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.
0
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 

Author Comment

by:tindavid
ID: 36707629
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 ?

0
 
LVL 41

Expert Comment

by:pcelba
ID: 36708562
& 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).
0
 

Author Comment

by:tindavid
ID: 36866849
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




0
 
LVL 41

Accepted Solution

by:
pcelba earned 500 total points
ID: 36890675
Answer 1:  SELE 2 (non abbreviated form is SELECT 2) refers to the workarea No. 2. If there would be some table open in this workarea then the table columns are accessible without explicit alias prefix. There is no table open in workarea 2 in your case because all tables were closed by "close table all" command. The "use &ostemp" command  immediately following SELE 2 opens &ostemp (= temppath + "os_temp").

Answer 2: SELE 1 refers to the workarea No. 1 where is no table open at the moment.

Answer 3 is correct but the table isn't deleted just some of its recotds are marked for deletion.

BTW, as EE rules say you should ask new questions because we are not discussing the original subject.
0
 

Author Closing Comment

by:tindavid
ID: 36908088
Even the response is nort full answering my original question, but helpful for me to solve my understanding of FoxPro a bit.
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now