RE: Summarizing and fixing up some data

It appears that UPDATE/DELETE cannot be used with CTE (WITH ...) due to
obstinateness. Too bad. I apologize for the misstep.

Anyway, maybe this will serve you better: (3 statements instead of two.)
You'll note a lot of re-use here.

CREATE TABLE QTEMP/SHPCONPO AS (
SELECT SHIPPER, CONSIGNEE, PO, WAVE, COUNT(*) as COUNTER
, MIN(RRN(PROBLEMTBL)) as RRNBR
FROM PROBLEMTBL
GROUP BY SHIPPER, CONSIGNEE, PO
HAVING COUNT(*) > 1
) WITH DATA


UPDATE PROBLEMTBL PT
SET WAVE = 'MULTI'
WHERE EXISTS
(SELECT 1
FROM QTEMP/SHPCONPO T1
WHERE T1.SHIPPER = PT.SHIPPER
AND T1.CONSIGNEE = PT.CONSIGNEE
AND T1.PO = PT.PO
AND T1.RRNBR = RRN(PROBLEMTBL)
)




DELETE FROM PROBLEMTBL PT
WHERE EXISTS
(SELECT 1
FROM QTEMP/SHPCONPO T1
WHERE T1.SHIPPER = PT.SHIPPER
AND T1.CONSIGNEE = PT.CONSIGNEE
AND T1.PO = PT.PO
AND T1.RRNBR < RRN(PROBLEMTBL)
)


Technically, the UPDATE could get by with:

UPDATE PROBLEMTBL PT
SET WAVE = 'MULTI'
WHERE EXISTS
(SELECT 1
FROM QTEMP/SHPCONPO T1
WHERE T1.RRNBR = RRN(PROBLEMTBL)
)

... but I like the extra check provided in the original.


Dennis Lovelady
http://www.linkedin.com/in/dennislovelady
--
"The Internet...in the right hands it is a wondrous tool, and in the wrong
hands it is an even better one."
-- Cecil Adams

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


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