I put together a simple VB program to run SQL commands on any of our 4 I-series machines to return a simple grid view. I have run across a strange problem and was told it may have to do with optimization. For example if I run the following SQL ( I just want a count of parts (pm) that have not been sold in our order detail file (o6).
select count(*) from pm left join o6 on pmpart = odpn where o6ord# is null
using the LF's on the I-series I get the error message "SQL0666 SQL query exceeds specified time limit or storage limit. "
If I change the SQL to use the following using the PF's of those LF's it runs fine in about 2 seconds.
select count(*) from pmp
left join o6p on pmpart = odpn
where o6ord# is null
Where is the optimization issue on the server side or the I-series side ?
Thanks in advance.