Openedge Progress 4GL Table join

Question

进度4GL大师的任何建议，看看下面的4GL代码块，看看它是否做得过分了？ 可以这样做以便更容易遵循/更具可读性吗？

我有 TblA

Report  
6998077 
6998078 
6998097 
7062816 

和TblB

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

在我看来，我有一个复杂的进度 4GL 循环：

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.

那么我的进度代码的输出是：

+------------+---------+---------+---------+
| 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 |
+------------+---------+---------+---------+

我对Progress 4GL的了解非常有限。这段代码似乎做得太过分了吗？能更简单吗？

我来自SQL背景。所以在SQL中，我可以相当快速和轻松地解决这个问题。我的意思是，所有这些进度块代码基本上只是说，如果在 LoopB 中为 NULL，则说"来自 LoopA"，否则说"来自 LoopB">

这是我提出的SQL等效项：

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

进度 4GL大师的任何建议可以看看上面的 4GL 代码块，看看它是否做得过分了？ 可以做到更容易理解/阅读吗？

但我对任何答案持开放态度，如果这是在 4GL 中编码以实现最终结果的正确方法，那么我没关系。

谢谢谢谢

Answer 1

您似乎正在经历两次 TblA 和 TblB。相反，请为每个 TblB 创建一个 TblTemp。然后浏览 TblA 以查看它是否与 TblTemp 记录匹配。您可以将临时表逻辑移出到其自己的过程中。

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.

Answer 2

你可以写如下内容

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.

我不知道这是否已经有所帮助，即。 如果你可以跳过临时表。至少对于一个 TblA 记录，您不应看到相同的 TblB 记录两次(否则对于带有TblB.Source = TblB.Report的记录，就会发生这种情况)。 如果我在第一个for each TblB中用TblB.Source替换TblA.Report，用TblB.Report替换TblA.Report，那么唯一的区别似乎是TblTemp.cTypeOfRec和TblTemp.ID所以你应该能够缩短代码。