How to Implement SELECT DISTINCT, SELECT ALL and function COUNT(*) through JDE APIs

This document is to show possible implementation on SELECT DISTINCT, SELECT ALL and SQL function count(*) through JDEBase (JD Edwards Database) APIs.

Caution: This document may contain information, software, products or services which are not supported by Oracle Support Services and are being provided ‘as is’ without warranty. Please refer to the following site for My Oracle Support Terms of Use: https://support.oracle.com/CSP/ui/TermsOfUse.html

Before proceed further, Refer JDB_OpenTable() which is important for listed APIs above.

JDB_SelectAll
JDB_SelectAll performs a select on a table without a WHERE clause. This causes all records to be selected.

Parameter	Notes	Usage
hRequest	Valid request handle.

Return Value	Description
JDEDB_PASSED	Return value if this succeeds
JDEDB_FAILED	Return value if this fails

*** Break In Code ***

/* Initialize environment handle. */
idReturnCode = JDB_InitBhvr(lpBhvrCom, &hUser, (JCHAR *) NULL, JDEDB_COMMIT_AUTO); /* this API sets &hUser - User (specific) Handle/pointer which is to be used in JDB_OpenTable() */

/* Open F03B11 table */
idReturnCode = JDB_OpenTable(hUser, NID_F03B11, ID_F03B11_DOC_NUMBER__DOC_TYPE___A, /* User Handle, Table ID, Index */
nidF03B11Select, 37, (JCHAR *)NULL, &hRequestF03B11); /* Set unique name for request handle e.g., hRequestF03B11 which is to be used in JDB_SelectAll() */

/* We must go through every record in the F03B11. */
idReturnCode = JDB_SelectAll(hRequestF03B11);
if (idReturnCode == JDEDB_PASSED)
{
   idReturnCode = JDB_Fetch(hRequestF03B11, (void *)&dsF03B11Fetch,(int)0); /* request, return value, nLock (not in use) */
   while (idReturnCode == JDEDB_PASSED) /* this code shows a typical WHILE loop which scan whole table based on primary key */
   {
       /* break code to perform manipulation in memory */
       idReturnCode = JDB_Fetch(hRequestF03B11, (void *)&dsF03B11Fetch,(int)0); /* either implment exit routine or call JDB_Fetch() before End While otherwise, your code falls into infinite looping */
   } /* End While Loop */
} /* End Select All */

*** Break In Code ***

Entering JDB_InitBhvr
Exiting JDB_InitBhvr with Success (UserHandle 2BB4BE18)
Entering JDB_OpenTable(Table = F4211)
Exiting JDB_OpenTable(Table = F4211) (UserHandle 2BB4BE18) (hRequest 0D5695E0) with Success
Entering JDB_ClearSelection (hRequest 0D5695E0)
Exiting JDB_ClearSelection with Success
Entering JDB_SelectAll (hRequest 0D5695E0)
ORACLE DBInitReq conn=0B0E23D8 requ=0D5C0718 den60202jems (gsc910) reuse
SELECT ALL FROM PRODDTA.F4211
Exiting JDB_SelectAll with Success
Entering JDB_CloseTable (hRequest 0D5695E0)
Entering JDB_CloseTable(Table = F4211)

Note:

Refer to JDB_OpenTable() API before change any code
Above example, B03B0211 does not write SELECT ALL rather it will be appeared as SELECT return columns FROM PRODDTA.F03B11 (Note that WHERE clause is not appended because this will get all data from table)
In case you want to write SELECT ALL FROM PRODDTA.F03B11, call JDB_OpenTable(hUser, NID_F03B11, 0, NULL, 0, (JCHAR *)NULL, &hRequestF03B11);
Commonly indicate Primary Index for this API
EnterpriseOne Conversion tools or batch application perform same manipulation. So unless you understand this well, do not use this
If you need to implement WHILE loop, implement it with care
JDB_InitBhvr(lpBhvrCom, &hUser, (JCHAR *) NULL, JDEDB_COMMIT_AUTO) puts query explicit automatic commitment. Refer to <Document 1513147.1> E1: DB: JDEDB_COMMIT_MANUAL, JDEDB_COMMIT_AUTO, Transaction Processing Boundaries and Deadlock

