Re: SQL needed

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