进度4GL嵌套块显示相关数据

Question

首先，我是非常新的进度4GL，仍然试图得到我的头周围嵌套为每个块的工作原理。我有以下两个表，我从中获取信息，ivc_header和ivc_mchgs:

ivc_header
    invoice_nbr     | sold_to_cust_nbr | sold_to_cust_seq | invoice_amt
        1000051     |        70        |        0         |   $1,000
        1000049     |        70        |        1         |   $1,500
        1000010     |       310        |        0         |   $2,000
        1000011     |       310        |        1         |   $2,500
ivc_mchgs
    invoice_nbr | line_nbr | misc_seq_nbr | extension
       1000051  |     1    |      1       |   $300
       1000051  |     1    |      2       |   $200
       1000051  |     2    |      1       |   $100
       1000049  |     1    |      1       |   $400
       1000049  |     1    |      2       |   $100
       1000049  |     2    |      1       |   $150
       1000010  |     1    |      1       |    $50
       1000010  |     1    |      2       |    $50
       1000010  |     2    |      1       |   $100
       1000011  |     1    |      1       |    $75
       1000011  |     1    |      2       |    $80
       1000011  |     2    |      1       |    $90

仅供参考，ivc_header的主键是invoice_nbr, ivc_mchgs的主键是由invoice_nbr, line_nbr和misc_seq_nbr组成的复合键。外键为invoice_nbr。

关于数据的注意事项，ivc_mchgs中的信息是发票line_nbr中的杂项费用。

我想要得到的是sold_to_cust_nbr + sold_to_cust seq的总发票和扩展。在做了一些研究之后，我决定将总数放在变量中，而不是使用Progress内置的ACCUMULATE函数。

下面是我的代码:

DEFINE VARIABLE cCustNum AS CHARACTER           NO-UNDO.
DEFINE VARIABLE dInvoiceSubTotal AS DECIMAL     NO-UNDO.
DEFINE VARIABLE dSurchargeTotal AS DECIMAL      NO-UNDO.
FOR EACH ivc_header 
    NO-LOCK
    WHERE (ivc_header.sold_to_cust_nbr = "000070")
          OR (ivc_header.sold_to_cust_nbr = "000310")
    BREAK BY ivc_header.sold_to_cust_nbr:
    IF FIRST-OF(ivc_header.sold_to_cust_nbr) THEN
        ASSIGN dInvoiceSubTotal = 0.
        ASSIGN dInvoiceSUbTotal = dInvoiceSUbTotal + ivc_header.invoice_amt.
    IF LAST-OF(ivc_header.sold_to_cust_nbr) THEN
        DISPLAY ivc_header.sold_to_cust_nbr + ivc_header.sold_to_cust_seq   FORMAT "x(9)" LABEL "CustNum"
            dInvoiceSUbTotal LABEL "SubTotal".
FOR EACH ivc_mchgs WHERE ivc_header.invoice_nbr = ivc_mchgs.invoice_nbr 
    NO-LOCK
    BREAK BY ivc_mchgs.invoice_nbr:
    IF FIRST-OF(ivc_mchgs.invoice_nbr) THEN
        ASSIGN dSurchargeTotal = 0.
        ASSIGN dSurchargeTotal = dSurchargeTotal + ivc_mchgs.extension.
    IF LAST-OF (ivc_mchgs.invoice_nbr) THEN
        DISPLAY
                dSurchargeTotal LABEL "Surcharge".
    END.
END.

此代码将通过sold_to_cust_nbr + sold_to_custrongeq为我提供总invoice_amt，并通过invoice_nbr对扩展进行汇总。我不知道如何做的是获得sold_to_cust_nbr + sold_to_custrongeq的总扩展。

任何帮助都是感激的。

谢谢

Answer 1

我想你可能没有意识到你可以指定多个BY子句。

现在你可能想要像这样编写内部的FOR EACH:

FOR EACH ivc_mchgs NO-LOCK WHERE ivc_header.invoice_nbr = ivc_mchgs.invoice_nbr 
    BREAK BY ivc_mchgs.invoice_nbr
          BY ivc_mchgs.sold_to_cust_nbr
          BY ivc_mchgs.sold_to_cust_seq:
    IF FIRST-OF(ivc_mchgs.invoice_nbr) THEN
        ASSIGN dSurchargeTotal = 0.
    IF FIRST-OF(ivc_mchgs.sold_to_cust_nbr ) THEN ...

等。

Answer 2

假设您需要发票和汇总表的交换总额，那么您可以这样做:

DEFINE VARIABLE cCustNum AS CHARACTER           NO-UNDO.
DEFINE VARIABLE dInvoiceSubTotal AS DECIMAL     NO-UNDO.
DEFINE VARIABLE dSurchargeTotal AS DECIMAL      NO-UNDO.
DEFINE VARIABLE dSurchargeSubTl AS DECIMAL      NO-UNDO.
FOR EACH ivc_header 
    NO-LOCK
    WHERE (ivc_header.sold_to_cust_nbr = "000070")
          OR (ivc_header.sold_to_cust_nbr = "000310")
    BREAK BY ivc_header.sold_to_cust_nbr:
    IF FIRST-OF(ivc_header.sold_to_cust_nbr) THEN
        ASSIGN dInvoiceSubTotal = 0
               dSurchargeSubTl = 0.
    ASSIGN dInvoiceSUbTotal = dInvoiceSUbTotal + ivc_header.invoice_amt.
    IF LAST-OF(ivc_header.sold_to_cust_nbr) THEN   
        DISPLAY ivc_header.sold_to_cust_nbr + ivc_header.sold_to_cust_seq  FORMAT "x(9)" LABEL "CustNum"
            dInvoiceSUbTotal LABEL "SubTotal"
            dSurchargeSubTL LABEL "Srchg SubTl".
    FOR EACH ivc_mchgs WHERE ivc_header.invoice_nbr = ivc_mchgs.invoice_nbr 
        NO-LOCK
        BREAK BY ivc_mchgs.invoice_nbr:
        IF FIRST-OF(ivc_mchgs.invoice_nbr) THEN
            ASSIGN dSurchargeTotal = 0.
        ASSIGN dSurchargeTotal = dSurchargeTotal + ivc_mchgs.extension.
        IF LAST-OF (ivc_mchgs.invoice_nbr) THEN DO:
            DISPLAY dSurchargeTotal LABEL "Surcharge".
            ASSIGN dSurchargeSubTl = dSurchargeSubTl + dSurchargeTotal.
        END.
    END.
END.

优雅的方式是使用左外连接组合两个查询，并使用ACCUMULATE函数，但这应该可以工作。