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

["Lennon_s_j@xxxxxxxxxxx" <lennon_s_j@xxxxxxxxxxx>]

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