RE: Use for SQL?

["Alan Campin" <Alan.Campin@xxxxxxxxxxxxxxxx>]

<snip>
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.
</snip>

Two things would come to mind. 

1. A Virtual Join so that the result of the join is summarized to one
account.
2. Use a Union. I think that would be more appropriate in this case.
Everything except Burger King on one side and Burger King summarized on
the other side of the Union.

A virtual example. Note that the second Inner Join is virtual. 

Select a.WKCTR, a.ORDNO, a.MITNO, a.OPSEQ, C.ACRTDT, Min(A.TRNDT),
       Max(a.TRNDT)
       From PCHIST a
         Inner Join CSTSUM b
           On a.ORDNO = b.ORDNOF And
              a.MITNO = b.FITEMF
         Inner Join (Select ORDNO,ITNBR, Max(TRNDT) As Max_Date 
                             From IMHIST
                       Where TCODE = 'RM' And
                             CMPCD = 'C'  And
                             TRNDT  Between 1061002 And 1061101 
                       Group By ORDNO,ITNBR) As d
                       On a.ORDNO = d.ORDNO And
                        a.MITNO = d.ITNBR
         Inner Join DISJQARC c
           On a.ORDNO = c.AM# And
              a.OPSEQ = c.AOPSEQ
      Where  b.OSTATF Between '45' and '55'     And
             a.WKCTR In ('2001','2006')         And
             a.MITNO Not Like 'R%'              And
             c.ACRTDT >= 1050101                And
             a.TRNDT >= 20050101
      Group by a.WKCTR, a.ORDNO, a.MITNO, a.OPSEQ , c.ACRTDT
      Order by a.WKCTR, a.ORDNO, a.MITNO, a.OPSEQ , c.ACRTDT