RE: Use for SQL?

["Joe Pluta" <joepluta@xxxxxxxxxxxxxxxxx>]

AGH!!  This is almost painful to watch.

Here's my analysis: you have accounts.  Some of them have parent accounts,
some don't.  If the account has a parent account, you want to summarize by
that parent account.

In order for this to work, you MUST have one of two things: either the
account file must have a parent field which is either zero or non-zero, or
you must have a relationship file where an account number can chain to get a
parent.  This has nothing to do with SQL, it's just the minimum you would
have to have in ANY database in order to be able to summarize by parent.

Okay, moving on.  Let's take option one.  There is an account file, and the
account file has a "parent account" field which is either zero or contains
the parent account.  The detail file has an account field which points to
the account file.

WITH T1 AS (SELECT 
   CASE WHEN ACCOUNT.PARENT <> 0
      THEN ACCOUNT.PARENT
      ELSE ACCOUNT.ID
   END AS REPORTING_ID,
   DETAIL.FIELD_TO_SUMMARIZE
 FROM DETAIL JOIN ACCOUNT
   ON DETAIL.ACCOUNT_ID = ACCOUNT.ID)
SELECT REPORTING_ID, SUM(FIELD_TO_SUMMARIZE)
  GROUP BY REPORTING_ID
  ORDER BY SUM(FIELD_TO_SUMMARIZE) DESC

Option two means you have a relationship file.  If a record exists, the
parent field contains the parent.  If no record exists, there is no parent.

WITH T1 AS (SELECT 
   COALESCE(PARENTXREF.PARENT, DETAIL.ACCOUNT_ID)
      AS REPORTING_ID,
   DETAIL.FIELD_TO_SUMMARIZE
 FROM DETAIL LEFT OUTER JOIN PARENTXREF
   ON DETAIL.ACCOUNT_ID = PARENTXREF.CHILD)
SELECT REPORTING_ID, SUM(FIELD_TO_SUMMARIZE)
  GROUP BY REPORTING_ID
  ORDER BY SUM(FIELD_TO_SUMMARIZE) DESC

I could have made this simpler if SQL allowed me to use the "AS" name in the
GROUP BY and ORDER BY clauses, but it doesn't work, at least on the V5R3
machine I tested with.  Common sense would allow this:

SELECT
   COALESCE(PARENTXREF.PARENT, DETAIL.ACCOUNT_ID)
      AS REPORTING_ID,
   SUM(DETAIL.FIELD_TO_SUMMARIZE) AS SUM_FIELD
FROM DETAIL LEFT OUTER JOIN PARENTXREF
   ON DETAIL.ACCOUNT_ID = PARENTXREF.CHILD)
GROUP BY REPORTING_ID
ORDER BY SUM_FIELD DESC

But I find that common sense doesn't always apply in SQL.

Joe

> -----Original Message-----
> From: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx]
> On Behalf Of Phil Kestenbaum
> Sent: Wednesday, December 20, 2006 3:30 PM
> To: RPG programming on the AS400 / iSeries
> Subject: Use for SQL?
>
> HI I am trying to see how to use SQL here.
>
>
>
> I have to modify a RPG report that prints the customers ranked as top
> 50. However, in one case, we have many accounts that are actually a
> franchise of the same parent company and these appear many times in this
> top 50, let us call them Burger-King. They would like to group the
> Burger-King accounts so that only one Burger-King will appear on the
> report w/ the aggregate totals. For the sake of this report, it doesn't
> matter which account number is listed for the total Burger-Kings.
>
>
>
> There are several steps involved in arriving at the final list, incl.
> work files.
>
>
>
> If it could be hard-coded, to look for all the Burger-Kings that would
> be easy but we want to be able to take any account that appears in the
> ranking more than once to aggregate it.
>
>
>
> For timeliness, it seems to me that if I can deal w/ the final file
> right prior to printing the report and make the adjustment there that
> would be the preferred method.
>
>
>
> Can the SQL be able to discern where it has a number of Burger-Kings and
> aggregate them?
>
>
>
> Thank you,
>
>
>
> Phil
>
>
> This message contains information proprietary to our company. It is
> intended to be read only by the individual or entity named above or their
> designee.
> Any distribution of this message or the information contained herein
> without written permission from our company is strictly prohibited. If the
> reader of this message is not the intended recipient or an agent
> responsible for delivering it to the intended recipient, you are hereby
> notified that you have received this document in error and that any
> review, dissemination, distribution, or copying of this message is
> strictly prohibited. If you have received this communication in error,
> please notify us immediately by e-mail, and delete the original message.
>
>
> --
> 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/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.