Help! JDB_FetchColsAggregate

[jolly]

Hi All,

Has anybody got this API to work? It always fails for me and I get the following error logged:


7792/6460 WRK:Starting jdeCallObject Wed Mar 21 10:25:59.724000 Jdb_exef.c6475
JDB98000055 - Failed to swap column list to requested columns. Check whether table is opened with all the columns.

If I use the simple JDB_FetchAggregate I get the correct answer so everything else seems correct. I suspect the problem is somehow in the select list for the table open vs the lpStruct passed to fetch the columns into.

In my 8.12 code set, there is not one single example of a JDB_FetchColsAggregate and there are no specific references to the function on the KG or Google!

It is documented in the API help file as follows:

Syntax
JDEDB_RESULT JDB_FetchAggregate (HREQUEST hRequest, void * lpStruct, void * lpAggregateValue)

Parameter
Notes
Usage

hRequest
Valid request handle.
This is the request handle by which JDB will identify all requests made to the particular table.

lpStruct
Pointer to ordinary column results structure or NULL
If no structure is to be used then pass NULL

lpAggregateValue
Pointer to aggregate results structure or NULL
If no structure is to be used then pass NULL


Return Value
Returns JDEDB_PASSED when the API succeeded or JDEDB_FAILED otherwise.

H have attached the source and include files for the business function.

Any ideas. Am standing by to kick myself but have been staring at this for way to long and can no longer see forest for trees!

Thanks
JohnO

 

[jolly]

Why is it that the answer always comes to oneself after asking the question?

I never did get JDB_FetchColsAggregate to work but Duh! JDBFetchAggregate can return the GroupBy columns anyway and that works fine.

FYI The code I have working now is:

idJDBReturn = JDB_FetchAggregate(hRequestF4111, &dsFetchF4111Cols, dsAggregateBuffer);

<slaps self>

 

[jolly]

Righto - having sorted that problem out I write my next aggregate function to find MIN(ILTRDJ) from F4111.

And it fails because the JDB Aggregate functions only work on numeric data...

WTF ?!?!?!

Setting aside this ridiculous limitation - dates are numeric in JDE databases anyway!

 

[BOster]

With date fields you could probably create a virtual table (sql view as the recordset source for a custom JDE table) over the physical table that "casts" the date as a math_numeric. Then you could use the aggregate api on the "converted" field in the v-table, once you have the value you would have to convert it from math_numeric back to a JDEDATE. A little bit of work but you would still have the performance benefit that the aggregate api gives you.

 

[jolly]

Hi,

I re-coded the function as a select with an order by to return the lowest date first, and just fetch the first record.

Strangely enough, I checked the select min() against the select/order-by/fetch and the latter was faster by some way (there was a suitable index already on the table to help).

 

[amiii]

I understand these APIs works well for tables. Does it work for Views as well or can suggest anything related to Views with these APIs?

 

[amiii]

Does APIs work for JDE Views as well ???

I understand these APIs works well for tables. Does it work for Views as well or can suggest anything related to Views with these APIs?

 

[8152]

Hello amiii

Yes. You can use Aggregate function APIs with views. For example, I used JDB_OpenViewExtended to open a joint business view and used Aggregate function APIs.

8152