4GL after row/next field statement

4GL after row/next field statement

Post by Lester Knuts » Thu, 04 Nov 1993 14:18:10



Hello all,

This is a question I received from a member of our
Informix User Group and I am posting it for them.  Please
send any replys back to me and I will fax then to Kelli.

Thanks - Lester

#############################################################################

#         Advanced DataTools Corporation        Voice: 703-256-0267         #
#         Providing Informix Database Tools and Consulting                  #
#############################################################################

Posted for: Kelli Buccelli, ARS

Summary:
Need a way for the after row /next field statement in an input
array to return to the same row instead of moving to the next row.

Problem:
In a 4GL program input array, we are using the after row statement
to test if all the values are correct.  The row contains 4 numbers
one or more of which must be greater then 0.  All four cannot be 0.
If all four are 0 we want to go the the first field on the row and
prompt the user to re-enter a valid row.  The problem is that the
next field statement in an after row statement goes to the next
field on the next row leaving a row of incorrect values.  The following
is an example 4gl program, screen and sql.  This problem has been
tested with 4GL 4.0, 4.1, 1.1 and RDS 4.1.  We have a workaround
using the after field statement and duplicating a lot of code.
Any suggestion on how to do it with the after row statement?

-------cut here rowtest.4gl -------
## After Row test:
# Need a way in 4GL to return to the same row if the whole row
# does not meet some input requirement.  The following is
# an example where a row is 4 numbers and on of the four numbers
# must be > 0.  Currently the after row clause to check this
# moves on the the next row.

database rowtest

main

define         p_f1 array[50] of record like f1.*,
        p_rec        int,
        s_rec        int
open window win1 at 5,5
        with form "rowtest" attribute (border )

call set_count(0)
input array p_f1 from s_f1.*
        before row
                let p_rec = arr_curr()
                let s_rec = scr_line()
                error "Before Row p_rec = ",p_rec, " s_rec = ", s_rec
        after row
                let p_rec = arr_curr()
                let s_rec = scr_line()
                if ( p_f1[p_rec].col1 = 0 and p_f1[p_rec].col2 = 0
                and p_f1[p_rec].col3 = 0 and p_f1[p_rec].col4 = 0 ) then
                        error "Invalid Row one entry must be > 0"
                        next field col1
                        ################################################
                        # this is the problem. the program executes this
                        # statement and goes to col1 on the next row
                        ################################################
                end if
                error "After Row p_rec = ",p_rec, " s_rec = ", s_rec
                sleep 2
end input

end main
------------cut here rowtest.per ------------------
database rowtest
screen size 24 by 80
{
             After Row test Table
[f000       ] [f001       ] [f002       ] [f003       ]
[f000       ] [f001       ] [f002       ] [f003       ]
[f000       ] [f001       ] [f002       ] [f003       ]
[f000       ] [f001       ] [f002       ] [f003       ]
[f000       ] [f001       ] [f002       ] [f003       ]
[f000       ] [f001       ] [f002       ] [f003       ]
[f000       ] [f001       ] [f002       ] [f003       ]
[f000       ] [f001       ] [f002       ] [f003       ]
[f000       ] [f001       ] [f002       ] [f003       ]
[f000       ] [f001       ] [f002       ] [f003       ]

Quote:}

end
tables
f1
attributes
f000 = f1.col1;
f001 = f1.col2;
f002 = f1.col3;
f003 = f1.col4;
end
instructions
screen record s_f1[10] ( f1.* )
end
-----cut here rowtest.sql -------
create database rowtest;

create table f1
  (
    col1 integer,
    col2 integer,
    col3 integer,
    col4 integer
  );
------------end --------------------

 
 
 

4GL after row/next field statement

Post by Dave Snyd » Fri, 05 Nov 1993 03:38:47



}
} Problem:
} In a 4GL program input array, we are using the after row statement
} to test if all the values are correct.  The row contains 4 numbers
} one or more of which must be greater then 0.  All four cannot be 0.
} If all four are 0 we want to go the the first field on the row and
} prompt the user to re-enter a valid row.  The problem is that the
} next field statement in an after row statement goes to the next
} field on the next row leaving a row of incorrect values.  The following
} is an example 4gl program, screen and sql.  This problem has been
} tested with 4GL 4.0, 4.1, 1.1 and RDS 4.1.  We have a workaround
} using the after field statement and duplicating a lot of code.
} Any suggestion on how to do it with the after row statement?
}

Unfortunately you'll have to check after each field.  However, there is
no need to duplicate a lot of code...

Here --------- Snip Here --------- Snip Here --------- Snip Here --------- Snip
DATABASE rowtest

DEFINE  p_f1 ARRAY[50] OF RECORD LIKE f1.*

MAIN
    OPEN WINDOW win1 AT 5,5 WITH FORM "rowtest" ATTRIBUTE(BORDER)

    CALL set_count(0)
    INPUT ARRAY p_f1 FROM s_f1.*
        AFTER FIELD col1
            IF NOT valid_row() THEN
                NEXT FIELD col1
            END IF
        AFTER FIELD col2
            IF NOT valid_row() THEN
                NEXT FIELD col1
            END IF
        AFTER FIELD col3
            IF NOT valid_row() THEN
                NEXT FIELD col1
            END IF
        AFTER FIELD col4
            IF NOT valid_row() THEN
                NEXT FIELD col1
            END IF
    END INPUT
