AW: SQL Challenge

Hi,

Assumed that the records are identical except the status field, but
duplicated in A and B and you only want the records (except the status
field) once, you may try the following:

Create Table ...
As (Select Field1, Field2, Field3 ... FieldN
From A
Where Status in ('A', 'X', ...)
Union
Select Field1, Field2, Field3 ... FieldN
From B
Where Status in ('B', 'C', ...)
With DATA

The stati in the example above are only examples and may not be the same you
need, but I think it's easy for you to replace them.

Union or Union Distinct merges 2 select statements and removes all
duplicates, that means you'll see the same record only once.
Unfortunately you need to list all fields/Columns you want to see in your
table. There is no way to define something like "Select * Except Field".


Mit freundlichen Grüßen / Best regards

Birgitta Hauser

"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"

-----Ursprüngliche Nachricht-----
Von: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] Im Auftrag von Neill Harper
Gesendet: Thursday, 01. October 2009 01:26
An: 'Midrange Systems Technical Discussion'
Betreff: SQL Challenge

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