How to Write LIKE Key Word SQL through a Business Function

Purpose of Document

To optimize system resource across EnterpriseOne, the query below is required,

SQL> SELECT return columns FROM Schema.Table WHERE Column LIKE 'ABC%' ORDER BY Column;

Using the EnterpriseOne APIs (Application Programming Interface), JDEBASE APIs will be used to access the database which are made up of:

Desired Result:
SQL> SELECT KGITEM, KGCO, KGMCU, KGOBJ, KGSUB FROM PRODDTA.F0012 WHERE KGITEM LIKE 'SP%' AND KGCO = '00000';

The best practice for this type of request can be fulfilled by examining existing code (standard EnterpriseOne C Business Functions) which were delivered upon installation.

  1. Make sure that Business Function you are creating is referencing TargetTable.h where NID_Table and NID_Columns are defined with #define key word or define NID inside the C source file.
  2. Declare Variables.
  3. Declare Data Structure and if needed define typedef.
  4. Initialize Memory (or pointer which is Address of Memory).
  5. Open a specific table.
  6. Construct the WHERE clause.
  7. JDB_SetSelection() using Data Structure of Select[].
  8. JDB_SelectKeyed() .
  9. JDB_Fetch().
  10. Loop using WHILE.
  11. JDB_CloseTable().

NOTE:



Reference of XX0901.c


ID I_3609CacheSpeedCodes(HUSER hUser, HCACHE hI0012Cache)
{
/* 1. Declare variables */
ID idReturnCode = ER_SUCCESS; /* Return Code */
ID idJDBReturnCode = JDEDB_PASSED; /* JDB Return Code */
HREQUEST hRequestF0012;
NID szColF0012[11] = {NID_ITEM, NID_CO, NID_MCU, NID_OBJ, NID_SUB};
ushort nNumColsF0012 = 5;
JDECM_RESULT jdeCacheCode = JDECM_FAILED;

/* 2. Declare structures */
SELECTSTRUCT Select[2] = {0}; /* Array for WHERE */
DS_COLUMN_F0012 dsColF0012; /* Return Value */
I0012 dsI0012;/* Table Data Structure */

/* 3. Initialize memory (Address) */
memset((void *)(&dsI0012), (int)(_J('\0')), sizeof(dsI0012));
memset((void *)(&dsColF0012), (int)(_J('\0')), sizeof(dsColF0012));

/* 4. Open Table */
idJDBReturnCode = JDB_OpenTable (hUser, NID_F0012,
ID_F0012_ITEM_NUMBER_COMPANY,
szColF0012, nNumColsF0012,
JCHAR *)NULL, &hRequestF0012);
if (idJDBReturnCode != JDEDB_PASSED)
{
return (idReturnCode);
}

/* 5. Construct WHERE clause */
jdeNIDcpy(Select[0].Item1.szDict, NID_ITEM);
jdeNIDcpy(Select[0].Item1.szTable, NID_F0012);
Select[0].Item1.idInstance = (ID)0;
jdeNIDcpy(Select[0].Item2.szDict, _J(""));
jdeNIDcpy(Select[0].Item2.szTable, _J(""));
Select[0].Item2.idInstance = (ID)0;
Select[0].lpValue = _J("SP%");/* Hard coded value */
Select[0].nValues = 1;
Select[0].nAndOr = JDEDB_ANDOR_AND;
Select[0].nCmp = JDEDB_CMP_LK;

jdeNIDcpy(Select[1].Item1.szDict, NID_CO);
jdeNIDcpy(Select[1].Item1.szTable, NID_F0012);
Select[1].Item1.idInstance = (ID)0;
jdeNIDcpy(Select[1].Item2.szDict, _J(""));
jdeNIDcpy(Select[1].Item2.szTable, _J(""));
Select[1].Item2.idInstance = (ID)0;
Select[1].lpValue = _J("00000"); /* Value is hard coded */
Select[1].nValues = 1;
Select[1].nAndOr = JDEDB_ANDOR_AND;
Select[1].nCmp = JDEDB_CMP_EQ;

/* 6. JDB_SetSelection() using DataStructure of Select */
idJDBReturnCode = JDB_SetSelection(hRequestF0012, (LPSELECT)Select,
(ushort) 2, JDEDB_SET_REPLACE);
/* 7. JDB_SelectKeyed() */
idJDBReturnCode = JDB_SelectKeyed(hRequestF0012,(ID) 0,
(void *) NULL, (short) 0);
/* 8. JDB_Fetch() */
idJDBReturnCode = JDB_Fetch (hRequestF0012, (void *)&dsColF0012, FALSE);
/* 9. Loop using WHILE */
while (idJDBReturnCode != JDEDB_FAILED)
{
dsI0012.kgitem = dsColF0012.kgitem[2];
jdeStrncpy(dsI0012.kgmcu, (const JCHAR *)(dsColF0012.kgmcu), DIM(dsI0012.kgmcu));
jdeStrncpy(dsI0012.kgobj, (const JCHAR *)(dsColF0012.kgobj), DIM(dsI0012.kgobj));
jdeStrncpy(dsI0012.kgsub, (const JCHAR *)(dsColF0012.kgsub), DIM(dsI0012.kgsub));
/* Write routine to handle data or store return value into cache */
idJDBReturnCode = JDB_Fetch (hRequestF0012, (void *)&dsColF0012, FALSE);
}
/* 10. JDB_CloseTable() */
JDB_CloseTable(hRequestF0012);
return idReturnCode;
}

NOTE:



Alternatively, implement it using Table I/O as below,

/* Define/Initialize Variables */
VA evt_szItemNumber_ITEM = "SP%"
VA evt_szCompany_CO = "00000"
VA evt_szCostCenter_MCU = ""
VA evt_szObjectAccount_OBJ = ""
VA evt_szSubsidiary_SUB = ""

/* Open Table to make connection */
F0012.Open
F0012.Select
VA evt_szItemNumber_ITEM is Like TK Item Number
VA evt_szCompany_CO = TK Company
F0012.Fetch Next
VA evt_szCostCenter_MCU <- TK Business Unit
VA evt_szObjectAccount_OBJ <- TK Object Account
VA evt_szSubsidiary_SUB <- TK Subsidiary
While SV File_IO_Status is equal to CO SUCCESS
/* Write Routine to make use of return value */
F0012.Fetch Next
VA evt_szCostCenter_MCU <- TK Business Unit
VA evt_szObjectAccount_OBJ <- TK Object Account
VA evt_szSubsidiary_SUB <- TK Subsidiary
End While
F0012.Close




Reference:

Valid values for member nCmp of the SELECT structure.
Defined in /system/include/database/db_defines.h

Definition Sign Description
JDEDB_CMP_LE = 0 <= Less Than and Equal To
JDEDB_CMP_GE = 1 >= Greater Than and Equal To
JDEDB_CMP_EQ = Equal To
JDEDB_CMP_LT < Less Than
JDEDB_CMP_GT > Greater Than
JDEDB_CMP_NE != Not Equal To
JDEDB_CMP_IN IN
JDEDB_CMP_NI Not In
JDEDB_CMP_BW Between A And B (Includes)
JDEDB_CMP_NB Not Between A And B (Excludes)
JDEDB_CMP_LK Like
JDEDB_CMP_MATCH_ALL TextSearch MatchAll
JDEDB_CMP_MATCH_ANY TextSearch MatchAny
JDEDB_CMP_MATCH_EXACT TextSearch MatchExact
JDEDB_CMP_CONTAINS TextSearch CONTAINS
JDEDB_CMP_BOGUS = 99999 Not a valid value. This is just to force 4 byte ENUM on AS/400

NOTE: