multiple scan - limiting table scan

[happydermis]

Hallo Kathleen,____I am working on a report with a multiple scanning. As you know such a report needs a _pseudo_ master table, which is limited to the scope 1 and a link to bind the further scanning tables. ____The problem is: because of this working method I cannot limit the scope of the actual _master_ tables I use. I use a relative small table as the linking table and 2 tables under it (on the same level) and from each of those tables I scan further tables accordingly. My two _master_ tables are thus not limited and since they contain about 600000 tuples each, they need a lot of time to be scanned.____My question: is there a way to limit the scope of those two _master_ tables? Is there a way of doing it with queries? I was searching the documentation for some clues but couldn_t find anything.____Many thanks, Dekers__

[kfleming]

There is not a way to scope the tables that are being scanned by the master. What you could do however is build a conditional index on the scanned table (for example you could create an index on a constant value but set the condition for the index key as ACCTNO>123__to have that index only records where the ACCTNO field had a value greater than 123. In the report we would then only see the records that meet the index condition.____Kathleen__R&R Support

[happydermis]

Thanks for the answer! That sounds promising :-)____How do I create a conditional index?? Is this the value of the ^Link^ field? ____OR____Maybe do you have any screen shots or report examples you could send me? That would be great. My eMail address: r.schuchmann@zeltwanger.de____I am most grateful, Dekers____

[Larissabut]

I just finished a couple of reports similar to yours (fortunately, mine only had 300K records in each multi-scan table). The report looked like this:____ctrl ------ ---Today [flexlink on constant ^XXXXX^]__ |__ ---ThisWeek [flexlink on constant ^YYYYY^]__ |__ ---ThisMonth [flexlink on constant ^ZZZZZ^]____ctrl had 1 record and the three linked tables were actually all the same table.____Unfortunately, R&R^s FlexLink does not contain an IIF() function, so you can^t create a conditional index this way. You might be able to do something with a logical index [i.e. LEFT(DTOC(ONDATE, 1), 4) = "2004" ] and link to it with a calculated field with a value of .T.____If you have access to an Xbase development environment like FoxPro, you can create a conditional index for your table as below:____INDEX ON IIF(ONDATE > {^2004-01-01}, "XXXXX", "UUUUU") TAG Vari01__INDEX ON IIF(ONDATE > {^2004-01-01}, "YYYYY", "UUUUU") TAG Vari02__INDEX ON IIF(ONDATE > {^2004-01-01}, "ZZZZZ", "UUUUU") TAG Vari03____This would give me an index that would work only for records with dates after January 1, 2004.____You^d also have three calculated fields defined:____Calc Field Name Value (Expression)__--------------- ------------------__DayLink "XXXXX"__WkLink "YYYYY"__MthLink "ZZZZZ"____and these would be put into the relations. When the link occurred, only those that matched would work. This should cut down on the data.________