MIDRANGE dot COM Mailing List Archive



Home » MIDRANGE-L » October 2012

RE: Summarizing and fixing up some data



fixed

I have so much to learn about SQL. Thanks again.

Jack

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Dennis Lovelady
Sent: Wednesday, October 03, 2012 3:11 PM
To: 'Midrange Systems Technical Discussion'
Subject: 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.


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






Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2014 by MIDRANGE dot COM and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available here. If you have questions about this, please contact