JDB_FetchAggregate SUM function

[nkuebelbeck]

I'm trying to sum records in the F4981.FHNAMT using JDB_FetchAggregate. I've got it doing a sum by shipment number but when the number comes out of JDB_FetchAggregate in the buffer, it's not respecting the decimals of the data in the table.

example

SHPN | NAMT
1234 | 10.00
1234 | 10.00

2000 is what i'm getting from the aggBuffer[0] and not 20.00

I noticed because I was attempting to compare the sum to another mathnumeric value (ddict AA) and it's not returning 0 when doing MathCompare();

is there something I need to do to make this work other than manually dividing the aggBuffer[0] value?

 

[BOster]

I have never had to manipulate or adjust the returned aggregate values. You may want to post your relevant code. Also may want to turn on debug log and look at the SQL statement it generates.

 

[nkuebelbeck]

i've done the debugging. the returned struct only has the value, no decimal or precision data. Sql looks fine.

some code snips

jdeNIDcpy(dsF4981Agg[0].Item.szDict, (JCHAR *)NID_NAMT);
jdeNIDcpy(dsF4981Agg[0].Item.szTable, (JCHAR *)NID_F4981); 
dsF4981Agg[0].Item.idInstance = 0;
dsF4981Agg[0].nFunction = JDB_AGGRGFUNC_SUM;          
if(JDB_SetAggregate(hF4981, dsF4981Agg,1, JDB_AGGRGFUNCTION_OPT_FUNC_ONLY)==JDEDB_PASSED){
  /*more code*/
}

and

if (JDB_SetSelection(hF4981, zSelect, 3, JDEDB_SET_REPLACE)== JDEDB_PASSED){            	  	               
   if(JDB_SelectKeyed(hF4981,0, NULL,0) == JDEDB_PASSED){ 
      JDB_FetchAggregate(hF4981,NULL,dsAggBuffer);
      if(MathCompare(&dsAggBuffer[0],&dsF550124.fmaa)!=0){                     
         MathCopy(&lpDS->mnShipmentNumber,&dsAggBuffer[0]);
         bStop=TRUE;
         jdeErrorSet(lpBhvrCom, lpVoid, 1L,(JCHAR *)_J("55012511"),(LPVOID) NULL);                     
      }                  
   }
}

SQL from debugger SELECT SUM(FHNAMT) FROM TESTDTA/F4981 WHERE ( FHSHPN = 34786.000000 AND FHBLPB = 'P' AND FHDOC = 0.000000 )

 

[nkuebelbeck]

i found this in the api guide

"Similar to JDB_Fetch, this API will execute post fetch and currency triggers and perform currency conversion on all the ordinary select columns. Any aggregate data (if it exists) will not be passed to the triggers and no currency conversion will be performed on the aggregate data. Consumers of this API are responsible for performing any necessary currency conversion on the output aggregate data from this API."

might explain why F4981.FHNAMT isn't getting any mathnumeric love

 

[BOster]

I have never had to deal with any currency conversion so if that is enabled for your organization maybe that's the problem????? That is beyond my expertise. From quick glance at code and SQL statement, all look good to me.

Probably unrelated, but why are you copying the aggregate value to lpDS->mnShipmentNumber?

 

[nkuebelbeck]

i changed it to SUM the WGTS column and it respected the math_numeric struct.

woah is me

see attached

EDIT attached another one of NAMT

 

[nkuebelbeck]

I have never had to deal with any currency conversion so if that is enabled for your organization maybe that's the problem????? That is beyond my expertise. From quick glance at code and SQL statement, all look good to me.

Probably unrelated, but why are you copying the aggregate value to lpDS->mnShipmentNumber?

heh, that was temporary to see what was coming out of that function. bad idea cause shpn doesn't carry decimals anyway...

We have to deal with currency conversion :/

 

[nkuebelbeck]

my dirty solution

JDB_FetchAggregate(hF4981,NULL,dsAggBuffer);
dsAggBuffer[0].nCurrencyDecimals = 2;
dsAggBuffer[0].nDecimalPosition = 2;
if(MathCompare(&dsAggBuffer[0],&dsF550124.fmaa)!=0){                                          
   bStop=TRUE;
   jdeErrorSet(lpBhvrCom, lpVoid, 1L,(JCHAR *)_J("55012511"), (LPVOID) NULL);                     
}

 

[BOster]

i changed it to SUM the WGTS column and it respected the math_numeric struct.

So that would seem to give more evidence to the problem being caused by currency conversion.