change sort seq on the fly

[agoyal]

"I want to do a fetch next loop on a table (F4211) using an “order by” that is not in any of the available indexes. In SQL I would just do a select statement with an ORDER BY clause. I do not want to add index as this table already has many indexes.
I read something in Post # 73468 which seems a cool solution. And on that basis I created a business function as follows:


JDEBFRTN (ID) JDEBFWINAPI B55AddSort (LPBHVRCOM lpBhvrCom, LPVOID lpVoid, LPDSD55UTIL01 lpDS)
{
/************************************************************************
* Variable declarations
************************************************************************/
ID idReturn;


/************************************************************************
* Declare structures
************************************************************************/
FILEIO_HANDLE_INFO dsHandleInfo = { 0 };

/************************************************************************
* Declare pointers
************************************************************************/
SORTSTRUCT Sort[3] = {0};
/************************************************************************
* Check for NULL pointers
************************************************************************/
if ((lpBhvrCom == (LPBHVRCOM) NULL) ||
(lpVoid == (LPVOID) NULL) ||
(lpDS == (LPDSD55UTIL01) NULL))
{
jdeErrorSet (lpBhvrCom, lpVoid, (ID) 0, "4363", (LPVOID) NULL);
return ER_ERROR;
}

/************************************************************************
* Set pointers
************************************************************************/

/************************************************************************
* Main Processing
************************************************************************/
if(!RTK_CER_FIOGetHandleInfo( lpDS->idTableHandle , &dsHandleInfo ))
{
return ER_ERROR;
}
jdeNIDcpy(Sort[0].Item.szDict, "UPRC");
jdeNIDcpy(Sort[0].Item.szTable, "F4211");
Sort[0].Item.idInstance = 0;
Sort[0].nSort = JDEDB_SORT_ASC;
jdeNIDcpy(Sort[1].Item.szDict, "DCTO");
jdeNIDcpy(Sort[1].Item.szTable, "F4211");
Sort[1].Item.idInstance = 0;
Sort[1].nSort = JDEDB_SORT_ASC;

jdeNIDcpy(Sort[2].Item.szDict, "DOCO");
jdeNIDcpy(Sort[2].Item.szTable, "F4211");
Sort[2].Item.idInstance = 0;
Sort[2].nSort = JDEDB_SORT_ASC;

JDB_ClearSequencing(dsHandleInfo.hReqest);

idReturn = JDB_SetSequencing (dsHandleInfo.hReqest, Sort, 3, JDEDB_SET_REPLACE);

if (idReturn != JDEDB_PASSED )
{
jdeSetGBRError(lpBhvrCom, lpVoid, (ID) 0, "3143");
JDB_CloseView(dsHandleInfo.hReqest);
return FALSE;
}


/************************************************************************
* Function Clean Up
************************************************************************/

return (ER_SUCCESS);
}

--------------------------------------


"The DS Template Type Definition for the above is as follows:


#ifndef DATASTRUCTURE_D55UTIL01
#define DATASTRUCTURE_D55UTIL01

typedef struct tagDSD55UTIL01
{
ID idTableHandle;
} DSD55UTIL01, *LPDSD55UTIL01;

#define IDERRidTableHandle_4 4L

#endif
----------------------------


"I used F4211 Handle (HF4211) to open the table in the application. Then I did Select using the same handle. The I passed this handle to the above business function expecting it to change the data sequencing. But it is not working.
I wonder if any one of you have done something like this. Please comment.

 

[JMR]

The concept should work. My guess is the Select you are doing (I assume in the ER) is actually submitting the SQL statement. Try calling your bsfn BEFORE you do the ER Select (but after the open). Also, turn on logging and see when the SQL statement is being submitted and what it looks like.

Good luck and let us know if you figure it out.

 

[agoyal]

1. Yes, I am doing Select in ER of JDE Application.
2. I already tried to call BSFN just before I do SELECT (but immediately after OPEN). Also, I tried to call BSFN just after I do SELECT too.
3. I had the debug logging turned on. I saw that BSFN was called (with the correct parameters). And then JDE_ClearSequencing and JDE_SetSequencing were called (as usual, no parameters were mentioned for these native BSFNs). Then BSFN was existed correctly.

I am sure there is something which can be done to achieve this ORDER BY clause(especially in JDE Application design).

 

[Larry_Jones]

Try creating an index with the desired sort order in JDE ... BUT do not generate/create the index in the database. That should cause the correct SQL to be generated without experiencing the overhead of the index in the database.

 

[JMR]

Sorry that doesn't work. It appears that the ER Select is doing the sequencing also...based on the index you choose. Then it does a JDB_SelectKeyed which submits the SQL to the DB. So there is no window to do the sequencing yourself. You could verify this by putting that same Select code into an NER and looking at the generated C-code. It must be doing all of: JDB_SetSelection, JDB_SetSequencing and JDB_SelectKeyed.

Don't give up, though. Larry's idea is a good one and should work, but the drawback to that solution is that any time that table does get its indices regen'ed, you will have added your index to the DB.

You could also modify your new bsfn to also do the Select portion. Meaning you would call have to call JDB_SetSelection and JDB_SelectKeyed yourself. I know it is not as nice as the ER tool, but it could work. Of course, you'd have to pass the parameters that the current Select is using.

Good luck.

 

[agoyal]

Jeremy/Larry:
a) Larry's idea is a good one - but I have to keep track of it for future upgrades.
b) Doing JDB_SelectKeyed in BSFN is what I wanted to avoid.

Nonetheless, I appreciate your insight on this issue.

Regards,
Anuj

 

[Zoltan_Gyimesi]

Hi Anuj,

If nothing other works, then the brute force solution do: using work table and Business View for Select/FetchNext over the work table and F4211.

OK, I know, not to sophisticated solution, but it will be do the job.

Regards,

Zoltán

 

[agoyal]

Well another solution could be that you create a new form with grid (make both the form and the grid as "not visible"; have sort order defined on the grid the way you want). call this form within event rule. Execute your (fetch next) logic for each record read for the grid. The variables, if any, you can pass as Form Interconnect variables.