Select a range in Table.Select

Rauf

Rauf

VIP Member
How can a specify a range in Select Table IO ?
I just tried the following code and it works nice. But performance will degrade if there are too much records.
In the code below, I just calculate the total hours worked by a labor during a period.

Any other ways to achieve the same ?

Code:
Listing of ER for Named ER: N59RM01


=======================================================================
     NAMED ER: Get Labor Hours Summary
=======================================================================
     evt_HR
     evt_OT
     evt_HR_Total
     evt_OT_Total
     evt_EmployeeCardJDate
0001 //
0002 //
0003 F59RM03.Select
        BF mnLaborID =  TK RM Labor ID
        BF jdTimesheetDateFROM <= TK Emp Card Date - Julian
0004 F59RM03.Fetch Next
        VA evt_EmployeeCardJDate <- TK Emp Card Date - Julian
0005 While SV File_IO_Status is equal to CO SUCCESS And VA evt_EmployeeCardJDate is less than or equal to BF jdTimesheetDateTO
0006    // Calculate ...
0007 End While

Wednesday September 10, 2014  10:18
 
Last edited:
Not that I know of.

Even N0800034 Determine Eligibility Hours works the same way just the other way around

If you had a known week or month timespan type field in your F59 table you could use that. So a 4 digit WWYY type field, so 0114 is the first week of Jan 2014 etc

So that could be an option??? If you know the date then you know the week of the year your records will fall on and you could use that to limit the records in your select...?

Basically your moving that date into another field into another format which you can then select down from
 
You can create very complex WHERE clauses and do a range like you want. Just not in ER code. You would need to do this in a C BSFN using NEWSELECTSTRUCT and JDB_SetSelectionX.

Example:
Code:
NEWSELECTSTRUCT		select[3]={0};
...
nSelIdx=0;
jdeNIDcpy( select[nSelIdx].Item1.szDict,		NID_LABID);
jdeNIDcpy( select[nSelIdx].Item1.szTable,	NID_F59RM03 );
select[nSelIdx].Item1.idInstance			= (ID)0;
jdeNIDcpy( select[nSelIdx].Item2.szDict,		_J("") );
jdeNIDcpy( select[nSelIdx].Item2.szTable,	_J("") );
select[nSelIdx].Item2.idInstance			= (ID)0;
select[nSelIdx].lpValue					= &lpDS->mnLaborID;
select[nSelIdx].nValues					= 1;
select[nSelIdx].nCmp					= JDEDB_CMP_EQ;
select[nSelIdx].nAndOr					= JDEDB_ANDOR_AND;
select[nSelIdx++].nParen				= JDEDB_PAREN_NONE;

jdeNIDcpy( select[nSelIdx].Item1.szDict,		NID_JDATE);
jdeNIDcpy( select[nSelIdx].Item1.szTable,	NID_F59RM03 );
select[nSelIdx].Item1.idInstance			= (ID)0;
jdeNIDcpy( select[nSelIdx].Item2.szDict,		_J("") );
jdeNIDcpy( select[nSelIdx].Item2.szTable,	_J("") );
select[nSelIdx].Item2.idInstance			= (ID)0;
select[nSelIdx].lpValue					= &lpDS->jdTimeSheetDateFrom;
select[nSelIdx].nValues					= 1;
select[nSelIdx].nCmp					= JDEDB_CMP_GE;
select[nSelIdx].nAndOr					= JDEDB_ANDOR_AND;
select[nSelIdx++].nParen				= JDEDB_PAREN_OPEN;

jdeNIDcpy( select[nSelIdx].Item1.szDict,		NID_JDATE);
jdeNIDcpy( select[nSelIdx].Item1.szTable,	NID_F59RM03 );
select[nSelIdx].Item1.idInstance			= (ID)0;
jdeNIDcpy( select[nSelIdx].Item2.szDict,		_J("") );
jdeNIDcpy( select[nSelIdx].Item2.szTable,	_J("") );
select[nSelIdx].Item2.idInstance			= (ID)0;
select[nSelIdx].lpValue					= &lpDS->jdTimeSheetDateThru;
select[nSelIdx].nValues					= 1;
select[nSelIdx].nCmp					= JDEDB_CMP_LE;
select[nSelIdx].nAndOr					= JDEDB_ANDOR_AND;
select[nSelIdx++].nParen				= JDEDB_PAREN_CLOSE;

if(JDB_OpenTable(hUser, NID_F59RM03, (ID)0, (NID *)NULL, (ushort)0, (JCHAR *)NULL, &hReq) != JDEDB_PASSED ||

   JDB_SetSelectionX(hReq, select, nSelIdx, JDEDB_SET_REPLACE) != JDEDB_PASSED ||
   
   JDB_SelectKeyed(hReq, (ID)0, (void *)NULL, (short)0) != JDEDB_PASSED)
{
	//do error handling stuff   
}

while(JDB_Fetch(hReq, &recF59RM03, 0) == JDEDB_PASSED)
{
  //do record processing
}

JDB_CloseTable(hReq);

create the following WHERE clause:

WHERE F59RM03.LABID == 12345 and (F59RM03.JDATE >= 5/1/2014 and F59RM03.JDATE <= 5/31/2014)
 
Last edited:
2 criteria on a single field. Table IO may not do that.

How about using custom section replacing Select & fetch Next? Create custom section with view on this table and set your selection similar to above. I guess it will do the job for you.
 
This will work only with reports and we do the same for reports.
But here I use custom business function.
 
Rauf, could you not just have the date in 2 fields as I suggested above? Copy the date to another field that the user never sees and keep them in synch (URDT?)

Far easier than C :)
You can't do 2 selects on the same column table IO in ER. So you have to start thinking maybe you should split the data into 2 fields in which you can do a range
 
Back
Top