RE: Can SQL do this?

["Walden H. Leverich III" <WaldenL@xxxxxxxxxxxxxxx>]

The CASE statement will give you your units. I'm not on the 400 the syntax
check this right now, but something to the effect of:

Select Region, 
        District, 
        Color, 
        Red, 
        Yellow, 
        Blue, 
        case color 
                when 'red' then red 
                when 'blue' then blue 
                when 'yellow' then yellow 
                else -1 end 
        as Units
        from ...

Should get you the first field. 

The problem with the second is that you need to know the total to do the
math. Something like:

Select Region, 
        District, 
        Color, 
        Red, 
        Yellow, 
        Blue, 
        case color 
                when 'red' then red 
                when 'blue' then blue 
                when 'yellow' then yellow 
                else -1 end 
        as Units,
        Units/(select 
                sum     (
                        case color
                                when 'red' then red 
                                when 'blue' then blue 
                                when 'yellow' then yellow 
                                else 0 end
                        )
                from filename B
                where B.Region = A.Region
                        and B.Color = A.Color
                )
        as Allocation
        from filename A

Should work in concept, but I don't know how the iSeries will swollow the
use of 'units' field in the definition of the 'allocation' field, the
sub-select in the divide or the case statement in the sum clause. But that's
the general idea. If it won't allow the use of 'units' in the definition of
'allocation' try:

Select Region, 
        District, 
        Color, 
        Red, 
        Yellow, 
        Blue, 
        case color 
                when 'red' then red 
                when 'blue' then blue 
                when 'yellow' then yellow 
                else -1 end 
        as Units,
        case color 
                when 'red' then red 
                when 'blue' then blue 
                when 'yellow' then yellow 
                else -1 end 
        /
                (select 
                sum     (
                        case color
                                when 'red' then red 
                                when 'blue' then blue 
                                when 'yellow' then yellow 
                                else 0 end
                        )
                from filename B
                where B.Region = A.Region
                        and B.Color = A.Color
                )
        as Allocation
        from filename A

Which simply replaces the use of 'units' with its definition.

-Walden

------------
Walden H Leverich III
President
Tech Software
(516) 627-3800 x11
(208) 692-3308 eFax
WaldenL@xxxxxxxxxxxxxxx
http://www.TechSoftInc.com 

Quiquid latine dictum sit altum viditur.
(Whatever is said in Latin seems profound.)
 

-----Original Message-----
From: Dan [mailto:dbcerpg@xxxxxxxxx] 
Sent: Monday, April 21, 2003 4: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.