Openedge Progress 4GL Table join

Question

any advise from progress 4GL guru to take a peek on the block of 4GL code bellow and see if it is overly done ? can it be done so that it is easier to follow / more readable?

I have TblA

And TblB

+-----------+------------+-----------+
| ID        |      Source|    Report |
+-----------+------------+-----------+
|   4976117 |    6998077 |   6998077 |
|   4976118 |    6998078 |   6998078 |
|   4976137 |    6998097 |   6998097 |
|   5107798 |    7062816 |   6998078 |
|   5107799 |    7062816 |   6998097 |
+-----------+------------+-----------+

and I have, in my opinion, a complicated Progress 4GL loop :

def temp-table TblTemp no-undo
field cTypeOfRec     as char
field Report         as int
field Source         as int
field ID             as int 
index key is unique primary Report Source ID.

procedure SOOptimize:
  output stream dOut1 to value("/OutPut.txt").
  export stream dOut1 delimiter "|"
    "Report"
    "Source"  
    "ID".  
  for each TblA no-lock
           on error undo, return error on stop undo, return error:
    for each TblB no-lock where
             TblB.Source = TblA.Report
             on error undo, return error on stop undo, return error:

      find TblTemp exclusive-lock where
           TblTemp.SrcPltSeq = TblA.Report and
           TblTemp.RptPltSeq = TblB.Report and
           TblTemp.ID        = TblB.ID
           no-error.
      if NOT available TblTemp
      then do:
        create TblTemp.
        assign
          TblTemp.cTypeOfRec = "From LoopA"
          TblTemp.SrcPltSeq  = TblA.Report
          TblTemp.RptPltSeq  = TblB.Report
          TblTemp.ID         = TblB.ID.
      end. 
    end.
    for each TblB no-lock where
             TblB.Report = TblA.Report
             on error undo, return error on stop undo, return error:  
      find TblTemp exclusive-lock where
           TblTemp.SrcPltSeq = TblB.Source     and
           TblTemp.RptPltSeq = TblA.Report     and
           TblTemp.ID        = TblB.ID
           no-error.

      if NOT available TblTemp
      then do:
        create TblTemp.
        assign
          TblTemp.cTypeOfRec = "From LoopB"
          TblTemp.SrcPltSeq    = TblB.Source     
          TblTemp.RptPltSeq    = TblA.Report
          TblTemp.ID           = TblB.PltSrcSeq.        
      end.
    end.
  end.
  for each TblTemp no-lock
  on error undo, return error on stop undo, return error:
    export stream dOut1 delimiter "|"
      TblTemp.      
  end.  
end procedure.

Then the output of my progress Code is:

+------------+---------+---------+---------+
| cTypeOfRec | Source  | Report  |   ID    |
+------------+---------+---------+---------+
| From LoopA | 6998077 | 6998077 | 4976117 |
| From LoopA | 6998078 | 6998078 | 4976118 |
| From LoopB | 7062816 | 6998078 | 5107798 |
| From LoopA | 6998097 | 6998097 | 4976137 |
| From LoopB | 7062816 | 6998097 | 5107799 |
+------------+---------+---------+---------+

I have a very limited knowledge of Progress 4GL. Does this code seems overly done? can it be simpler?

I come from SQL background. So in SQL, I can solve this rather quickly and easily. And what I mean by that is, ALL this Block of Progress code is doing basically just saying, say "From LoopA" if NULL in LoopB, otherwise say "from LoopB"

Here is the SQL equivalent that I come up with:

Select 
case when B.ID is null then 'From LoopA'  
else B.cTypeOfRec 
End "cTypeOfRec"
, A.*  
from #TblTemp A 
left join (
    select A.*, 'From LoopB'  "cTypeOfRec" from ( select * from #TblTemp)A
    left join (
        select B.Source, A.Report, B.ID  from #TblA A
        Inner join #TblB B
        on B.Report=A.Report)B
    on A.Source = B.Report
    where B.Source is null) B
on A.Report=B.Report
and a.ID = b.ID 
and a.Source= b.Source
order by A.Report
, case when B.ID is null then 'From LoopA'  
else B.cTypeOfRec 
End

any advise from progress 4GL guru to take a peek on the block of 4GL code above and see if it is overly done ? can it be done so that it is easier to follow / readable?

But I am open to any answer, if that is the proper way to code in 4GL to achieve the end result then I am ok.

Thank you Thank you

score 1 · Answer 1 · answered Feb 28 '17 at 03:03

You seem to be going through TblA and TblB twice. Instead, create a TblTemp for each TblB. Then go through TblA to see if it matches a TblTemp record. You can move the temp table logic out to its own procedure.

def temp-table TblTemp no-undo
field cTypeOfRec     as char
field RptPltSeq      as int
field SrcPltSeq      as int
field ID             as int 
index key is unique primary RptPltSeq SrcPltSeq ID.

define stream dOut1.

run SOOptimize.

procedure SOOptimize:
  output stream dOut1 to value("OutPut.txt").
  export stream dOut1 delimiter "|"
    "Report"
    "Source"  
    "ID".  

  for each TblB no-lock:
    run updateTempRec (input "From LoopB", input TblB.Source, input TblB.Report, input TblB.ID). 
  end.

  for each TblA no-lock:
    run updateTempRec (input "From LoopA", input TblA.Report, input TblA.Report, input 0).      
  end.

  for each TblTemp no-lock:
    export stream dOut1 delimiter "|"
      TblTemp.      
  end.  
end procedure.

procedure updateTempRec:
    define input parameter pcType as character no-undo.
    define input parameter piSrc as integer no-undo.
    define input parameter piRpt as integer no-undo.
    define input parameter piID as integer no-undo.

    find first TblTemp where
      TblTemp.SrcPltSeq  = piSrc and
      TblTemp.RptPltSeq = piRpt
      no-error.

    if available(TblTemp) then
      TblTemp.cTypeOfRec = pcType.
    else
      if piID <> 0 then
      do:
        create TblTemp.

        assign
          TblTemp.cTypeOfRec = pcType
          TblTemp.SrcPltSeq  = piSrc     
          TblTemp.RptPltSeq  = piRpt
          TblTemp.ID         = piID.
      end.
end procedure.

score 0 · Answer 2 · edited Mar 31 '17 at 13:18

You could write something like the following

for each TblA no-lock,
  each TblB no-lock where
           TblB.Source = TblA.Report
        or TblB.Report = TblA.Report
           on error undo, return error on stop undo, return error:
  /* ... */
end.

I don't know if this already helps, ie. if you could skip the temp-table. At least for one TblA record you shouldn't see the same TblB record twice (which otherwise would occur for records with TblB.Source = TblB.Report). If I replace TblA.Report with TblB.Source in the first for each TblB and TblA.Report with TblB.Report then the only differences seem to be TblTemp.cTypeOfRec and TblTemp.ID so you should be able to shorten the code.

Openedge Progress 4GL Table join

2 Answers2