Re: Can SQL do this?

[Dan <dbcerpg@xxxxxxxxx>]

Thanks to all who have responded thus far.  This is really getting into cutting 
edge (for me)
here.

I got the "Units" column going, that was the easier of the two challenges I 
suspect.

The second challenge, calculating allocation, is a bit trickier and requires a 
better
understanding of what's going on with the GROUP BY, as I'm sure it's what makes 
the "sum" function
do its magic.  

Per this "challenge" and Eric's advice to post the SQL, I am doing so below.  I 
should point out
that I did not have access to the SQL statement when I wrote up my original 
post, which explains
why what appears below is materially different from that original post.  The 
two main challenges
being discussed were presented in both cases.

As I wrote this, I am being questioned by my supervisor about going this 
complex with SQL. 
Continuation of this thread may likely become strictly academic, as nothing 
goes into production
around here without a thorough understanding of the underlying code and an 
agreement that the
other programmers will be able to understand and maintain it as well (as it 
should be).  So, with
that understanding, herewith is the latest version of the SQL I am working with:

SELECT AL3.DMREGCD, AL1.MKISCIBASE, AL1.MKISCINUM, AL1.MKNIELCD,
       AL1.MKCOSTTYPE, AL1.MKDEMOCAT, COUNT (DISTINCT AL2.CYCYSTDT),
       AVG ( AL3.DMTALUNTR ), AVG ( AL3.DMHTALUNTR ), AL3.DMNIELNM,
 case when AL1.MKDEMOCAT = 'H' then AVG( AL3.DMHTALUNTR )
   else AVG( AL3.DMTALUNTR ) end as Units
  FROM ##PFPROD/RTISCMKT AL1
       LEFT OUTER JOIN ##PFPROD/TLCYCDTE AL2 ON
                           (     AL1.MKISCINUM = AL2.CYISCNUM
                             AND AL1.MKMKTCAT  = AL2.CYMKTCAT
                             AND AL1.MKNIELCD  = AL2.CYNIELCD )
       LEFT OUTER JOIN ACPFACC/ACDMACD AL3 ON
                           ( AL1.MKNIELCD  = AL3.DMNIELCD  )
  WHERE (AL1.MKCOSTTYPE = 'T')
  GROUP BY AL1.MKISCIBASE, AL1.MKISCINUM, AL1.MKNIELCD,
           AL3.DMNIELNM, AL1.MKCOSTTYPE, AL1.MKDEMOCAT, AL3.DMREGCD
  ORDER BY  AL3.DMREGCD, AL1.MKISCIBASE

TIA, Dan

--- Pete Clifford <pete@xxxxxxxxxxxxxxxxxx> wrote:
> 
> 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