Re: Summarizing and fixing up some data

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