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

I'm not clear on exactly what you want, but If you are on a fairly recent release, e.g V5R3 or later I think, try (a variation) on something like this:

with mydata as (
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),CONCAT('/',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
)
select LINE_ITEM, CUSTNUM, ORDERNO,
sum(ORDER_QTY), sum(SHIP_QTY)
from mydata
group by LINE_ITEM, CUSTNUM, ORDERNO
order by LINE_ITEM, CUSTNUM, ORDERNO

The above is all one big SQL statement. The "with" kind of creates a temporary view for you which you use in the final grouping.

Sam

On 4/16/2010 9:06 AM, Peter_Vidal@xxxxxxxx wrote:

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),CONCAT('/',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.


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