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.
