[stackoverflow] [progress Openedge Abl] Openedge Progress 4gl Table Join

[BobNoobGuy]

I have TblA

Report
6998077
6998078
6998097
7062816


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

Continue reading...