MIDRANGE dot COM Mailing List Archive



Home » MIDRANGE-L » October 2012

RE: Summarizing and fixing up some data



fixed

You're welcome. After you remove the "WAVE, " from the below (first) SELECT
-- remember that ubiquitous bug I referred to earlier -- what happens here
is:

Create a file with SHIPPER, CONSIGNEE, PO, a count of "duplicates" called
COUNTER, and the lowest Relative Record Number of the set of matches (that
column will be named RRNBR).

The file will only contain records where there is more than one match per
SHIPPER, CONSIGNEE, and PO. You won't need COUNTER in the below; I gather
it for reference only.

The UPDATE and the DELETE use that information to determine what records
will be updated/deleted. The WHERE clause in each case is similar to the
way one might write an inner join operation using the Pre-ANSI (or non-ANSI)
syntax.

If you think of the WHERE clause as creating an INTERNAL JOIN between the
temp file and the problem file (and selecting based upon the relationship to
lowest RRN), the rest is fairly simple to understand.

Note: The only "special" function that RRN(table) serves here, is that it
provides a guaranteed unique value. It could have been
MIN(any_other_column) instead of MIN(RRN(table)) if the combination of that
column and SHIPPER/CONSIGNEE/PO provides a unique value. Again, I opted for
the guaranteed-unique value without knowing your data. You may have an
affinity for another column, based upon your data and your needs, and may
thus want to tweak accordingly.


Dennis Lovelady
http://www.linkedin.com/in/dennislovelady
--
"Just because you're offended, doesn't mean you're in the right."
-- Ricky Gervais


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.

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