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