Answered by:
Duplicated records

Question
-
im lost now... i need to create a program that recive a Table 1... run it searching for duplicated records... copy them (duplicated records) to table 2 and the others(that dont have any duplicated) copy to table 3... im exausted to think about that... i need help...
what kind of tables i have:
T1= messy full of duplicated records
T2= T1 without the duplicated records
remember pls: what i need is a table with the list of whichs records are duplicated
(before running)
T1 T2 TABLE3
A A
B B
C C
A
B
C
(after running)
TABLE 1 TABLE2 TABLE3
A A A
B B B
C C C
A D
B E (notice that E is not duplicated in t1 so it cant appear on t3)
C
D
D
E
BWednesday, October 15, 2008 7:35 PM
Answers
-
Code Snippet
SELECT
livro,tipo,folha,termo,nome,mae,cartorio,mode ; FROM (m.table1) ; HAVING COUNT(*) > 1 ; GROUP BY livro,tipo,folha,termo,nome,mae,cartorio,mode ;INTO TABLE ('c:\barrados')
You could also use scan...endscan (and it would be fast) but Select-SQL is easier to code and portable to other backends.
PS: It is not complicated at all but you need to explain your problem well.
Thursday, October 16, 2008 8:52 AM
All replies
-
Leonardo,
I think nobody would understand what you are asking. Try elaborating your question. Also define what is a duplicate for you.
Wednesday, October 15, 2008 8:36 PM -
ok.. here it is... first a duplicated record is 2 records that has the same key.. the exctaly same value...
and as an answer i was hoping for something like... try this code here... i gave alot of time to it and all i came up was this:
CLOSE DATABASES
CREATE TABLE c:\barrados (LIVRO N(6), TIPO C(6), FOLHA n(6), TERMO n(6),NOME c(68), MAE c(68),cartorio c(15), mode n(1))
table1 = GETFILE('DBF', 'Tabela 1','SET', 1, 'Analisar')
USE (table1)
FOR i =1 TO RECCOUNT()
GO RECORD i
var1=livro
var2=tipo
var3=folha
var4=termo
var5=nome
var6=mae
var7=cartorio
FOR c1 = i+1 TO RECCOUNT()
GO RECORD c1
IF var1==livro AND var3==folha AND var4==termo AND var5==nome AND var6==mae
INSERT INTO barrados (livro,tipo,folha,termo,nome,mae,cartorio,mode) ;
VALUES (var1,var2,var3,var4,var5,var6,var7,1)
ENDIF
ENDFOR
ENDFOR
CLOSE DATABASES
it works... slow.. but works... i set table1... and in "barrados" i have all registers that are repeated in table1...
important to notice: it works kind like this... it "take" record i and compare with all bellow him... i think i cant use scan or select cause they will compare the record X with all the table... and eventually u will compare X with itself and will acuse the X is duplicated when it really isnt... get it? its complicated... if u need even futher explanation just tell me...Thursday, October 16, 2008 3:13 AM -
*-- With this SELECT you'll get unique records in a cursor
SELECT * FROM T1 GROUP BY Field1 ORDER BY Field1 INTO CURSOR T2
*-- With this SELECT you'll get records not duplicated
SELECT * FROM T1 GROUP BY Field1 HAVING COUNT(*) = 1 ORDER BY Field1 INTO CURSOR T3
I only use "Field1" as group criteria because you do not tell nothing about fields; you must put in GROUP BY clause all the fields that let you find a duplicated record.
Best regards
dD
Thursday, October 16, 2008 3:14 AM -
Code Snippet
SELECT
livro,tipo,folha,termo,nome,mae,cartorio,mode ; FROM (m.table1) ; HAVING COUNT(*) > 1 ; GROUP BY livro,tipo,folha,termo,nome,mae,cartorio,mode ;INTO TABLE ('c:\barrados')
You could also use scan...endscan (and it would be fast) but Select-SQL is easier to code and portable to other backends.
PS: It is not complicated at all but you need to explain your problem well.
Thursday, October 16, 2008 8:52 AM -
tks alot!!! thats perfect!!!Thursday, October 16, 2008 12:39 PM