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




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.


As an Amazon Associate we earn from qualifying purchases.

This thread ...

Follow-Ups:
Replies:

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.