Re: SQL Challenge

Neil -

(Done from memory, not currently on a system...)

Remember structural decomposition? Break it down into three separate steps.
Assuming that you don't allow key changes, you'll need the union of three
separate queries to achieve the result you desire. I'll use corresponding
joins.


The first query will produce a set of records that exist in both files,
showing updated records:

Select FieldA,FieldB,FieldC, FieldD, FieldE, 'U' from FileB B
Where Exists
(
select *from fileA A
where (B.FieldA = A.FieldA and B.FieldB=A.FieldB)
and
(
B.FieldC <> A.FieldC or
B.FieldD <> A.FieldD or
B.FieldE <> A.FieldE
)
)

The second query will produce a set of records that exist in both files,
showing unchanged records:

Select FieldA,FieldB,FieldC, FieldD, FieldE, 'X' from FileB B
Where Exists
(
select *from fileA A
where (B.FieldA = A.FieldA and B.FieldB=A.FieldB)
and
(
B.FieldC = A.FieldC or
B.FieldD = A.FieldD or
B.FieldE = A.FieldE
)
)


The third query will show the records in File B that don't exist in File A:

Select FieldA,FieldB,FieldC, FieldD, FieldE, 'D' from FileB B
Where NOT Exists
(
select *from fileA A
where (B.FieldA = A.FieldA and B.FieldB=A.FieldB)
)

I'll leave it up to you to turn this into a Union...

Regards,
Steve


Neill wrote:
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