I have the following VFP code:
SELECT m.ccompno, AVG(i.ncost) ;
FROM ICIWHS i inner join MIBOML m ;
on i.citemno + cwarehouse = m.ccompno ;
where m.citemno = '02006' ;
GROUP BY m.ccompno ;
ORDER BY m.ccompno

Table ICIWHS has an index on citemno + cwarehouse, MIBOML is endexed on citemno
Code works as expected.

On SQL side I have this:
select * from openquery(VAMCPD, 'SELECT m.ccompno, AVG(i.ncost) FROM ICIWHS i inner join MIBOML m
on i.citemno + cwarehouse = m.ccompno
where m.citemno = ''02006'' group by m.ccompno')

That returns no records ????

What I have found is that it only works if comnad looks something like this:

select * from openquery(VAMCPD, 'SELECT * FROM ICIWHS
where citemno + cwarehouse like ''02006''')

Works very fast.

This will work too:
select * from openquery(VAMCPD, 'SELECT m.ccompno, AVG(i.ncost) FROM ICIWHS i inner join MIBOML m
on i.citemno = m.ccompno
where m.citemno = ''02006'' group by m.ccompno')
but will take foreever.

What I am saying is it really likes LIKE when it comes to use indexes.

Any idea why:
select * from openquery(VAMCPD, 'SELECT m.ccompno, AVG(i.ncost) FROM ICIWHS i inner join MIBOML m
on i.citemno + cwarehouse = m.ccompno
where m.citemno = ''02006'' group by m.ccompno')
does not work?

Thanks