locked
Duplicated records RRS feed

  • 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
    B                                       

    Wednesday, 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