Here's a non-SQL way to do the check for duplicates (useful to find values that
do not work in a Primary key in converting data or using existing data). This gave me a list of duplicates with the record numbers and field values of duplicate records. The table was indexed on the field that was not working as a primary key. Hope this helps.
LOCAL cCNTID1, cCNTID2, cCMPID1, cCMPID2
cCNTID1 = ""
cCNTID2 = ""
cCMPID1 = ""
cCMPID2 = ""
USE C:\MVS\MS_8021.DBF && OPEN TABLE
SET ORDER TO 3 && Indexed on CNT_ID - the field to check for duplicates
SET TEXTMERGE TO C:\MVS\MS_8021DUP.TXT && Open the text output file
SET TEXTMERGE ON NOSHOW && Turn on the textmerge capability w/o console output
LOCATE && GO TOP
SCAN
* Capture the values in the current record for comparison with the next
cCNTID1 = CNT_ID
cCMPID1 = CMP_ID
cRECORD1 = STR(RECNO(),5,0)
SKIP && move to the next record
IF CNT_ID = cCNTID1 && make the comparison
* if values the same output the values from each record
TEXT
<
> / <> / <> / <> / <> / <>
ENDTEXT
ENDIF
SKIP -1 && move the record pointer back to previous record
* because SCAN will do a skip forward to evaluate the next two
ENDSCAN
SET TEXTMERGE OFF
