• Introducing Dark Mode! Switch by clicking on the lightbulb icon next to Search or by clicking on Default style at the bottom left of the page!

How to simulate SELECT SUM

vbojan

Well Known Member
Hi List,

Is there any way in JDE to retrieve sum for some field without of fetching records (like "SELECT SUM (FieldName) FROM (TableName) WHERE…")? I’d like to avoid network traffic with:

TableName.Select
TableName.FetchNext
While SV File_IO_Status is equal to CO SUCCESS
TableName.Fetch Next
End While

and fetch the result only.

Thanks in advanced.

Bojan.

B733.2, SP 10, NT 4.0, SQL 7.0.
 

Zoltan_Gyimesi

Legendary Poster
Bojan,
In which type of object do you want it to do (e.g. APPL, UBE, BSFN)?
Zoltán

B7332 SP11, ESU 4116422, Intel NT4, SQL 7 SP1
(working with B7321, B7331, XE too)
 

vbojan

Well Known Member
Hi Zoltan,

I am looking for some BSFN or API to send me a sum only, without of fetching records on local station. I need somehow to send aggregate SQL Statement to server. DB Engine (SQL in my case) should do calculation on Enterprise server and send only a result to local workstation. That should improve performance in APPL and locally executed UBE. There is a solution to make BSFN with Select/FetchNext and map that BSFN to run on Enterprise server but I don’t like that solution too much.

Thanks for quick response.

Bojan.

B733.2, SP 10, NT 4.0, SQL 7.0.
 

michelle_perrin

Active Member
If you are using SQL you could create a SQL view over the table that you
wish to perform the sum on.

The view would need to contain the columns that will be in the where clause
so that you can use them to filter. These columns would also need to be in
the group by statement

eg:
SELECT XXCOL1, SUM(XXCOL2) AS XXSCOL2
FROM TESTDTA.F57XXX
GROUP BY XXCOL1

You can then create a JDE table definition that maps to the columns in this
view. Create the table definition but do not go as far as creating the
table via OW.

A couple of things to remember

1) You will not be able to view the data in this view via the Universal
Table Browser as this does a check to see if the table exists before
allowing the data to be displayed. You can however view / select the data
via a OW form or Table IO.
2) Make sure to set the permissions on the view so that JDE can select from
it.

Let us know if this works for you.

Michelle Perrin
 
Top