Retornar a qtde de registros filtrados com setfilter

[rubens]

Boa tarde...

Alguém sabe se tem alguma função que retorne a qtde de registros filtrados com o set filter to.
Eu tentei reccount() mas retorna a qtde de registros do dbf.

Daí toda vez tenho que mandar varrer o filtro para pegar a qtde de registros.

Obrigado

Rubens

[Itamar M. Lins Jr.]

Ola!
OrdKeyCount()

This example demonstrates using ORDKEYCOUNT() with various
orders:

USE customer
// Assume 1000 total records,
// 500 less than thirty years old, and
// 895 making less than 50,000

INDEX ON Age TO Age

INDEX ON First TO First FOR Age < 30
INDEX ON Last TO Last FOR Salary < 50000

// Age is the controlling order
SET INDEX TO Age, First, Last

? RECCOUNT() // Result: 1000
? ORDKEYCOUNT() // Result: 1000

? ORDKEYCOUNT("First") // Result: 500
? ORDKEYCOUNT(3) // Result: 895

Saudações,
Itamar M. Lins Jr.

[ANDRIL]

Não sei ao certo, acho que OrdKeyCount() seja para saber de um determinado indice e não sobre o SET FILTER TO. Me corrija se estiver enganado.

Tente LastRec().

Ate+

[Itamar M. Lins Jr.]

Não, é para retornar a quantidade de registros no filtro, só que com "index for", não sei se funciona com set filter to.

Saudações,
Itamar M. Lins Jr.

[Euclides]

Oi pessoal...
Vamos manter as coisas mais simples possível...

Código: Selecionar todos

use ...
set filter to ...
COUNT to nNum

nNum terá o numero de registros filrados (pena que vai ter que 'correr' a base de dados inteira...)
T+
Euclides

[fladimir]

Não testei as formas / sugestões dos outros colegas, mas da forma q o Euclides mencionou resolve, mas tem o porém q ele mencionou.

A título de conhecimento adicional, pra quem não sabe, tb pode-se usar o DBEVAL

Pegando a idéia do Euclides ficaria assim:

Código: Selecionar todos

local nRecs := 0
Use ...
Set Filter To ...
Alias->( DBEVAL( { || nRecs++ } ) )

*-- Onde nRecs vai ter o total de Registros da área Filtrada.

[]´s

[aferra]

eu já fiz com set filter e OrdKeyCount() funciona e bem mais rápido.

[Euclides]

Alex, pelo que sei... OrdKeyCount() fornece o número de registros no 'indexfile' .

O "COUNT to nRecs" é traduzido para: "DBEval( {|| nRecs := nRecs + 1},,,,, .F. )" pelo préprocessador e realmente é mais lento do que "DBEval( {|| nRecs++})"... mas só no Harbour. No xHarbour leva quase o mesmo tempo...
Só para constar...
T+
Euclides

[Itamar M. Lins Jr.]

Evitem a todo custo usar "set filter" é um comando que praticamente mata qualquer aplicação em rede. Só use em arquivos com poucos registros. 50/100 tá bom!
use sempre index for temporary, ordscope().
Count eu não mencionei porque é a mesma coisa que ele já faz com "Do While" ou DbEval(), muito lento na rede.

Saudações,
Itamar M. Lins Jr.

[Itamar M. Lins Jr.]

Estava procurando na rede o exemplo do Alexander Kresin...

Optimization of queries by means of index files

It is known, that some operations, such as LOCATE FOR, SUM FOR, moving through the filtered table are quite slow and this may be critical if the database is big enough, especially if it is located in a file server. Using of indexes can significantly accelerate this process, and, surely, if you know in advance the LOCATE or FILTER expression, you will try to do this in your program.

But what if the expression is not known beforehand, if it is determined, for example, by the end user ? It is a case, for which the proposed method is intended.

Program scans all opened indexes and by the analysis of key expressions tries
to find an index, which allows to optimize a performing of a given request.