JDB_SelectKeyedDistinct
JDB_SelectKeyedDistinct performs a select on a table, view using the identified index and any custom selection and sequencing. Sending a zero in the number of keys to be used will cause the index to be ignored; however, if custom selection was identified it will always be used in this API. This only selects records matching the index (if used) exactly.

This eliminates duplicates, if they exist, from the result set. The hRequest used in this API should be obtained from a call to JDB_OpenTable or JDB_OpenView where the table is opened with a specified number of columns. If all columns are requested in JDB_OpenTable or if all keyed columns are included in the Open Table call, then JDB_SelectKeyedDistinct will give identical results as SelectKeyed.

Example of query:
sql> SELECT DISTINCT SDKCOO, SDDCTO FROM PRODDTA.F4211 WHERE ( SDKCOO = '00001' AND SDDCTO = 'SO' );

JDEDB_RESULT JDB_SelectKeyedDistinct(HREQUEST hRequest, ID idIndex, void * lpKey, short nNumKeys);

Parameter	Notes	Usage
hRequest	Valid request handle.
idIndex	Valid Index ID to use for the selection. If zero then index of request will be used.
lpKey	Pointer key structure or NULL. If no structure is to be used then pass NULL.
nNumKeys	Number of keys to be used. If ON then the request is valid for caching.

Return Value	Description
JDEDB_PASSED	Return value if this succeeds
JDEDB_FAILED	Return value if this fails

*** Break In Code ***

idJDBReturn = JDB_InitBhvr(lpBhvrCom, &hUser, (JCHAR *) NULL, JDEDB_COMMIT_AUTO); /* to get &hUser */
idJDBReturn = JDB_OpenTable(hUser, NID_F3460, /* you can open table using hUser returned */
                               ID_F3460_ITEM_NUMBER__BRANCH__ESU_ONLY,
                               szArray, uNumberInArray, /* szArray defined return columns. If possible, set unique name e.g., szF3460Array */
                               (JCHAR *)NULL, &hRequestF3460); /*returns hRequestF3460 which is to be used by JDB_SetSelection() and JDB_SelectKeyedDistinct() */
   if(idJDBReturn == JDEDB_PASSED)
   {
      idJDBReturn = JDB_SetSelection(hRequestF3460, dsSelect, uNumberInSelect, JDEDB_SET_REPLACE); /* This API restricts selection of data */
      /* JDB_SetSequencing() can be called to append Order By in query */
      if(idJDBReturn == JDEDB_PASSED)
      {
         idJDBReturn = JDB_SelectKeyedDistinct(hRequestF3460, 0, NULL, 0);
         if(idJDBReturn == JDEDB_PASSED)
         {
            while((JDB_Fetch(hRequestF3460, NULL, 0) == JDEDB_PASSED) && (bContinue == TRUE)) /* for this example, bContinue prevent infinite looping in detail routine commented below */
          {
              /* Do something to manipulate based on unique data returned. In case you get top 100 the logic has to coded here */
            }
         }
      }
   }
*** Break In Code ***

