Re: reasonable SQL syntax - Join

It appears that you doing the coalesces on the fields from table SSVNDCHS.
Since you have that file first and just doing a join, how would this return
records if they don't exist? A join will only return records where records
exist in both tables.


Also, for performance you shouldn't select anything that you know to be
true. i.e. in your where clause you are selecting a date equal
to :CurYYYYMM, so why put that as part of your select fields? Of course I
don't know what :CYT is either.

Use the COALESCE for fields coming from a file that is being joined on a
LEFT JOIN.


--

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





Jeff Crosby
<jlcrosby@dilgard
foods.com> To
Sent by: "RPG programming on the IBM i /
rpg400-l-bounces@ System i" <rpg400-l@xxxxxxxxxxxx>
midrange.com cc

Subject
12/08/2009 10:18 Re: reasonable SQL syntax - Join
AM


Please respond to
RPG programming
on the IBM i /
System i
<rpg400-l@midrang
e.com>






There are no null values in the fields, but there is a chance (slight)
there
won't be any records found. I've used Coalesce in the past for that.
What's the better way?

On Tue, Dec 8, 2009 at 9:34 AM, <Michael_Schutte@xxxxxxxxxxxx> 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

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.

--
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.

--
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.