How to use JDE API to use business view or join tables

[nikiliu]

On Xe, there are JDB_OpenView JDB_CloseView to open and close BSVW. I've found in the forum people can do JDB_SetSelctionX on BSVW but it always fails for me (no problem if the API is done on a Table). The jde.log says 'JDB9900269 - Failed to find requested Table -- V4211J' (yes, this is the BSVW I want to use) though the Open/CloseView were successful.

My question is I want to be able to select fields on two tables and what should I do? I have no problems to use all the JDB_ functions in C BSFN on a single table, but yet to find a way (or any standard JDE BSFNs) to do this either on a view or on multiple tables. Any help will be appreciated.

 

[nikiliu]

I think I have figured out the problem for the table V4211J not found issue -- I need to pass in the table but not the view to JDB_SetSelectionX structure. However, I would still appreciate that if anyone can point me to how I can select on multiple tables (do I always have to use a BSVW?). I mean if I want to select fields from F4201 and F4211 (for example, in SQL it is like 'select fielda, fieldb from f4201, f4211 where f4201.doco=f4211doco ... ), can I do this without a BSVW? Thanks.

 

[BOster]

If you think of JDB_OpenTable or JDB_OpenView as the SELECT statement and JDB_SetSelectionX as the WHERE clause it might makes things clearer.

In your example you do not need to do a where clause such as F4201.doco == F4211.doco since you define that in your business view. So you can kind of think of JDB_OpenView as as a SELECT statment with JOIN statements. The best thing to do is turn on your debug log and review the SQL that gets generated for various JDB api combinations. However, using your example to further illustrate, lets say you want to see all order lines where the shipping branch plant matches the header branch plant (F4201.shmcu = F4211.sdmcu) OR some arbitray value say '1234', What you want is the following sql (full sales order keys ommitted in example)

select shdoco, shan8, shshan, shmcu, sdlnid, sditm
from f4201
inner join f4211 on f4201.shdoco = f4211.sddoco
where f4201.shmcu = f4211.sdmcu OR f4211.sdmcu = 1234

You would create a BV that joins F4201 to F4211 on the order keys (doco) then create the following C code:


nSelIdx = 0;
jdeNIDcpy( dsSelect[nSelIdx].Item1.szDict, NID_MCU);
jdeNIDcpy( dsSelect[nSelIdx].Item1.szTable, NID_F4201 );
dsSelect[nSelIdx].Item1.idInstance = (ID)0;
jdeNIDcpy( dsSelect[nSelIdx].Item2.szDict, NID_MCU);
jdeNIDcpy( dsSelect[nSelIdx].Item2.szTable, NID_F4211);
dsSelect[nSelIdx].Item2.idInstance = (ID)0;
dsSelect[nSelIdx].lpValue = NULL;
dsSelect[nSelIdx].nValues = 0;
dsSelect[nSelIdx].nCmp = JDEDB_CMP_EQ;
dsSelect[nSelIdx].nAndOr = JDEDB_ANDOR_AND;
dsSelect[nSelIdx++].nParen = JDEDB_PAREN_NONE;

jdeNIDcpy( dsSelect[nSelIdx].Item1.szDict, NID_MCU);
jdeNIDcpy( dsSelect[nSelIdx].Item1.szTable, NID_F4201 );
dsSelect[nSelIdx].Item1.idInstance = (ID)0;
jdeNIDcpy( dsSelect[nSelIdx].Item2.szDict, _J(""));
jdeNIDcpy( dsSelect[nSelIdx].Item2.szTable, _J(""));
dsSelect[nSelIdx].Item2.idInstance = (ID)0;
dsSelect[nSelIdx].lpValue = &mnOrderNum; // order 1234
dsSelect[nSelIdx].nValues = 1;
dsSelect[nSelIdx].nCmp = JDEDB_CMP_EQ;
dsSelect[nSelIdx].nAndOr = JDEDB_ANDOR_OR;
dsSelect[nSelIdx++].nParen = JDEDB_PAREN_NONE;

if(JDB_OpenView(hUser, ID_BV564201A, (JCHAR *)NULL, &hReq) != JDEDB_PASSED || //SELECT, JOIN clause

JDB_SetSelectionX(hReq, dsSelect, nSelIdx, JDEDB_SET_REPLACE) != JDEDB_PASSED || //WHERE clause

JDB_SelectKeyed(hReq, (ID)0, (void *)NULL, (short)0) != JDEDB_PASSED) //execute sql statment
{
//error handling code
}

while(JDB_Fetch(hReq, (void *)&dsRec, 0) == JDEDB_PASSED)
{
//loop records
}

 

[nikiliu]

Thanks BOster, this is the best explanation I've even seen. I always wonder why you need to do the JDB_SelectKeyed after JDB_SetSelectionX (as opposed to simply doing a keyed fetch) but the execute SQL statement makes this very clear. Does it mean if I have to do a select on two or more tables (as in your example joining SO Header and Detail together) I have to use a BSVW? I mean can I use JDB_OpenTable on both F4201 and F4211, and do the Select structure as your example. Will it work?