Can SQL do this?

[Dan <dbcerpg@xxxxxxxxx>]

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