How to convert this SQL into JDE Event Rules

Hi,ALL
Can anyone tell me how to make this Simple SQL into ER
"SELECT SUM(SDUORG) FROM F4211 GROUP BY SDAN8,SDLITM,SDMCU"
I wanna know how to get SUM(SDUORG) in Tools Deverlopment.
P.S.
I know that
1st, I should Use Table I/O to SELECT
2nd,Use the Fetch Next
3rd,Use While to Fetch all the Records
....
But I can't do a SUM after Fetch the Records
EX. If I have 5 different [SDUORG] fetched ,how can I make this 5 [SDUORG] to do a SUM?

Can Anyone give a SAMPLE to convert this Simple SQL to a ER.
Thanks very much~~~

What version you is? Also, are you 1000000% sure that UORG is the same UOM for that select?

Newer versions let you build that SUM with an aggregate, but that won't help the uom conversion issues. In a select/fetch next loop you accumulate the values man-you-all-ee.

Hi,WhippingBoy
E8.12 Releases

The UOM conversion is not the issue,coz' I know how to deal with it.
But I can't find there is any AGGREGATE,is there any functions to do SUM?
I used Debugger to trace the code what i wrote to test, I just found in the select/fetch next loop,all the values the system had fetched, but i don't know when end while ,how can i aggregate these UORG to a SUM?
Can u tell how to do a SUM after the select /fetch next loop end


Another Example

0005 VA grd_ItemFeatureCategory = "MAXQT"
0006 F4107.Select
VA grd_55_IdentifierShortItem_ITM = TK Item Number - Short
VA grd_ItemFeatureCategory = TK Item Feature Category
0007 F4107.Fetch Next
VA grd_55_Description_QTY <- TK Short Description
0008 While SV File_IO_Status is equal to CO SUCCESS
0009 F4107.Fetch Next
VA grd_55_Description_QTY<- TK Short Description
0010 End While

After the End While, How can I do a SUM to all the VA grd_55_Description_QTY?

F4211.Select
F4211.FetchNext
VA_UnitstrnsQty_UORG <--SD.UORG

While SV File I/O Status is Equal to CO_Success

VA_TotalQty_UORG=VA_UnitstrnsQty_UORG+VA_TotalQty_UORG

F4211.FetchNext
VA_UnitstrnsQty_UORG <--SD.UORG

End While

Just curious "later versions" of what? JDE? That would be very welcome news since I have always thought that a lack of aggregate functions in the JDEBASE api was a major ommission.

Yes, JDE, but I don't know when they were put in. 8.10 maybe?

Search through the source for JDB_SetAggregate, or JDB_AGGRGFUNC_SUM for examples.

JDEKDFN.h defines these functions:

JDB_AGGRGFUNC_MIN,
JDB_AGGRGFUNC_MAX,
JDB_AGGRGFUNC_SUM,
JDB_AGGRGFUNC_AVG,
JDB_AGGRGFUNC_COUNT,
JDB_AGGRGFUNC_SUM_DISTINCT,
JDB_AGGRGFUNC_AVG_DISTINCT,
JDB_AGGRGFUNC_COUNT_DISTINCT,
JDB_AGGRGFUNC_COUNT_ALL,
JDB_AGGRGFUNC_INVALID

The detail processing will get all the 5 records one at a time and for each one accumulate the value of SDUORG of into a variable. A the end of processing the detail records record (summary time after all records are fetched) take the accummulated value and use that. This summary time processing will be in a different section of course if this is a UBE.

I believe they were delivered in 8.94 foundation along with version 8.10 (as WhippingBoy mentioned). 8.94 was advertised as improving the performance of Financial Report Writer. I think the aggregate JDB functions played a big part in that improvement.

If you want to see them in action download the Plant Manager Dashboard from edelivery.oracle.com (B42713-01). You don't have to install it into your E1 system. You can just unpack it and take a look at the business functions delivered with it. They live in B42713-01.zip\planner\Package\PM3\source.cab. Each metric/aggregate calculation displayed on the PM dashboard is calculated by a business function that uses the JDB aggregates.

Now, if they can just deliver aggregate functions to business views ...

*sigh* I wish this was available in Xe... it would make so many things so much easier...

Hi ,Guys
Thanks for ur help
I think that I can make it now

P.S. I am newbie in JDE, This site really a great place with a lot of elites

In the case of XE or in current releases where you want to back a UBE section against an aggregate view, I would use the "Virtual Table" approach that has been discussed many times on the list:

1. Create a native database view that does the aggregation you want.
2. Define the native view in OneWorld as a table but do not generate it.
3. Put a business view over the table if you want to use it in a UBE

As an example, I used this approach with a particular OneWorld site that made heavy use of subledgers in their accounting. Most reports run over the F0902 were forced to roll-up subledger information. In the case of one UBE it was taking 1 hour to run where it had previously run in 5 minutes under FASTR (this was a recently migration former World site). I built a view over the F0902 that did the roll-up aggregation. I defined a OneWorld table with the same name and structure as the view. I then put a business view on top of it and tied the UBE driving section to the view. The result was a UBE that ran in 5 minutes -- the same time as the previous World FASTR.

That is just one example. In fact this approach is still useful even with the new aggregate JDB functions. It is the only way that I know of to achieve aggregation in a business view at this point.

In While loop keep one variable which will keep on accumulating SDUORG values by self adding itself. So when ever loop gets completed, the variable will have the total sum of SDUORG for all the records...............

Virtual tables don't really work with aggregrates because any field that you may want to query on must be defined in the resulting recordset and thus must be included in the group by clause of the aggregate. For example, if you want to find the total Order Quantity by Shipping Branch Plant for a given time frame your SQL would look like this (outside of JDE):

SELECT SDMCU, sum(SDUORG) as Total FROM dbo.F4211
WHERE SDTRDJ > '1/1/2008' AND SDTRDJ < '1/31/2008'
GROUP BY SDMCU


To try and do this in a virutual table, you would have to write SQL something like this (in your SQL view):

SELECT SDMCU, SDTRDJ, sum(SDUROG) as SDGNUM2 FROM dbo.F4211
GROUP BY SDMCU, SDTRDJ

Then your application would set the where clause on the date, but since the date is included in the group by, you are not really getting the result set you want... you are gettting totals by Branch Plant and by Date. Now it may reduce the number of records you would need to loop through in you JDE application thus increasing performance (as you stated), but it doesn't give the full solution that true aggregate functionality would give you.

This is a good post, I'm not aware however where in event rules you do the assignment to access the record variable just fecthed.

"VA_UnitstrnsQty_UORG <--SD.UORG"

can you assist?
(using 8.11sp1 with tools 8.95)



-------------------------------
F4211.Select
F4211.FetchNext
VA_UnitstrnsQty_UORG <--SD.UORG

While SV File I/O Status is Equal to CO_Success

VA_TotalQty_UORG=VA_UnitstrnsQty_UORG+VA_TotalQty_UORG

F4211.FetchNext
VA_UnitstrnsQty_UORG <--SD.UORG

End While
------------------------------------------

Hi,ALL
how to make this Simple SQL into ER

SELECT Sum(SDSOQS) AS CANTOTAL FROM F4211
WHERE SDLITM='TP41001-3F-STD' AND SDDOCO IN (66,68)
AND SDDCTO='S9'
I wanna know how to get SUM(SDUORG) in Tools Deverlopment.
P.S.

I know that
1st, I should Use Table I/O to SELECT
2nd,Use the Fetch Next