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




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.

As an Amazon Associate we earn from qualifying purchases.

This thread ...

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.