Re: reasonable SQL syntax - Join

[CRPence <CRPbottle@xxxxxxxxx>]

  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.