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.
