RE: Summarizing and fixing up some data

Thanks Dennis and SJL for the great responses. I'll give them a try.

Jack

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Dennis Lovelady
Sent: Wednesday, October 03, 2012 8:00 AM
To: 'Midrange Systems Technical Discussion'
Subject: RE: Summarizing and fixing up some data

My ubiquitous second reply:

GROUP BY SHIPPER, CONSIGNEE, PO

<<<Not>>> GROUP BY SHIPPER, CONSIGNEE, PO, WAVE

But you probably already knew that.

Sheesh, my kingdom for a correct first answer!

Dennis Lovelady
http://www.linkedin.com/in/dennislovelady
--
Computers are not intelligent. They only think they are.

In SQL, there is no way that I know of to delete and update in one
statement.

So here is one solution (I have not tried it), requiring two statements.
NOTE the difference between the T1.RRNBR : RRN(PROBLEMTBL) comparisons
in the UPDATE and the DELETE. This is key to your specs.


WITH T1 AS (SELECT SHIPPER, CONSIGNEE, PO, WAVE, COUNT(*) as COUNTER
, MIN(RRN(PROBLEMTBL)) as RRNBR
FROM PROBLEMTBL
GROUP BY SHIPPER, CONSIGNEE, PO, WAVE
HAVING COUNT(*) > 1
)
UPDATE PROBLEMTBL PT
SET WAVE = 'MULTI'
WHERE EXISTS
(SELECT 1
FROM T1
WHERE T1.SHIPPER = PT.SHIPPER
AND T1.CONSIGNEE = PT.CONSIGNEE
AND T1.PO = PT.PO
AND T1.RRNBR = RRN(PROBLEMTBL)
)



WITH T1 AS (SELECT SHIPPER, CONSIGNEE, PO, WAVE, COUNT(*) as COUNTER
, MIN(RRN(PROBLEMTBL)) as RRNBR
FROM PROBLEMTBL
GROUP BY SHIPPER, CONSIGNEE, PO, WAVE
HAVING COUNT(*) > 1
)
DELETE FROM PROBLEMTBL PT
WHERE EXISTS
(SELECT 1
FROM T1
WHERE T1.SHIPPER = PT.SHIPPER
AND T1.CONSIGNEE = PT.CONSIGNEE
AND T1.PO = PT.PO
AND T1.RRNBR < RRN(PROBLEMTBL)
)


Dennis Lovelady
http://www.linkedin.com/in/dennislovelady
--
The pessimist's ultimate observation:
-- - To make the attempt, is the first step toward faulure.

I have a file that has columns like this:



Shipper, Consignee, PO#, Wave#.



If there is more than one wave# per Shipper/Consignee/PO#, I want to
delete all but one of the records, and change the wave# to 'MULTI'



If there is only one record, leave it alone. Any ideas on how I

could

do
this with SQL or RPG?



Thanks.



Jack

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L)

mailing

list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take
a moment to review the archives at

http://archive.midrange.com/midrange-l.


--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a
moment to review the archives at http://archive.midrange.com/midrange-l.