If it find one, it:

set this index current (SET ORDER TO) ,
calculates a key to search the first record
calculates an expression for DO WHILE cycle .

See at the code below:

Código: Selecionar todos

	
FUNCTION SPEEDFLT( expflt )
/*
    Parameter expflt - an expression, which execution we should
    optimize, for instance, "DNAKL=CTOD("01/01/96").AND.SUMMA>1000000... "
*/
LOCAL rez  := .F.,;  // a variable to keep a result of analysis
      iord := 0,  ;  // index counter
      ielem, elemf, ellen, elemzn, poz, poz1, poz2, poz3, exptmp, expind
 
PUBLIC stseek,;      // a key to search the first record
       stusl         // an expression for <i>DO WHILE</i> cycle
 
   STORE "" TO stseek, stusl
 
   // Beginning of main cycle - scan and analysis all opened indexes 
   DO WHILE !rez
      iord++         // Increment index counter
                     // take a key expression
      expind := Upper( Ordkey( iord ) )
 
      IF Empty( expind )   // Exit, if there no more indexes
         EXIT
      ELSE                 // otherwise - we begin analysis of a key expression
 
/*
      We look for expression items, divided by the sign "+".
      For example, in the expression "DTOS(DNAKL) + NNAKL + STR(CENA,11,2)"
      we have three items:  DTOS(DNAKL), NNAKL and STR(CENA,11,2)
*/
 
      DO WHILE !Empty( expind )
 
        // Look for the next item
        poz := At( "+",expind )
        IF poz != 0
           ielem := Left( expind,poz-1 )
           poz ++
           expind := Substr( expind,poz )
        ELSE
          ielem := expind
          expind := ""
        ENDIF
 
        // Then we check, is the item a function ( by presence of "("
        // or it is only a field name
        poz1 := At( "(", ielem )
 
        IF poz1 == 0
           elemf := ielem // it is a fieldname
        ELSE
           // if we found opening bracket "(", we cut
           // the field name from the item, supposing that it begins right after
           // this parentheses and ends with comma "," or closing bracket ")"
           elemf := Substr( ielem,poz1+1 )
           poz2  := At( ",",elemf )
 
           IF poz2 == 0
             poz2 := At( ")", elemf )
           ENDIF
           IF poz2 != 0
              elemf := Left( elemf,poz2-1 )
           ENDIF
        ENDIF
 
        ellen := Len( elemf )   // Get length of a field name
 
        // Now search this fieldname in 'expflt'
        poz2 := At( elemf, Upper(expflt) )
 
        IF poz2 > 0 .AND. Substr( expflt,poz2+ellen,1 ) == "="
           // If it is found, and it is followed by the "="
           IF At( elemf, Upper( Substr( expflt,poz2+ellen+1 ) ) ) == 0
              /*
               and this field name is not met in the expression anymore
               ( it is needed to exclude expressions with .OR.
               on this field ) to be sure that current key expression allows
               to optimize a performing of an expression.
              */
              rez := .T.
              /*
               Now continue an analysis to build stseek and stusl.
               For this purpose we extract the required value of a field
               from an expression.
               We consider that this value must be in "expflt" immediately
               after the "=", and will be completed by a newline
               or a dot "." ( if there are .AND or .OR. )
              */
              elemzn := Substr( expflt, poz2+ellen+1 )
              poz2 := At( ".", elemzn )
              IF poz2!=0 .AND. Isdigit( Substr( elemzn,poz2+1 ) )
                 poz3 := At(".",substr( elemzn,poz2+1 ) )
                 poz2 := poz2 + Iif( poz3==0, 999, poz3-1 )
              ENDIF
 
              elemzn := Substr( elemzn, 1, Iif(poz2==0,999,poz2-1) )
              // Now build 'stusl'
              stusl += Iif( Empty( stusl ), "", ".AND." ) + elemf + "=" + elemzn
              // and 'stseek'. For this we substitute found line with value
              // fields (elemzn) in key expression item instead of a field name,
              // compute a tin expression and add to stseek!
              exptmp := Stuff( ielem, poz1+1, ellen, elemzn )
              stseek := stseek + &exptmp
           ENDIF
        ENDIF
        IF !( elemf $ stusl )
           // If next element not suitable for optimization,
           // stop an analysis of current key expression.
           EXIT
        ENDIF
      ENDDO
    ENDIF
  ENDDO
 
  IF rez
     // If suitable index is found, set it as current
     Ordsetfocus( iord )
  ENDIF
 
