I need another jump start on something. I’ve been banging my head against the wall this morning.

• Using the database reader, I pulled order header and detail information out of Sage 300. (this works)

SELECT OEORDH.ORDNUMBER, OEORDH.CUSTOMER, OEORDH.SHPNAME, OEORDH.SHPADDR1, OEORDH.SHPADDR2,OEORDH.SHPCITY, OEORDH.SHPSTATE, OEORDH.SHPZIP, OEORDH.SHPCOUNTRY, OEORDH.SHPPHONE, OEORDH.SHPCONTACT,OEORDH.PONUMBER, OEORDH.TYPE, OEORDH.EXPDATE, OEORDH.SHIPVIA, OEORDH.LASTINVNUM, OEORDD.ITEM, OEORDD.QTYBACKORD, OEORDD.QTYSHPTODT
FROM OEORDH LEFT OUTER JOIN OEORDD ON OEORDH.ORDUNIQ = OEORDD.ORDUNIQ
WHERE OEORDH.TYPE = 1 AND OEORDD.COMPLETE = 0

• Using the transform connector, I created a new field ORDERNO: if item # = UPS-RET then ORDERNO = Order # +”R” else ORDERNO = Order #. (this works)
• The next thing I need to do is take all of these records and group them by ORDERNO – I need to pull subtotals of qtys, maximums, minimums and some basic info that stays the same throughout like ship via codes. ß this last piece is the part that is confusing me.

• I played with the flatten connector but it seems like you have to start with a truly hierarchical query (separate records for header and details) in order to flatten

• oI tried a group by in SQL and in Iman – but since I want to evaluate the information based on the new formula ORDERNO, this is a real pain. I played with it a bit and could continue to try this if this is my best option.

Can you give me a hint? J From there, I’m just exporting to excel and that doesn’t look hard at all… Thanks!