Re: SQL needed

Another way of doing what you want
select SUM(CASE WHEN A.PVAL>0 THEN A.PVAL ELSE 0 END)
AS USVAL FROM USA/ATSFILE
WHERE A.PCNTRY<>'US' AND
A.PDATE BETWEEN 20070401 AND 20070630 AND
A.PSYMBOL IN('7V','99','03','03') AND A.PSUBSYM ='B'
and exists
(select * from USA/ATSUSFILE B where
A.PFA = B.PFA and
A.POFC = B.POFC and
A.PACC = B.PACC)



Alan Shore

NBTY, Inc
(631) 244-2000 ext. 5019
AShore@xxxxxxxx
"If you're going through Hell, keep going" - Winston Churchill



rpg400-l-bounces@xxxxxxxxxxxx wrote on 07/27/2007 12:36:26 PM:

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.