Re: SQL

["Carel Teijgeler" <coteijgeler@xxxxxxxxx>]

Donna,

Without testing, try something like:

WITH tmp AS (SELECT  keyfield, CASE WHEN pval = 2 THEN pquant ELSE 0 END AS
first,
                                    CASE WHEN pval = 1 THEN pquant ELSE 0
END AS second, pgross AS gross
                      FROM    usa/atsfile
                     )
SELECT       a.keyfield, SUM((first - second)) AS quant,  SUM(gross)
FROM          usa/atsfile a, tmp b
WHERE       a.keyfield = b.keyfield
AND            (first - second) = -300
GROUP BY  a.keyfield
HAVING       SUM(gross) = -300
ORDER BY  a.keyfield

With regards,
Carel Teijgeler

*********** REPLY SEPARATOR  ***********

On 1-8-2007 at 9:50 donna lester wrote:

> Hello.
>  My sql displays as below  
>  select pval,pquant,pgross from USA/ATSFILE 
> where pcnty='USA'
>  
> pval     pquant    pgross
>  1        100       100-
>  1        200       200-  
> 2        300       300
> 1        400       400-
> 2        100       100
>  PVAL will have only values either  1 or 2. I want to calculate
> PQUANT,PGROSS as below
>  PQUANT = (PQUANT WHEN PVAL IS 2)-(PQUANT WHEN PVAL IS 1) = 300-
> PGROSS should be calculated sum of all pgross values=300-
>  Can some one help  to combine these calculations in my above sql
> statement?
>
>
> Thanks,
>
> Donna.
>       
> ---------------------------------
> Be a better Globetrotter. Get better travel answers from someone who
knows.
> Yahoo! Answers - Check it out.
> -- 
> 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.
>
>
>
> -- 
> No virus found in this incoming message.
> Checked by AVG Free Edition. 
> Version: 7.5.476 / Virus Database: 269.11.0/929 - Release Date: 31-7-2007
> 17:26