Find mismatch records from two tables.

[Niki_S]

I have my tables as below.

_U1

nStylecode     cLotName      Delvr_Pcs
123            A1            100
456            COMR          750
785            A2            452

U2

nStylecode     cLotName      Delvr_Pcs
123            A1            105
456            COMR          750
785            A2            450

Now I want to find mismatch records from these tables. For that I did my code like this, but it is not working.

SELECT * FROM _U1 WHERE _U1.nStylecode=U2.nStylecode AND _U1.cLotName=U2.cLotName AND _U1.Delvr_Pcs<>U2.Delvr_Pcs INTO CURSOR _DELQTY

How can I do this?

Thank you

 

[Chris Miller]

The query you have should give you the third _U1 record. So what's wrong with that?

Do you also want the record of U2?

You only SELECT * FROM _U1, so you don't get U2 records. For that you need to reverse the names:

SELECT * FROM U2 WHERE U2.nStylecode=_U1.nStylecode AND U".cLotName=_U1.cLotName AND U2.Delvr_Pcs<>_U1.Delvr_Pcs INTO CURSOR _DELQTY2

Then to get both records you need a UNION:

(SELECT * FROM _U1 WHERE _U1.nStylecode=U2.nStylecode AND _U1.cLotName=U2.cLotName AND _U1.Delvr_Pcs<>U2.Delvr_Pcs)
UNION
(SELECT * FROM U2 WHERE U2.nStylecode=_U1.nStylecode AND U".cLotName=_U1.cLotName AND U2.Delvr_Pcs<>_U1.Delvr_Pcs)
Into Cursor AllDifferentRecords

You wouldn't know which record came from where. I suggest, that you only query the combinations of nStylecode and cLotname that have differing Delcr_Pcs. That's enough info, the different Pcs counts are still in the _U1 and U2 data and can be queried from that info alone:

Which boils down to your own select, just not including the Delvr_Pcs:

SELECT nStylwcode, cLotName FROM _U1 WHERE _U1.nStylecode=U2.nStylecode AND _U1.cLotName=U2.cLotName AND _U1.Delvr_Pcs<>U2.Delvr_Pcs INTO CURSOR _DelErrors

And from there you could get the difference:

SELECT _DelErrors.*, _U1.Delvr_Pcs - U2.Delvr_Pcs AS DifferenceU1minusU2 From _DelErrors;
Inner Join _U1 on _U1.nStylecode=_DelErrors.nStylecode AND _U1.cLotName=_Delerrors.cLotName ;
Inner Join U2 on U2.nStylecode=_DelErrors.nStylecode AND U2.cLotName=_Delerrors.cLotName

Chriss