× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



Yea, Baby! ! !

Mark Walter
Business to Business Data Integration Specialist
Certified IBM System i Specialist
Paragon Consulting Services, Inc
mwalter@xxxxxxxxxxxxxxx
717-764-7909 ext. 126

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of rob@xxxxxxxxx
Sent: Thursday, October 01, 2009 9:55 AM
To: Midrange Systems Technical Discussion
Subject: Re: SQL Challenge

nah. A real cycle freak would have no calc's and the contents of the O
specs would all be qualified with indicators instead
01NMR 'I'
02NMR 'D'
01 MR 03 'C'
01 MRN03 'X'

Well, maybe controlling 03 might take some calc's.
And I bet the line of OR's on whether to output a.fieldc or b.fieldc
(renamed of course) would be intense.

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





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



02NMR Write...

On Thu, Oct 1, 2009 at 9:11 AM, <rob@xxxxxxxxx> wrote:

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.


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


As an Amazon Associate we earn from qualifying purchases.

This thread ...

Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

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.