>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

Mark,
Though it works, I suggest not to use "m" as an alias.

Your queries are not the same. When calling from a linked server in MSSQL you are implicitly using "ANSI ON". VFP's default is ANSI OFF. Test your first query in VFP:

Either like this:

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

or this:

set ansi on
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

Is the result same as you get from linked server?

Probably what you need is (I say probably because I am not sure if it were an exact or partial match between citemno+cwarehouse and ccompno):

select * from openquery(VAMCPD,  
'SELECT mi.ccompno, AVG(ic.ncost) FROM ICIWHS ic inner join MIBOML mi  
  on ic.citemno + ic.cwarehouse like mi.ccompno + ''%''
where mi.citemno = ''02006'' 
group by mi.ccompno')

Cetin