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