MIDRANGE dot COM Mailing List Archive



Home » MIDRANGE-L » October 2012

Re: Summarizing and fixing up some data



fixed



"Jack Tucky" wrote:

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?


Jack -

Here is one solution, using only SQL.

Note:
**** MAKE A BACKUP OF YOUR FILE FIRST *****

(see below)

- sjl


CREATE TABLE MYLIB/JACKTUCKY
(SHIPPER CHAR (20 ) NOT NULL WITH default,
CONSIGNEE CHAR (20 ) NOT NULL WITH DEFAULT,
PO# CHAR (20 ) NOT NULL WITH DEFAULT,
WAVE# CHAR (20 ) NOT NULL WITH DEFAULT)
rcdfmt Jacktuckyr

-- Table JACKTUCKY in MYLIB created but was not journaled.

INSERT INTO MYLIB/JACKTUCKY VALUES('A', 'B', '100', '1')

-- 1 rows inserted in JACKTUCKY in MYLIB.

INSERT INTO MYLIB/JACKTUCKY VALUES('A', 'B', '100', '2')

-- 1 rows inserted in JACKTUCKY in MYLIB.

INSERT INTO MYLIB/JACKTUCKY VALUES('A', 'B', '100', '3')

-- 1 rows inserted in JACKTUCKY in MYLIB.

INSERT INTO MYLIB/JACKTUCKY VALUES('C', 'D', '100', '1')

-- 1 rows inserted in JACKTUCKY in MYLIB.

INSERT INTO MYLIB/JACKTUCKY VALUES('E', 'F', '200', '1')

-- 1 rows inserted in JACKTUCKY in MYLIB.


SELECT * FROM MYLIB/jacktucky
ORDER BY SHIPPER, CONSIGNEE, PO#, WAVE#

SHIPPER CONSIGNEE PO# WAVE#
A B 100 1
A B 100 2
A B 100 3
C D 100 1
E F 200 1
******** End of data ********


CREATE TABLE QTEMP/jacktucky AS
(
Select distinct
SHIPPER, CONSIGNEE, PO#, CHAR('MULTI') as WAVE#
From MYLIB/jacktucky where
(SHIPPER, CONSIGNEE, PO#) in
(SELECT SHIPPER, CONSIGNEE, PO#
FROM MYLIB/jacktucky GROUP BY
SHIPPER, CONSIGNEE, PO# HAVING COUNT(*) > 1)
) WITH DATA


SELECT *FROM QTEMP/JACKTUCKY

SHIPPER CONSIGNEE PO# WAVE#
A B 100 MULTI
******** End of data ********


DELETE From MYLIB/jacktucky where
(SHIPPER, CONSIGNEE, PO#) in
(SELECT SHIPPER, CONSIGNEE, PO#
FROM MYLIB/jacktucky GROUP BY
SHIPPER, CONSIGNEE, PO# HAVING COUNT(*) > 1)


SELECT *FROM MYLIB/JACKTUCKY

SHIPPER CONSIGNEE PO# WAVE#
C D 100 1
E F 200 1
******** End of data ********


INSERT INTO MYLIB/JACKTUCKY
(SELECT *FROM QTEMP/JACKTUCKY)


SELECT *FROM MYLIB/JACKTUCKY

SHIPPER CONSIGNEE PO# WAVE#
A B 100 MULTI
C D 100 1
E F 200 1
******** End of data ********







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