select SUM(CASE WHEN PVAL>0 THEN PVAL ELSE 0 END) AS USVAL
FROM USA/ATSFILE a
-- Join the file
JOIN USA/ATSUSFILE b ON
a.pfa = b.pfa and a.pofc = b.pofc and a.pacc = b.pacc
--
WHERE PCNTRY<>'US' AND
PDATE BETWEEN 20070401 AND 20070630 AND
PSYMBOL IN('7V','99','03','03') AND PSUBSYM ='B'
Michael Schutte
Admin Professional
"The Secret's the Sauce! Enjoy our new Bob-B-Q Pulled Pork Knife & Fork
Sandwich!"
donna lester
<donna_lester_ny@
yahoo.com> To
Sent by: RPG programming on the AS400 /
rpg400-l-bounces@ iSeries <rpg400-l@xxxxxxxxxxxx>
midrange.com cc
Subject
07/27/2007 12:36 SQL needed
PM
Please respond to
RPG programming
on the AS400 /
iSeries
<rpg400-l@midrang
e.com>
select SUM(CASE WHEN PVAL>0 THEN PVAL ELSE 0 END)
AS USVAL FROM USA/ATSFILE
WHERE PCNTRY<>'US' AND
PDATE BETWEEN 20070401 AND 20070630 AND
PSYMBOL
IN('7V','99','03','03') AND PSUBSYM ='B'
I want to improve the above SQL so that sum should be calculated only when
pfa,pofc,pacc of ATSFILE equal to pfa,pofc&pacc of ATSUSFILE.
Iam not familiar using "With" in SQL , can some one help me to get improved
SQL.
Thanks,
Donna.
---------------------------------
Moody friends. Drama queens. Your life? Nope! - their life, your story.
Play Sims Stories at Yahoo! Games.
--
This is the RPG programming on the AS400 / iSeries (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.
midrange.com
