Re: Can SQL do this?

This seems to work for me:

SELECT a.REGION, a.DISTRICT, a.COLOR, RED, YELLOW, BLUE ,
case when a.color = 'R' then red
         when a.color = 'Y' then yellow
         else blue end as unit,
case when a.color = 'R' then dec(dec(red,3,0)/dec(total,3,0),5,4)
         when a.color = 'Y' then dec(dec(yellow,3,0)/dec(total,3,0),5,4)
         else dec(dec(blue,3,0)/dec(total,3,0),5,4) end as alloc
FROM sqltest a,
(select region,color, sum(case when color = 'R' then red
                                                     when color = 'Y' then
yellow
                                                     else blue end) as total
from sqltest
group by region,color) x
where a.region = x.region and a.color = x.color

If the files are large, I'm not sure how this will perform however.

Pete

----- Original Message -----
From: "Dan" <dbcerpg=/E1597aS9LQAvxtiuMwx3w@xxxxxxxxxxxxxxxx>
Newsgroups: gmane.comp.lang.as400.rpg
Sent: Monday, April 21, 2003 9:19 PM
Subject: Can SQL do this?


> I currently have SQL-generated output from 3 joined files that looks like
this:
>
> Region  District  Color   #Red   #Yellow   #Blue
> ------  --------  -----  ------   ------  ------
> NER     BOS         Y       25        7      47
> NER     NYC         Y       18        4      20
> NER     NAN         Y       14        9      76
> NER     BAN         R       17        9      62
> NER     PIT         R       11        2      50
> NER     NAN         R       34        9      33
>
> I need to end up with this result (two new columns):
>
> Region  District  Color   #Red   #Yellow   #Blue   UNITS  ALLOCATION
> ------  --------  -----  ------   ------  ------   -----  ----------
> NER     BOS         Y       25        7      47       7       .3500
> NER     NYC         Y       18        4      20       4       .2000
> NER     NAN         Y       14        9      76       9       .4500
> NER     BAN         R       17        9      62      17       .2741
> NER     PIT         R       11        2      50      11       .1774
> NER     NAN         R       34        9      33      34       .5483
>
> The UNITS column needs to be derived from one of the #Red, #Yellow, or
#Blue values, depending on
> the value of Color.  I.e. (how I would do it in RPG):
>    Select
>    When   Color = 'R'
>    Eval   UNITS = #Red
>    When   Color = 'Y'
>    Eval   UNITS = #Yellow
>    Other
>    Eval   UNITS = #Blue
>    Endsl
>
> THEN...  I also need to get an ALLOCATION of the units for the Region and
Color.  Note that, for
> Region 'NER' and Color 'Y', there are a total of 20 units.  District 'BOS'
has 7 of those 20 units
> so, doing the math, the allocation is 0.35.  The sum of the ALLOCATION
column for a given
> Region/Color always adds up to (approximately) 1.00.
>
> If SQL can do all this, I may well be able to eliminate one RPG program.
>
> TIA, Dan
>
> __________________________________________________
> Do you Yahoo!?
> The New Yahoo! Search - Faster. Easier. Bingo
> http://search.yahoo.com
> _______________________________________________
> This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing list
> To post a message email: RPG400-L=Zwy7GipZuJhWk0Htik3J/w@xxxxxxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo.cgi/rpg400-l
> or email: RPG400-L-request=Zwy7GipZuJhWk0Htik3J/w@xxxxxxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/rpg400-l.
>
>