Tips / Traps‎ > ‎

Obtaining a MAX Column

Recently it was asked how to obtain a 'Max' field, from a specific table.  The SQL statement to retrieve such a record is rather simple, however - there isn't a simplified 'Max' command to actually pull the value from an SQL statement.  

Ideally, the SQL Statement would look similar to:
“Select max(rpdoc) from proddta.f03b11 where rpco = 00001;”

Though there are API(s) that can be used in C - I use a very simplified means, by selecting the correct index and Select / Fetch Next statements.

First, make sure that you have an Index with a Descending Mark on the Column in the table you want to use.  Note, that by default, all columns in JDE Defined Table are Ascending.  You can change the property of the index by right-clicking the column and deselecting the Ascending value.

For the example SQL statement - Create a new index, MAX DOC and define it as below:

Create the Index

Right-Click and change from Ascending
   

Change to


The Column's property in the new index should look like:

Notice that the Sort Order is, now, Descending.

Now, within your Event Rules - Create the following logic:
1 - Use the New Index and set RPCO = your variable



Use Fetch Next to pull the Highest Value'd RPDOC from the table"


Your Event Rules would look similar to:

===================================================================
     SECTION: <MAXDOC> [GROUP SECTION] (S2)
===================================================================
     OBJECT:  SECTION
     EVENT:  Do Section
-----------------------------------------------------------------------
     OPT: Using Defaults
0001 VA rpt_DocVoucherInvoiceE = "00000"
0002 F03B11.Select
        VA rpt_Company =  TK Company
0003 F03B11.Fetch Next
        VA rpt_DocVoucherInvoiceE <- TK Document (Voucher, Invoice, etc.)



Do Note:  The actual 'Select' statement submitted to the server will return all the records that fit the criteria - and you are only cherry-picking the first (top) row.

Oracle provides a very 'clean' example of how to retrieve the 'MAX' Column, using C API(s) in their document:
E1: BSFN: How to Get MAX/Aggregate using JDE API [ID 1337735.1]
 

Comments