|
Now for the perverse challenge - who can write this as a matchingrecords
RPG cycle program?exactly
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 DATATable 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
the same records as File B. However over time File A has had recordsfield
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
called Record Status. So that file C contains all of the originalrecords
from FILE B that are no longer in FILE A (where record status = D,all
Deleted),
all of the new records in FILE A (where record status = I, Inserted) ,
of the Records that are in FILE A that have been changed from FILE Bnot
(where
record status = U, Updated) and finally all records in FILEA that have
changed from FILE B( where status = X, unchanged)list
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
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.
As an Amazon Associate we earn from qualifying purchases.
This mailing list archive is Copyright 1997-2024 by midrange.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 on our policy page. If you have questions about this, please contact [javascript protected email address].
Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.
midrange.com
