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



donna lester wrote:

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.
I am not familiar using "With" in SQL , can some one help me to get improved SQL.


Donna -
One way to do this is to perform a "corresponding" join on the
ATSUSFILE like this:

(Assuming that the column names in both files are the same...)

SELECT SUM(CASE WHEN XX.PVAL>0 THEN XX.PVAL ELSE 0 END) AS USVAL
FROM USA/ATSFILE XX
WHERE XX.PCNTRY<>'US'
AND XX.PDATE BETWEEN 20070401 AND 20070630
AND XX.PSYMBOL IN('7V','99','03','03')
AND XX.PSUBSYM ='B'
AND EXISTS
(
SELECT *FROM ATSUSFILE YY where XX.PFA = YY.PFA and XX.POFC=YY.POFC and XX.PACC=YY.PACC
)

Regards,
Steve Landess
Austin, Texas



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.