General information
Category:
Databases,Tables, Views, Indexing and SQL syntax
Environment versions
Network:
Windows 2008 Server
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
Next
Reply
View the map of this thread
View the map of this thread starting from this message only
View all messages of this thread
View all messages of this thread starting from this message only