But do not stop investigating, I still think Thomas' multiple SQL suggestion (or pure xBase) would be much faster. Somewhere along the posts I think I saw 50seconds as original timing and that means now around 20-25 which seems very slow to me. Probably you don't have right indexes ?
Cetin
>Thanks for all the suggestions. The UNION was the quickest, it cuts down the time by more than half. This is the expression I ended up with:
>
>Select b.cfk1 , b.cfk2 ,' ' as ctype ;
> from vcf!many b ;
> where !(namount1 = 0 and namount2 = 0) ;
>union ;
>Select b1.cfk1 , b1.cfk2 ,a1.ctype ;
> from vcf!many b1 ;
> inner join vcf!one a1 on b1.cfk1 = a1.cid ;
> where b1.namount3 # 0 and a1.ctype in ('F','8')
>
>Many thanks
>Cyril.
>
>>
>>WHERE iif( b.namount3 <> 0 and a.ctype$"F8",.t.,;
>> !(b.namount1 = 0 and b.namount2 = 0) ) ;
>>...
>>
>>However I second to Thomas' idea about doing another SQL. Yet another way :
>>
>>Select b.cfk1, b.cfk2,a.ctype ;
>> from vcf!many b ;
>> inner join vcf!one a on b.cfk1 = a.cid ;
>> where !(namount1 = 0 and namount2 = 0) ;
>>union ;
>>Select b1.cfk1, b1.cfk2,a1.ctype ;
>> from vcf!many b1 ;
>> inner join vcf!one a1 on b1.cfk1 = a1.cid ;
>> where b1.namount3 # 0 and a1.ctype in ('F','8')
>>Not exactly the same as grouping but I don't get why a grouping when cType is there anyway.
>>About OR unfortunately I don't have a solid sample on hand. I can't say avoid, personally I do avoid. At least check in query window how it interprets your OR, I saw it rearranges it so that it's no more my original expression (though I use parentheses).
>>Cetin
>>
>>>Thanks for both the ideas. I'll test at the office in the morning.
>>>
>>>However, b.namount1 + b.namount2 <> 0 won't work. For example, it won't pick up: 111 - 111 = 0. I want the record if it has a non-zero in either field.
>>>
>>>Cetin - Regarding "OR":-
>>>
>>>I use "OR" a lot in views. Do you have any further information - should one always avoid OR?
>>>
>>>Thanks
>>>Cyril.
>>>
>>>
>>>
>>>
>>>>>I'd try a similar variation
>>>>>
>>>>>Select b.cfk1, b.cfk2, a.ctype;
>>>>> FROM vcf!many b ;
>>>>> INNER Join vcf!one a ON b.cfk1 = a.cid;
>>>>> WHERE (b.namount1 + b.namount2 <> 0) or ;
>>>>> (b.namount3 <> 0 and a.ctype$"F8" ) ;
>>>>> GROUP By b.cfk1, b.cfk2
>>>>>
>>>>>which should use short circuiting a bit better.
>>>>>Perhaps an index on namount1 + namount2 in vcf!one
>>>>>speeds things up - check the optimizing results,
>>>>>since I am not sure about <> being optimized.
>>>>>If it isn't, try
>>>>>
>>>>>
>>>>>WHERE (b.namount1 + b.namount2 > 0) or ;
>>>>> (b.namount1 + b.namount2 < 0) or ;
>>>>> (b.namount3 <> 0 and a.ctype$"F8" ) ;
>>>>>
>>>>>
>>>>>HTH
>>>>>
>>>>>thomas
>>>>
>>>>At least = is optimized :) !(a=b) would work I think. I was trying to avoid OR completely as I always found it to be problematic in SQL.
>>>>Cetin
