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
Same as I had before. No indexes in use. Takes forever.


>>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