Entering JDB_InitBhvr
Exiting JDB_InitBhvr with Success (UserHandle 2A8AF5C0)
Entering JDB_OpenTable(Table = F4211)
Exiting JDB_OpenTable(Table = F4211) (UserHandle 2A8AF5C0) (hRequest 0D44FC58) with Success
Entering JDB_ClearSelection (hRequest 0D44FC58)
Exiting JDB_ClearSelection with Success
Entering JDB_SetSelectionX (hRequest 0D44FC58)
Exiting JDB_SetSelectionX With Success
Entering JDB_SelectKeyedDistinct (hRequest 0D44FC58)
ORACLE DBRsetReq conn=0B2323D8 requ=0D4837E8 den60202jems (gsc910)
SELECT DISTINCT SDKCOO, SDDCTO FROM PRODDTA.F4211 WHERE ( SDKCOO = '00001' AND SDDCTO = 'SO' )
Exiting JDB_SelectKeyedDistinct with Success
ORACLE DBFetch conn=0B2323D8 requ=0D4837E8 maxrows=100
ORACLE usage 20:24:32 (11 FTCH 10 rows 6 LOBs) (0 UPD) (0 INS 0 rows) (0 DEL) (0 MISC)
ORACLE speed 20:24:32 ( 2000 usec per call, 29 FTCH+LOB calls)
Fetched the record
No More Data found
Entering JDB_CloseTable (hRequest 0D44FC58)
Entering JDB_CloseTable(Table = F4211)

Note:

If the SELECT clause contains more than one field, the combination of values from all fields must be unique for a given record to be included in the results.
The output of a query that uses DISTINCT is not updatable and does not reflect subsequent changes made by other users.
Currently there is no API like SELECT TOP 100 or SELECT DISTINCT ROW
Implement while loop with care

JDB_SelectKeyedGetCount
JDB_SelectKeyedGetCount gets a count of the number of rows in a table using the identified index and / or any custom selection & sequencing. Sending a zero in the number of keys to be used will cause the index to be ignored, however, if custom selection was identified it will always be used in this API. This only counts records matching the index (if used) exactly.

If no index is used, for example, if lpKey is passed in as a NULL and nNumKeys is passed in as a zero, then a count of all the rows in the table is returned.

Example of query:
sql> SELECT SDKCOO, SDDCTO FROM PRODDTA.F4211 WHERE ( SDKCOO = '00001' AND SDDCTO = 'SO' ) ORDER BY SDDOCO ASC,SDDCTO ASC,SDKCOO ASC,SDLNID ASC;
sql> SELECT COUNT(*) FROM PRODDTA.F4211 WHERE ( SDKCOO = '00001' AND SDDCTO = 'SO' );

Syntax:
Unsigned long JDB_SelectKeyedGetCount (HREQUEST hRequest, ID idIndex, void * lpKey, short nNumKeys)

Parameters:

Parameter	Notes	Usage
hRequest	Valid request handle.
idIndex	Valid Index ID to use for the selection. If zero then index of request will be used.
lpKey	Pointer key structure or NULL. If no structure is to be used then pass NULL.
nNumKeys	Number of keys to be used. If ON then the request is valid for caching.

Return Value	Description
Returns number of records in table corresponding to the selection criteria.

*** Break In Code ***
/* Declare variables associated with JDB_SelectKeyedGetCount*/
   idJDBReturn = JDB_InitBhvr(lpBhvrCom, &hUser, (JCHAR *) NULL, JDEDB_COMMIT_AUTO); /* to return User Handle hUser */
   idJDBReturn = JDB_OpenTable(hUser, NID_F04572,
                               ID_F04572_PCG_NUMBER___PAYMENT__NUMBER,
                               szArray, uNumberInSelect, /* make it sure return columns is initialized and declared correctly */
                               (JCHAR *)NULL, &hRequestF04572); /* return hRequestF04572 which is to be used in following JDB APIs */
   if(idJDBReturn == JDEDB_PASSED)
   {
      idJDBReturn = JDB_SetSelection(hRequestF04572, dsSelect, uNumberInSelect, JDEDB_SET_REPLACE); /* WHERE clause */
      if(idJDBReturn == JDEDB_PASSED)
      {
         idJDBReturn = JDB_SelectKeyed( hRequestF04572, 0, NULL, 0 ); /* this API must be called before you call JDB_SelectKeyedGetCount which reads returns rows in memory */
         if(idJDBReturn == JDEDB_PASSED)
         {
            ulCount = JDB_SelectKeyedGetCount( hRequestF04572, 0, NULL, 0 );
            if(ulCount > 0)
            {
                /* Do something if needed. Or simply return ulCount */
            }
         }
      }
   }

