× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



For an INNER JOIN [the default for JOIN clause syntax] the query rewrite should really have effected the same query; i.e. irrespective of the selection being on the WHERE clause or ON. Perhaps one or both of the query requests went CQE [which does not have query rewrite; or an older release where SQE query rewrite is not so robust] or the faster request was performed second for which paged\cached impacted the test results?

Also the SUM() aggregate functions imply this is a GROUP BY query; implicit, for lack of the GROUP BY clause, effects one group over all selected rows. Thus the COALESCE would be valuable for the given scenario when no rows are selected, because a SUM of now rows is the null value.

FWiW this is not RPG related.

Regards, Chuck

Michael_Schutte wrote:
First off, Since you are not doing a LEFT JOIN, there's no need
to use the COALESCE function unless SSVNDCHS has null values in
those fields.

I actually prefer this method since you are doing a regular
join.

Exec SQL
Select :CurYYYYMM,
Coalesce(Sum(s.VCSALS), 0),
Coalesce(Sum(s.VCCGSL), 0),
Coalesce(Sum(s.VCGMGN), 0),
0,
Coalesce(Sum(s.VCCASH), 0),
Coalesce(Sum(s.VCUNSH), 0),
Coalesce(Sum(s.VCCDAM), 0),
Coalesce(Sum(s.VCNMGN), 0),
0
Into :CYT
From SSVNDCHS s Join DMCUSMST c
On s.VCCSNR = c.CUSNR
and s.VCYYMM = :CurYYYYMM
and c.SLSNR = :srslnr;

If you put your record selections in the "ON" clause, you'll
select records from the two files before the join occurs so
there are less records being joined. FYI, I have tested this
with statements of my own. A webpage went
from 3 minutes to load to just 1 second by using this method.


As an Amazon Associate we earn from qualifying purchases.

This thread ...


Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.