RE: How to get only 1 record from a group of common records

Peter,

Wow... Just to be sure I understand the issue, you may have multiple
ship-release records for each line on the order. You want to sum up the
ship-release amounts by order line, before you join into the other
tables... Right?

Easy! Use the "With" syntax to pre-group your ship-release file, then
use the summarized version in your primary join in place of the MBADREP
table.


With ShpRls as
( Select order, lineNo, Item, sum(shpQty)
FROM MBADREP
GROUP BY order, lineNo, Item)
SELECT CDAITX AS ITNBR,
ADDRNB AS LINE_ITEM,
CDAAYY AS CUSTNUM,
CDCVNB AS ORDERNO,
...
FROM ... LEFT JOIN ShpRls on ()

Sorry I can't work up a better example right now...

Hth,
-Eric DeLong

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of
Peter_Vidal@xxxxxxxx
Sent: Friday, April 16, 2010 8:06 AM
To: midrange-l@xxxxxxxxxxxx
Subject: SQL: How to get only 1 record from a group of common records

Hi list!

I need to find a way to sum the results of more than one child record
(shipment release file) for each parent (order item header) so the table

does not return more than one line for each ordered line item.
The code we have so far is the following:

SELECT CDAITX AS ITNBR,
ADDRNB AS LINE_ITEM,
CDAAYY AS CUSTNUM,
CDCVNB AS ORDERNO,
ADDZVA AS ORDER_QTY,
DDARQT AS SHIP_QTY,
CDDTVA AS UNIT_PRICE,
CONCAT(SUBSTR(CDALDT,4,2),CONCAT('/',CONCAT(SUBSTR(CDALDT,6,2),CONCAT('/
',SUBSTR(CDALDT,2,2)))))
AS ORDER_DATE,
CONCAT(SUBSTR(C.ADBIDT,4,2),CONCAT('/',CONCAT(SUBSTR(C.ADBIDT,6,2),CONCA
T('/',SUBSTR(C.ADBIDT,2,2)))))
AS PROMISE_DATE

FROM ((AMFLIB1.MBC6REP A
LEFT JOIN AMFLIB1.MBCDREP B ON A.C6AENB = B.CDAENB AND A.C6DCCD =
B.CDDCCD
AND A.C6CVNB = B.CDCVNB)
LEFT JOIN AMFLIB1.MBADREP C ON A.C6AENB = C.ADAENB AND A.C6DCCD =
C.ADDCCD
AND A.C6CVNB = C.ADCVNB AND B.CDFCNB = C.ADFCNB )
LEFT JOIN AMFLIB1.MBBFREP D ON A.C6AENB=D.COMNO AND A.C6CANB = D.CUSNO
LEFT JOIN AMFLIB1.MBDEREP F ON A.C6B9CD = F.DEB9CD AND A.C6AENB =
F.DEAENB
AND F.DECANB = A.C6CANB
LEFT JOIN AMFLIB1.ITEMBL G ON B.CDAITX=G.ITNBR
LEFT JOIN AMFLIB1.MBDDREP H ON A.C6AENB = H.DDAENB AND A.C6CVNB =
H.DDCVNB
AND C.ADDRNB = H.DDABDD

WHERE A.C6AENB = 24 AND CDA3CD = 'BA1' AND CDAITX <> ' ' AND G.HOUSE =

'BA1'
AND A.C6DCCD = '1' AND A.C6FNST <> '50' AND CDH3ST <> '50' AND
C.ADBIDT < 1100319 AND CDCVNB = 435605

Just for your information:
1) MBC6REP = Sales Order - Header File
2) MBCDREP = Sales Order - Line Item File
3) MBADREP = Sales Order - Releases File
4) MBBFREP = Customer Master File
5) MBDEREP = Ship-To file
6) ITEMBL = Item Inventory Balance File
7) MBDDREP = Shipment Releases

Can you use a select statement within a select statement using SUM and
returning results as a column value in the original table? I do not
know,
I am just guessing.

Any help is highly appreciated.

TIA,
PETER VIDAL
PALL CORPORATION | SR SYSTEM ANALYST @ WH APPLICATION DEVELOPMENT GROUP
10540 RIDGE RD., SUITE 203, NEW PORT RICHEY, FL 34654-5111
727-815-3104 ||| FAX: 727-815-3120 ||| WWW.PALL.COM

"Imagination is more important than knowledge..."
Albert Einstein (1879 - 1955)

------------------------------------------------------------------------
--------

Attention:

This communication may contain information that is confidential,
privileged and/or exempt from disclosure under applicable law.
If you are not the intended recipient, please notify the sender
immediately and delete the original, all attachments, and all
copies of this communication.


------------------------------------------------------------------------
--------