Re: Can SQL do this? -- RPG400-L

I'll try to explain how to do the allocation bit by reference to my
"simplified" version of the code below. I'll leave you adapt this technique
to your actual tables.

This is the code without the calculation of the allocation:

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
FROM sqltest a

To get the allocation, you need to divide the units by the total for the
region and color. So, you need to calculate totals by region and color and
join to these so you can do the allocation calculation:

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 red/total
         when a.color = 'Y' then yellow/total
         else blue/total 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

Here I've joined the basic table (which I've called SQLTEST) to another
selection on the same table (the bit in brackets with the group by) which
provides totals by region and color. I've labelled the basic table a and the
group by selection x. I've joined the two on region and color to select the
right total.

In the code I sent you earlier today, I used dec() to format the output, but
this isn't strictly necessary.

I hope that makes it a bit clearer.

Pete

"Dan" <dbcerpg@xxxxxxxxx> wrote in message
news:20030422141433.72587.qmail@xxxxxxxxxxxxxxxxxxxxxxxxxx
> 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
> _______________________________________________
> 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.
>
>