E9.2 No Keys for SDRKCO, SDRORN, SDRCTO, SDRLLN?

[FrankCLT]

Hello All,

I need to read through an F59 table that contains Related Order info (RORN, RKCO.....) for each record read in the F59 table, I need to check the F4211 for the related values. Problem for me is there are no keys over the Related order data in the F4211 that I saw. How can I do this? I'm open to doing something in C as well. Ideas?

Thank you,
FrankCLT

 

[BOster]

You don't need secondary keys to do table I/O. You can select on any fields you want. Having said that you may want to add a secondary key for performance reasons. This really is the only reason to add a secondary key. I see devs add secondary keys all the time thinking they need them for what is effectively the WHERE clause when doing table I/O. That is not the case.


In ER code simply do a F4211.Select, then read result set with F4211.FetchNext

In C use JDB_SetSelectionX.

 

[FrankCLT]

Thank you…..One more quick question, what is the difference between SetSelectionX and SetSelection ?

 

[JohnDanter2]

If you are allowed to create indexes in E1, just make one. Make sure you generate indexes only. NOT the table.

If not, a select as Brian as said is great. Don't need the key and maybe a DBA will create one to help with performance.
I advise developers to wrap the select and fetch nexts with an open close

Also, if you try and use fetch single and you don't use ALL the keys, you'll get an error.

So do this
open table
select table (on field you want)
fetch next on table
close same table

 

[FrankCLT]

Hello,

I've tried the JDE_SetSelection and the JDE_SetSelectionX using the sdrorn, sdrcto, sdrkco and sdrlln and it doesn't return any data. I've used the below format numerous times in other functions successfully. In this example it is the JDE_SetSelection but I have also tried the JDE_SetSelectionX. I have validated the values in the lpDS through debug.

