RE: Can SQL do this?

["DeLong, Eric" <EDeLong@xxxxxxxxxxxxxxx>]

How about giving us the SQL that describes the joins.  As you've posed the
question, there's not any info about how the raw data is formatted. 

You will most likely be using CASE statements to make this work.  

RPG solution will probably be much faster than an all SQL solution, and SQL
solutions get VERY confusing and hard to understand as complexity increases.

Eric DeLong
Sally Beauty Company
MIS-Project Manager (BSG)
940-898-7863 or ext. 1863



-----Original Message-----
From: Dan [mailto:dbcerpg@xxxxxxxxx]
Sent: Monday, April 21, 2003 3:19 PM
To: rpg400-l@xxxxxxxxxxxx
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@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo.cgi/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.