RE: Use for SQL?

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.