Re: Embedded SQL with join and sum

On Mon, Dec 12, 2016 at 3:19 PM, (WalzCraft) Jerry Forss <
JForss@xxxxxxxxxxxxx> wrote:

I have 2 files, Order Header and Order detail, both keyed with Cust# and
Order#.

What I want is a single row per Cust#, Order# with the sum of Quantity
from detail file.

Try a common table expression :



SqlSel = '

with od_ttl_qty as
( select OdCustNo, OdOrderno, Sum(OdQty) As Pieces
Group By OhCustNo, OhOrderNo )

Select OhCustNo, +
OhOrderNo, +
OhType, +
OhDSOOrd, +
OhShipDt1, +
OhInvoice1, +
OhAmount1, +
OhDiscnt1, +
OhPaidDt1, +
OhPaidDay1, +
OhEOL1, +
OhEOLDays1, +

od.Pieces +

From CusOrdHdS1 OH +

Inner Join od_ttl_qty OD On OdCustNo = OhCustNo And +
OdOrderno = OhOrderNo +

Where OhCustNo = ' + %EditC(CustNo : 'X') + ' And ' +
'OhShipDt1 >= ' + %Char(StartDateISO) + ' And ' +
'OhShipDt1 <= ' + %Char(EndDateISO) + ' ' +

'Order By OhShipDt1';