How to simulate SELECT SUM

[Ray Justus]

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: [email protected]

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]

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]

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]

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
[email protected]


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