END MAIN

FUNCTION valid_row()
    DEFINE  p_rec INTEGER
    DEFINE  s_rec INTEGER

    LET p_rec = arr_curr()
    LET s_rec = scr_line()
    IF p_f1[p_rec].col1 = 0 AND p_f1[p_rec].col2 = 0 AND
       p_f1[p_rec].col3 = 0 AND p_f1[p_rec].col4 = 0 THEN
        ERROR "Invalid Row one entry must be > 0"
        RETURN FALSE
    END IF

    RETURN TRUE
END FUNCTION

Here --------- Snip Here --------- Snip Here --------- Snip Here --------- Snip

DAS
--

                                                              is db4glgen-3.16


 
 
 

4GL after row/next field statement

Post by Dennis Pimp » Thu, 11 Nov 1993 00:32:53


les...@access.digex.net (Lester Knutsen) writes:
>Posted for: Kelli Buccelli, ARS
>Summary:
>Need a way for the after row /next field statement in an input
>array to return to the same row instead of moving to the next row.
>Problem:
>In a 4GL program input array, we are using the after row statement
>to test if all the values are correct.  The row contains 4 numbers
>one or more of which must be greater then 0.  All four cannot be 0.
>If all four are 0 we want to go the the first field on the row and
>prompt the user to re-enter a valid row.  The problem is that the
>next field statement in an after row statement goes to the next
>field on the next row leaving a row of incorrect values.  The following
>is an example 4gl program, screen and sql.  This problem has been
>tested with 4GL 4.0, 4.1, 1.1 and RDS 4.1.  We have a workaround
>using the after field statement and duplicating a lot of code.
>Any suggestion on how to do it with the after row statement?

The "common" approach to this is to add a NOENTRY field in the perform
screen as the last field of the screen record, Put the INPUT ARRAY
statement in a WHILE loop, and keep a counter so that when we want to
go back a row we can EXIT INPUT, loop back up, reenter INPUT ARRAY, and
then use NEXT FIELD to the NOENTRY field to go down to the desired row.

I added to your code below for an example:

>-------cut here rowtest.4gl -------
>## After Row test:
># Need a way in 4GL to return to the same row if the whole row
># does not meet some input requirement.  The following is
># an example where a row is 4 numbers and on of the four numbers
># must be > 0.  Currently the after row clause to check this
># moves on the the next row.

>database rowtest

>main

# CHANGE TO BELOW >define         p_f1 array[50] of record like f1.*,
DEFINE         p_f1 ARRAY[50] OF RECORD
    col1    LIKE f1.col1,
    col2    LIKE f1.col2,
    col3    LIKE f1.col3,
    col4    LIKE f1.col4,
    ne      CHAR(1)            # NOENTRY field
END RECORD,
arrg    SMALLINT,    # Our "go to row" counter
act     SMALLINT,    # hold the value of arr_count()
>        p_rec        int,
>        s_rec        int
>open window win1 at 5,5
>        with form "rowtest" attribute (border )

LET act = 0
LET arrg = 1
WHILE arrg > 0
# CHANGE TO BELOW >call set_count(0)
CALL set_count(act)
# CHANGE TO BELOW >input array p_f1 from s_f1.*
input array p_f1 WITHOUT DEFAULTS from s_f1.*
>        before row
>                let p_rec = arr_curr()
>                let s_rec = scr_line()

                IF arrg > p_rec THEN
                    # we want to go lower in the array,
                    # so go to the NOENTRY field
                    NEXT FIELD ne
                END IF
                # reset arrg if we're going into this row
                LET arrg = 0
                # below not required, but recommended
                NEXT FIELD col1
>                error "Before Row p_rec = ",p_rec, " s_rec = ", s_rec
>        after row

                LET act = arr_count()
# NOT NEEDED >                let p_rec = arr_curr()
# NOT NEEDED >                let s_rec = scr_line()
>                if ( p_f1[p_rec].col1 = 0 and p_f1[p_rec].col2 = 0
>                and p_f1[p_rec].col3 = 0 and p_f1[p_rec].col4 = 0 ) then
>                        error "Invalid Row one entry must be > 0"

# CHANGE TO BELOW >                        next field col1
                    LET arrg = p_rec
                    EXIT INPUT
>            ################################################
>            # this is the problem. the program executes this
>            # statement and goes to col1 on the next row
>            ################################################
>                end if
>                error "After Row p_rec = ",p_rec, " s_rec = ", s_rec
>                sleep 2
>end input

# since we reset arrg on each row we enter, exiting with ACCEPT or
# INTERRUPT will cause use to exit the WHILE loop
END WHILE    # arrg > 0

- Show quoted text -

