Jeff
I prefer the first one, because it makes it clear that you are JOINing
files. I have a feeling that the optimizer changes the second one to a
real JOIN, but I'm not sure. It also helps make things clearer as to
what indexes you want - JOIN fields should precede WHERE fields, as I
recall - or vice versa.
I don't know that either performs any better - maybe there is optimizer
optimization with the true JOIN, as it does not have to convert it.
Lots of speculation this early in the morning!
Vern
Jeff Crosby wrote:
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;