RETURN rez

Now we can optimize, for example the LOCATE FOR command:

Código: Selecionar todos

	
FUNCTION FINDREC( expfnd ) // expfnd - condition for searching
LOCAL rez := .F.
 
   IF Speedflt( expfnd )   // If the request is optimized,
      Dbseek( stseek )     // search first record, partly satisfying
   ELSE                    // the condition, otherwise -
      GO TOP               // go to the beginning of a file
      stusl := ".T."
   ENDIF
   rez := .F.
   DO WHILE !Eof() .AND. &stusl
      IF &expfnd
         rez := .T // Suit record is found!
         EXIT
      ENDIF
      // Here you can insert something to animate the process of searching
      SKIP
   ENDDO
RETURN rez

It is easy now to write similar functions for SUM... FOR..., COUNT FOR..., etc.

You may redefine corresponding Clipper commands - and they will work much more quickly in case of the expression is optimizable, for example:

#command LOCATE [FOR ] => Findrec( <{for}> )

To optimize the movement through a filtered table you may write a function, similar to the Findrec(), which will place numbers of filtered records in the special array for following use and redefine commands for moving through the table - such as GO TOP, GO BOTTOM, SKIP. If you use TBrowse, you should define their own codeblocks for moving down the filtered table.

It is necessary to note that after filtration records will be located not in the initial order, but in accordance with that index, which was set by the Speedflt(). If you want records were located in order, determined by that index, which was current before filtration, it is possible to include in abovementioned array a line

KEY + STR(record number),

where KEY - a value of key expression of initial index for given record ( &(Ordkey(iord)) ). After terminating the filtrations just sort a received array.

Saudações,
Itamar M. Lins Jr.

[janio]

Eu ja tinha tentado usar o OrdKeyCount() pra saber a quantidade de registros num filtro, mas tinha encontrado um problema e não lembrava qual problema seria esse...

Ocorre q agora, fazendo uns testes, caiu em cima: OrdKeyCount() conta tbm os registros MARCADOS COMO DELETADOS! agora lascou pq ja tinha mudado todo o sistema pra usar ordkeycont...

lascou!

Exemplo concreto:

Código: Selecionar todos

Select dbf
DbSetOrder(1)
DbGoTop()
OrdScope( 0, filtro )
OrdScope( 1, filtro )
DbGoTop()

Count to nn

dd = OrdKeyCount()

nn retorna 2
dd retorna 4 (ha dois registros deletados)

O Problema q count eh muuuuuuito lento numa base grande! Pra mim eh inviável usar count...

Faço o q agora?

[alxsts]

Olá!

Como está setado o SET DELETED quando você executa o OrdKeyCount()?

[janio]

SET DELETED ON

mas fiz teste aqui com SET DELETED OFF e o resultado eh o mesmo!

preciso muito saber total de registros depois de um filtro... ha algumas rotinas cruciais no sistema q usa isso.

Poxa...

Janio

[Itamar M. Lins Jr.]

É só usar a função que postei logo acima.

Saudações,
Itamar M. Lins Jr.

[janio]

https://groups.google.com/forum/#!topic ... 5HVv69N3Ak

Código: Selecionar todos

In both cases it's the same reason: 
   set deleted on 
ordkeycount() returns number of keys in index regardless of SET DELETE ON 
flag. If you remove this command from your example then you will access 
deleted records in your skip loop. 

best regards, 
Przemek