Re: SQL Challenge

Now for the perverse challenge - who can write this as a matching records
RPG cycle program?

Rob Berendt
--
Group Dekko Services, LLC
Dept 01.073
Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.com





From:
rob@xxxxxxxxx
To:
Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
Date:
10/01/2009 09:05 AM
Subject:
Re: SQL Challenge
Sent by:
midrange-l-bounces@xxxxxxxxxxxx



CREATE TABLE ROB.FILEC AS (
SELECT 'D' AS STATUS, B.*
FROM FILEB B EXCEPTION JOIN FILEA A ON A.FIELDA=B.FIELDA AND
A.FIELDB=B.FIELDB
UNION ALL
SELECT 'I' AS STATUS, A.*
FROM FILEA A EXCEPTION JOIN FILEB B ON A.FIELDA=B.FIELDA AND
A.FIELDB=B.FIELDB
UNION ALL
SELECT 'C' AS STATUS, A.*
FROM FILEA A JOIN FILEB B ON A.FIELDA=B.FIELDA AND
A.FIELDB=B.FIELDB
WHERE A.FIELDC<>B.FIELDC OR A.FIELDD<>B.FIELDD OR A.FIELDE<>B.FIELDE
UNION ALL
SELECT 'X' AS STATUS, A.*
FROM FILEA A JOIN FILEB B ON A.FIELDA=B.FIELDA AND
A.FIELDB=B.FIELDB
WHERE A.FIELDC=B.FIELDC AND A.FIELDD=B.FIELDD AND A.FIELDE=B.FIELDE)
WITH DATA;

Proof:

CREATE TABLE ROB/FILEA (FIELDA CHAR (1 ) NOT NULL WITH DEFAULT,

FIELDB CHAR (1 ) NOT NULL WITH DEFAULT, FIELDC CHAR (1 ) NOT NULL
WITH DEFAULT, FIELDD CHAR (1 ) NOT NULL WITH DEFAULT, FIELDE CHAR
(1 ) NOT NULL WITH DEFAULT)
Table FILEA created in ROB.

INSERT INTO ROB/FILEA VALUES('A', 'A', 'A', 'A', 'A')

1 rows inserted in FILEA in ROB.

INSERT INTO ROB/FILEA VALUES('B', 'B', 'B', 'B', 'B')

1 rows inserted in FILEA in ROB.

INSERT INTO ROB/FILEA VALUES('C', 'C', 'C', 'C', 'C')

1 rows inserted in FILEA in ROB.

CREATE TABLE ROB/FILEB AS (SELECT * FROM ROB/FILEA) WITH DATA

Table FILEB created in ROB.

DELETE FROM ROB/FILEA WHERE FIELDA='C'

1 rows deleted from FILEA in ROB.

UPDATE FILEA SET FIELDC='1' WHERE FIELDA='B'

1 rows updated in FILEA in ROB.

INSERT INTO ROB/FILEA VALUES('D', 'D', 'D', 'D', 'D')

1 rows inserted in FILEA in ROB.

....+....1....+....2....+....3....+....4....+.
STATUS FIELDA FIELDB FIELDC FIELDD FIELDE
D C C C C C
I D D D D D
C B B 1 B B
X A A A A A
******** End of data ********

Rob Berendt
--
Group Dekko Services, LLC
Dept 01.073
Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.com





From:
"Neill Harper" <neill.harper@xxxxxxxx>
To:
"'Midrange Systems Technical Discussion'" <midrange-l@xxxxxxxxxxxx>
Date:
09/30/2009 07:26 PM
Subject:
SQL Challenge
Sent by:
midrange-l-bounces@xxxxxxxxxxxx



Hi Guys



Not sure if there is a way to do this, but if there is someone on here
will
know how!!



If I have 2 files, File A and File B that have the same record format as
follows:



File A

Field A (Key)

Field B (Key)

Field C

Field D

Field E



File B

Field A (Key)

Field B (Key)

Field C

Field D

Field E





File A is a new version of File B, i.e. at one point it contained exactly
the same records as File B. However over time File A has had records
inserted, deleted and updated.



Can anybody think of a way using sql that gives me a third file FILE C,
that
has the exact same format as FILE A and FILE B, except for one extra field
called Record Status. So that file C contains all of the original records
from FILE B that are no longer in FILE A (where record status = D,
Deleted),
all of the new records in FILE A (where record status = I, Inserted) , all
of the Records that are in FILE A that have been changed from FILE B
(where
record status = U, Updated) and finally all records in FILEA that have not
changed from FILE B( where status = X, unchanged)



Example



File A


Field A

Field B

Field C

Field D

Field E


2

1

Text12

Text22

Changed


3

1

Text123

Text23

Text334


4

1

Text1234

Text24

Text335


5

1

New

New

New



File B


Field A

Field B

Field C

Field D

Field E


1

1

Text1

Text2

Text3


2

1

Text12

Text22

Text33


3

1

Text123

Text23

Text334


4

1

Text1234

Text24

Text335



File C


Field A

Field B

Field C

Field D

Field E

Record Status


1

1

Text1

Text2

Text3

D


2

1

Text12

Text22

Changed

U


3

1

Text123

Text23

Text334

X


4

1

Text1234

Text24

Text335

X


5

1

New

New

New

I



Any help with this will be useful, thanks



Neill



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