JDB_ClearSelection(hReqF4211;

jdeNIDcpy(dsSelect[0].Item1.szDict, NID_RORN);
jdeNIDcpy(dsSelect[0].Item1.szTable, NID_F4211);
dsSelect[0].lpValue = (void *)lpDS->szRORN;
dsSelect[0].nValues = (ushort)1;
dsSelect[0].nAndOr = JDEDB_ANDOR_AND;
dsSelect[0].nCmp = JDEDB_CMP_GT;

jdeNIDcpy(dsSelect[1].Item1.szDict, NID_RCTO);
jdeNIDcpy(dsSelect[1].Item1.szTable, NID_F4211);
dsSelect[1].lpValue = (void *)lpDS->szRCTO;
dsSelect[1].nValues = (ushort)1;
dsSelect[1].nAndOr = JDEDB_ANDOR_AND;
dsSelect[1].nCmp = JDEDB_CMP_EQ;

jdeNIDcpy(dsSelect[2].Item1.szDict, NID_RKCO);
jdeNIDcpy(dsSelect[2].Item1.szTable, NID_F4211);
dsSelect[2].lpValue = (void *)lpDS->szRKCO;
dsSelect[2].nValues = (ushort)1;
dsSelect[2].nAndOr = JDEDB_ANDOR_AND;
dsSelect[2].nCmp = JDEDB_CMP_GT;

jdeNIDcpy(dsSelect[3].Item1.szDict, NID_RLLN);
jdeNIDcpy(dsSelect[3].Item1.szTable, NID_F4211);
dsSelect[3].lpValue = (void *)&lpDS->mnRLLN;
dsSelect[3].nValues = (ushort)1;
dsSelect[3].nAndOr = JDEDB_ANDOR_AND;
dsSelect[3].nCmp = JDEDB_CMP_EQ;

JDEDBReturn = JDE_SetSelection(hReqF4211, dsSelect, (ushort)4, JDEDB_SET_REPLACE);

if (JDEDBReturn == JDEDB_PASSED)
{
JDEDBReturn = JDB_SelectKeyed(hReqF5742008, (ID)0, (void *)NULL, (short)(0));
while (JDEDBReturn == JDEDB_PASSED)
{
JDEDBReturn = JDB_Fetch(hReqF4211, (void *)&dsF4211, (int)0);
if (JDEDBReturn == JDEDB_PASSED)
{
/* NEVER GETS HERE */
}
}
}

 

[craig_welton]

Why is your SelectKeyed using a totally different Request Handle (hReqF5742008)?

Also, review the jdedebug.log and jde.log to see if there are errors or SQL that isn't what you expect.

 

[FrankCLT]

That "hReqF5742008" has been replaced with "hReqF4211" in code, I was copying things and didn't change that instance for this example... but it is correct. I'm testing this through Visual Studio and Object Browser \ Test Function feature.




This is what is actually in code....

JDB_ClearSelection(hReqF4211;

jdeNIDcpy(dsSelect[0].Item1.szDict, NID_RORN);
jdeNIDcpy(dsSelect[0].Item1.szTable, NID_F4211);
dsSelect[0].lpValue = (void *)lpDS->szRORN;
dsSelect[0].nValues = (ushort)1;
dsSelect[0].nAndOr = JDEDB_ANDOR_AND;
dsSelect[0].nCmp = JDEDB_CMP_GT;

jdeNIDcpy(dsSelect[1].Item1.szDict, NID_RCTO);
jdeNIDcpy(dsSelect[1].Item1.szTable, NID_F4211);
dsSelect[1].lpValue = (void *)lpDS->szRCTO;
dsSelect[1].nValues = (ushort)1;
dsSelect[1].nAndOr = JDEDB_ANDOR_AND;
dsSelect[1].nCmp = JDEDB_CMP_EQ;

jdeNIDcpy(dsSelect[2].Item1.szDict, NID_RKCO);
jdeNIDcpy(dsSelect[2].Item1.szTable, NID_F4211);
dsSelect[2].lpValue = (void *)lpDS->szRKCO;
dsSelect[2].nValues = (ushort)1;
dsSelect[2].nAndOr = JDEDB_ANDOR_AND;
dsSelect[2].nCmp = JDEDB_CMP_GT;

jdeNIDcpy(dsSelect[3].Item1.szDict, NID_RLLN);
jdeNIDcpy(dsSelect[3].Item1.szTable, NID_F4211);
dsSelect[3].lpValue = (void *)&lpDS->mnRLLN;
dsSelect[3].nValues = (ushort)1;
dsSelect[3].nAndOr = JDEDB_ANDOR_AND;
dsSelect[3].nCmp = JDEDB_CMP_EQ;

JDEDBReturn = JDE_SetSelection(hReqF4211, dsSelect, (ushort)4, JDEDB_SET_REPLACE);

if (JDEDBReturn == JDEDB_PASSED)
{
JDEDBReturn = JDB_SelectKeyed(hReqF4211, (ID)0, (void *)NULL, (short)(0));
while (JDEDBReturn == JDEDB_PASSED)
{
JDEDBReturn = JDB_Fetch(hReqF4211, (void *)&dsF4211, (int)0);
if (JDEDBReturn == JDEDB_PASSED)
{
/* NEVER GETS HERE */
}
}
}

 

[BOster]

Also, review the jdedebug.log and jde.log to see if there are errors or SQL that isn't what you expect.

This. Even when things work I like to grab the generated SQL statement from the debug log just to make sure I have constructed my query the way I think I have.

I would use JDB_SetSelectionX version. It is the later version of the call.
Also I assume the JDEDB_CMP_GT were intentional????

Also just a little pattern I like to use that helps me (or someone behind me) from introducing a bug. Note the nSelIdx variable. Makes it easy to quickly change your WHERE clause by adding/removing clauses w/o having to edit everything before/after.

nSelIdx = 0;
jdeNIDcpy( select[nSelIdx].Item1.szDict,        NID_KCOO);
jdeNIDcpy( select[nSelIdx].Item1.szTable,        NID_F4201);
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->szCompany;
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_DOCO);
jdeNIDcpy( select[nSelIdx].Item1.szTable,        NID_F4201);
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->OrderNumber;
select[nSelIdx].nValues                                = 1;
select[nSelIdx].nCmp                                    = JDEDB_CMP_EQ;
select[nSelIdx].nAndOr                                = JDEDB_ANDOR_AND;
select[nSelIdx++].nParen                            = JDEDB_PAREN_NONE;

idJDEDBReturn = JDB_SetSelectionX(hReqSalesHdr, select, nSelIdx, JDEDB_SET_REPLACE);

 

[FrankCLT]

Yes on the GT, I was testing some other things. I did get it working, but with the JDE_SetSelection. I will change it up to the current version of the call. Thank you!