Re: reasonable SQL syntax - Join

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 when
from 3 minutes to load to just 1 second by using this method.


--

Michael Schutte
Admin Professional



Bob Evans Holiday Farmhouse Feast, Serves 6-8 l $74.99
A complete homestyle meal TO GO, ready to heat at home, serve & enjoy!
Perfect for Thanksgiving, Christmas or holiday entertaining.
For more information, visit www.FarmhouseFeast.com



rpg400-l-bounces@xxxxxxxxxxxx wrote on 12/08/2009 09:25:01 AM:

I think the following 2 SQL statements should give me the same results.

Is

there a preference on which 'style' to use? Or a performance difference?

This is a once-a-month task, so performance isn't critical. Readability

is

more my concern and both are very readable IMO.


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
Where s.VCYYMM = :CurYYYYMM and
c.SLSNR = :srslnr;


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,
DMCUSMST c
Where s.VCYYMM = :CurYYYYMM and
s.VCCSNR = c.CUSNR and
c.SLSNR = :srslnr;


--
Jeff Crosby
UniPro FoodService/Dilgard
P.O. Box 13369
Ft. Wayne, IN 46868-3369
260-422-7531
www.dilgardfoods.com

The opinions expressed are my own and not necessarily the opinion of my
company. Unless I say so.
--
This is the RPG programming on the IBM i / System i (RPG400-L) mailing

list

To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.