• 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

Ray Justus

Reputable Poster
Bojan,

The JDE way is to use the cursor approach (Select, Fetch Next) but my SQL instructor says virtually all cursors can be replaced using Joins. I haven't had a chance to prove or disprove his statement yet but I'm going try it out the next time I write a UBE that doesn't need the detail. It may be worth your time to experiment with business views.

Ray W. Justus
Kitchell Corporation
1707 East Highland Avenue, Suite 100
Phoenix, AZ 85016-4679
(602) 631-6157
mailto: rjustus@kitchell.com

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.
 

vap

Active Member
Well, Bojan,

There is a way: IF you ultimately need performance:
Create a SELECT statement with SUM as one of the fields, make sure that your
field names comply with OW naming conventions
Create an OW TBLE object with the same names, Generate it.
Go to SQL manager of your liking (Query Analyzer, SQLPlus, STRSQL...),
run DROP Table [tablename], then
run CREATE VIEW as [your select statement here]

There was a discussion on this topic a while ago, you can find it if you
need more detailed instructions.

As far as I know it is not possible to force OneWorld application into using
GROUP BY clause (or bulk select). Any former JDE employees/gurus there can
share, why?

Regards,
Vladimir Ponomarev
 

vbojan

Well Known Member
Thank you all for your valuable advises and instructions. Simulating OW table with aggregate query works perfect and fast. It was much easier to set everything than I expected.

Thanks again,

Bojan.

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

RLew

Member
Bojan:
I don't quite understand all the steps that you went through to solve your SELECT SUM problem. Can you please explain to me what you did step by step to set it up.

Thanks,
Randy
rlew@kitchell.com


Thank you all for your valuable advises and instructions. Simulating OW table with aggregate query works perfect and fast. It was much easier to set everything than I expected.

Thanks again,

Bojan.

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



--------------------------
Visit the forum to view this thread at:
http://198.144.193.139/cgi-bin/wwwthreads/showflat.pl?Cat=&Board=OWDEV&Number=9521
 
Call Orchestrations From Excel – The Easy Way to Make the Orchestrator Work for You.
Top