>end main
>------------cut here rowtest.per ------------------
>database rowtest
>screen size 24 by 80
>{
>             After Row test Table
>[f000       ] [f001       ] [f002       ] [f003       ][a]
>[f000       ] [f001       ] [f002       ] [f003       ][a]
>[f000       ] [f001       ] [f002       ] [f003       ][a]
>[f000       ] [f001       ] [f002       ] [f003       ][a]
>[f000       ] [f001       ] [f002       ] [f003       ][a]
>[f000       ] [f001       ] [f002       ] [f003       ][a]
>[f000       ] [f001       ] [f002       ] [f003       ][a]
>[f000       ] [f001       ] [f002       ] [f003       ][a]
>[f000       ] [f001       ] [f002       ] [f003       ][a]
>[f000       ] [f001       ] [f002       ] [f003       ][a]
>}
>end

{* I added field a to the screen form ------------------^^^ *}
>tables
>f1
>attributes
>f000 = f1.col1;
>f001 = f1.col2;
>f002 = f1.col3;
>f003 = f1.col4;

a = FORMONLY.ne TYPE CHAR, NOENTRY;

>end
>instructions

{* BELOW NOT REQUIRED, BUT RECOMMENDED *}
DELIMITERS "  "
{*
IF DELIMITERS LINE IS ADDED, WE CAN CHANGE THE SCREEN TO BELOW:
[f000       ] [f001       ] [f002       ] [f003       |a]
[f000       ] [f001       ] [f002       ] [f003       |a]
[f000       ] [f001       ] [f002       ] [f003       |a]
[f000       ] [f001       ] [f002       ] [f003       |a]
[f000       ] [f001       ] [f002       ] [f003       |a]
[f000       ] [f001       ] [f002       ] [f003       |a]
[f000       ] [f001       ] [f002       ] [f003       |a]
[f000       ] [f001       ] [f002       ] [f003       |a]
[f000       ] [f001       ] [f002       ] [f003       |a]
[f000       ] [f001       ] [f002       ] [f003       |a]
*}

{* CHANGE TO BELOW >screen record s_f1[10] ( f1.* ) *}
SCREEN RECORD s_f1[10] (col1, col2, col3, col4, ne)
>end
>-----cut here rowtest.sql -------
>create database rowtest;

>create table f1
>  (
>    col1 integer,
>    col2 integer,
>    col3 integer,
>    col4 integer
>  );
>------------end --------------------

================
Dennis J. Pimple
Informix CSE / Denver
 
 
 

4GL after row/next field statement

Post by Rob Mint » Fri, 12 Nov 1993 01:47:51


[Stuff Deleted]

Quote:>The "common" approach to this is to add a NOENTRY field in the perform
>screen as the last field of the screen record, Put the INPUT ARRAY
>statement in a WHILE loop, and keep a counter so that when we want to
>go back a row we can EXIT INPUT, loop back up, reenter INPUT ARRAY, and
>then use NEXT FIELD to the NOENTRY field to go down to the desired row.

[Code Deleted]

The only problem with this is that it looks terrible when you are on the last
few rows of the array and you do the 'exit - loop' routine to scroll back to
the row.  The scrolling could take forever on DOS, also.  Granted, it is the
best solution for the problem, yet, it makes for inconsistent coding, also
on Informix's part.  Every other statement that utilizes a 'CONTINUE XXXX'
works how most programmers want it to. So, why not a 'CONTINUE ROW' ???
( My opinion, ONLY!! :-)

The fastest and easiest solution I have is after each field test if the user
hit a down or up arrow by:

      IF fgl_lastkey() = fgl_keyval("down") OR
         fgl_lastkey() = fgl_keyval("up") THEN
          # Do what you want
      END IF

--
         Robert Minter                             Data Systems Support
Programmer, Software Development                        Orange, CA

bangpath:  uunet.uu.net!dssmktg!rob                  Fax: 714.771.3028

 
 
 

1. Need Amount field for first row, Null or 0 for next row

Hi,

I need help writing the SQL for an invoice and payments query.

For example, if we have an invoice for $200, but the customer pays $100, $80, and $20 in 3 separate payments, then I need to produce a query which shows the following:

InvoiceNum  InvoiceAmt  PaymentAmt  CheckNum
---------   ----------  ----------  --------
1001        200         100         12345
1001        Null        80          13123
1001        Null        20          14111

As you can see, it only puts the InvoiceAmt ONCE for the invoice number, and outputs a Null for every repeat of the same invoice number. When the invoice number changes, then it outputs the InvoiceAmt again, but only ONCE per each invoice number. The reason I need to do it this way, is because I'm creating a report which sums the columns. My balance will be off if the InvoiceAmt is repeated.

Tables used:

Invoice

ID [int]
InvoiceNumr [int]
InvoiceAmt [money]

Payments

ID [int]
Invoice_ID [int]
PaymentAmt [money]
CheckNum [varchar](20)

2. newbie question - INSERT

3. Mapping Prev/Next page in 4GL table field

4. -908 error connecting to Linux

5. db copyover using ftp

6. Enter,Next Row and Clear for Next record

7. Decision Support Applications

8. Create Dynamic Field in the Fetch Next Statement

9. NEXT FIELD in AFTER ROW

10. Need script for processing next row of data if error is encountered in row

11. 4gl - DEFINE statement w/ variable number of fields