How to add 'Where' and 'Sort' capabilities to data selection in a 'C' business functi

[Al Dinelt]

Hi,
I am writing a custom 'C' business function and have 25 years experience with the 'C' language. By looking at existing business functions, I have sort of figured out how to retrieve data from the F060116 table using the JDE API's. Don't fully understand it yet, but I am getting back the data I want. What I would like to do is limit the amount of data I am getting back (like adding a 'Where' clause to an SQL statement) as well as sort the data. I am at a total loss here.

Any help you can provide would be greatly appreciated.

Also, is there anywhere where the API's are documented?

Thanks in advance...
Al

 

[JMR]

look for examples that use the following API's:

JDB_SetSelectionX
JDB_SetSequencing

 

[nkuebelbeck]

http://www.patwel.com/APIs/898jdeapis.chm

 

[BOster]

That help file is a great resource and I generally have it open all the time. But I will caution you, only a very precious few of the myriad JDE C API calls are documented there. You can find documentation on other JDE C API sprinkled about all over the place in various Oracle documents but even then I doubt Oracle has even half of their APIs properly documented. Generally you have to find a "related" API, go to the .h file to find others and then either infer what they do from looking at the sig and/or find examples in existing code. Generally though, they are not too hard to figure out once you find them - the trick is finding them.

 

[BOster]

One other thing. The JDEBASE API is pretty well put together IMO (aside from a few weird "tricks" you have to do every once in a while). Once you find all the pieces, start to combine them in various ways along with Business Views, and if need be, SQL views. You can really do some pretty complex SQL statements. Obviously not nearly as complex as what you can do in plain ad-hoc SQL or some other language like Java but still not bad considering the framework that it runs under.

Also, since you are on TR9.1.5.5 look at this document on Oracle's site. They have added some additional calls. I have not used the new JDEBASE calls yet but the look interesting.

Doc 1952465.1

 

[Al Dinelt]

Thanks for all the great info folks. Figured out why the text in Patel's .CHM file wasn't displaying for me. I had to go into the properties on the file and 'unblock' it.

I have got the SetSequencing working okay for me, but now I am having a bit of a problem with the SetSelection.

Let's say I wanted to select an employee only if their union code was MGTCNT or MGTFT or MGTPT. When declaring the SELECTSTRUCT variable, I essentially set it up to have 4 elements. One for the address number and 3 for the different union codes.

So, my code would look like this:

jdeNIDcpy(SelectF060116[0].Item1.szDict, NID_ANPA);
jdeNIDcpy(SelectF060116[0].Item1.szTable, NID_F060116);
SelectF060116[0].Item1.idInstance = 0;
jdeNIDcpy(SelectF060116[0].Item2.szDict, _J(""));
jdeNIDcpy(SelectF060116[0].Item2.szTable, _J(""));
SelectF060116[0].Item2.idInstance = 0;
SelectF060116[0].lpValue = &dsF060116.yaan8;
SelectF060116[0].nValues = 1;
SelectF060116[0].nAndOr = JDEDB_ANDOR_AND;
SelectF060116[0].nCmp = JDEDB_CMP_EQ;

jdeNIDcpy(SelectF060116[1].Item1.szDict, NID_UN);
jdeNIDcpy(SelectF060116[1].Item1.szTable, NID_F060116);
SelectF060116[1].Item1.idInstance = 0;
jdeNIDcpy(SelectF060116[1].Item2.szDict, _J(""));
jdeNIDcpy(SelectF060116[1].Item2.szTable, _J(""));
SelectF060116[1].Item2.idInstance = 0;
SelectF060116[1].lpValue = _J("MGTCNT");
SelectF060116[1].nValues = 1;
SelectF060116[1].nAndOr = JDEDB_ANDOR_AND;
SelectF060116[1].nCmp = JDEDB_CMP_EQ;

jdeNIDcpy(SelectF060116[2].Item1.szDict, NID_UN);
jdeNIDcpy(SelectF060116[2].Item1.szTable, NID_F060116);
SelectF060116[2].Item1.idInstance = 0;
jdeNIDcpy(SelectF060116[2].Item2.szDict, _J(""));
jdeNIDcpy(SelectF060116[2].Item2.szTable, _J(""));
SelectF060116[2].Item2.idInstance = 0;
SelectF060116[2].lpValue = _J("MGTFT ");
SelectF060116[2].nValues = 1;
SelectF060116[2].nAndOr = JDEDB_ANDOR_OR;
SelectF060116[2].nCmp = JDEDB_CMP_EQ;

jdeNIDcpy(SelectF060116[3].Item1.szDict, NID_UN);
jdeNIDcpy(SelectF060116[3].Item1.szTable, NID_F060116);
SelectF060116[3].Item1.idInstance = 0;
jdeNIDcpy(SelectF060116[3].Item2.szDict, _J(""));
jdeNIDcpy(SelectF060116[3].Item2.szTable, _J(""));
SelectF060116[3].Item2.idInstance = 0;
SelectF060116[3].lpValue = _J("MGTPT ");
SelectF060116[3].nValues = 1;
SelectF060116[3].nAndOr = JDEDB_ANDOR_OR;
SelectF060116[3].nCmp = JDEDB_CMP_EQ;

Where I am confused is at which point do I set the .nAndOr value to JDEDB_ANDOR_OR? Does the .nAndOr value apply to the element that it resides with or to the next element in the group? Is the code I have above correct or should the 2nd element be JDEDB_ANDOR_OR instead of JDEDB_ANDOR_AND?

Thanks...
Al

 

[craig_welton]

I think the best way to make that work is to use nCmp = JDEDB_CMP_IN, set nValues = 3 and pass an array of char arrays with the 3 values you want to lpValue . That way you get WHERE YAAN8 = AB# AND YAUN IN ('MGTCNT','MGTFT','MGTPT').

Nice and concise.

To answer you question the AND|OR is applied before the element you are defining in the SELECTSTRUCT. The NEWSELECTSTRUCT and JDB_SetSelectionX give you the ability to assign parentheses.

Craig

 

[BOster]

One quick thing to save you some frustration when doing an IN clause on a string field - or I might just be pointing out the obvious... it wasn't obvious to me the first time I used JDEDB_CMP_IN.

When using an IN clause (JDEDB_CMP_IN or JDEDB_CMP_NI) as Craig suggested on a string field, make sure that each element in your string array is the exact size of the field in the database. In other words don't make an array element just big enough to hold each string value or too big either, make sure it is the same size as the data dictionary defininition (with the null term of course).

Typing this code from memory so I think I am giving a correct example...


Incorrect:

JCHAR   szAlphaNames[][11] = {_J("Brian", _J("Oster"), _J("Smith")};		//big enough to hold all the strings, but is smaller than F0101.ALPH
....
jdeNIDcpy( select[nSelIdx].Item1.szDict, NID_ALPH);
jdeNIDcpy( select[nSelIdx].Item1.szTable, NID_F0101 );
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 = szAlphaNames;
select[nSelIdx].nValues = DIM(szAlphaNames);
select[nSelIdx].nCmp = JDEDB_CMP_IN;
select[nSelIdx].nAndOr = JDEDB_ANDOR_AND;
select[nSelIdx++].nParen = JDEDB_PAREN_NONE;

Correct:

F0101 recAddressBook={0};		
JCHAR   szAlphaNames[][DIM(recAddressBook.abalph)] = {_J("Brian", _J("Oster"), _J("Smith")};		//expands to szAlphaNames[][41]
....

 

[Al Dinelt]

Thanks folks. You have been extremely helpful and it is much appreciated.