Openedge Progress 4GL表连接

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

在我看来，我有一个复杂的Progress 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，就说"From LoopA“，否则就说"from 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中将TblA.Report替换为TblB.Source，将TblA.Report替换为TblB.Report，那么唯一的区别似乎是TblTemp.cTypeOfRec和TblTemp.ID，因此您应该能够缩短代码。