*** break in code ***

Entering JDB_InitBhvr
Exiting JDB_InitBhvr with Success (UserHandle 2A769D18)
Entering JDB_OpenTable(Table = F4211)
Exiting JDB_OpenTable(Table = F4211) (UserHandle 2A769D18) (hRequest 2A01E8F8) with Success
Entering JDB_ClearSelection (hRequest 2A01E8F8)
Exiting JDB_ClearSelection with Success
Entering JDB_SetSelectionX (hRequest 2A01E8F8)
Exiting JDB_SetSelectionX With Success
Entering JDB_SelectKeyed (hRequest 2A01E8F8)
ORACLE DBInitReq conn=0B12C9D0 requ=0D2F58B0 den60202jems (gsc910) reuse
SELECT SDKCOO, SDDCTO FROM PRODDTA.F4211 WHERE ( SDKCOO = '00001' AND SDDCTO = 'SO' ) ORDER BY SDDOCO ASC,SDDCTO ASC,SDKCOO ASC,SDLNID ASC
Exiting JDB_SelectKeyed with Success
ORACLE DBRsetReq conn=0B12C9D0 requ=2A063F58 den60202jems (gsc910)
SELECT COUNT(*) FROM PRODDTA.F4211 WHERE ( SDKCOO = '00001' AND SDDCTO = 'SO' )
ORACLE DBFetch conn=0B12C9D0 requ=2A063F58 maxrows=1
ORACLE usage 17:44:25 (24 FTCH 2240 rows 0 LOBs) (0 UPD) (0 INS 0 rows) (0 DEL) (0 MISC)
ORACLE speed 17:44:25 ( 1166 usec per call, 24 FTCH+LOB calls)
ORACLE DBFetch conn=0B12C9D0 requ=0D2F58B0 maxrows=100
Fetched the record
Fetched the record
Fetched the record
No More Data found
Entering JDB_CloseTable (hRequest 2A01E8F8)
Entering JDB_CloseTable(Table = F4211)

JDB_OpenTable
JDB_OpenTable opens the table identified by the given table NID and prepares it for input/output processing. After successfully preparing the table for input/output, JDB associates the table with the passed user handle. The user is returned a request handle. This request handle must be used on every subsequent data manipulation call to JDB on this specific table. The request handle must be used only on the specified table until a JDB_CloseTable is called which will free the request handle. This API must be called prior to any other JDB operations on the table identified by the given table NID and must be used in conjunction with JDB_CloseTable.

JDEDB_RESULT JDB_OpenTable(HUSER hUser, NID szTable, ID idIndex, NID *lpColSelect, unsigned short nNumCols, char * szOverrideDS, HREQUEST * hRequest);

How to Implement SELECT DISTINCT, SELECT ALL and function COUNT(*) through JDE APIs

Purpose of Document

Other related work

Parameter	Notes	Usage
hUser	Valid user handle	From JDB_InitUser
szTable	Valid table NID
idIndex	This is the ID of the index to ready for processing. The index used can be changed after a table has been requested. If zero, the primary index will be used.
*lpColSelect	Pointer to an array of column NID's or NULL. These columns will be used for rquests to the database. Performance is enhanced if only the required columns are asked for. This is useful when the application is only processing certain columns in the database. If all columns are requred then NULL can be passed.
nNumCols	Number of columns required or zero. This is the number of column NIDs in the lpColSelect array. If all columns are required (e.g., if lpColSelect is NULL) then pass zero.
szOverrideDS	Datasource name. This will override the location of the table indicated by the Object Map.	NULL for default datasource based on the environment.
hRequest	NULL or request handle	The output (do not set NULL)