Hello everyone,
I have a problem with an SQL query I am trying to run in my FoxPro 7.0 program. The problem is that when I run the code from the command window, my query works. When I run the query from my program, I get an error saying that the SELECTS are not UNION compatible. Another strange thing is that the tables I am querying are in a database container. When I run the code in a test environment with only me as the user, the code runs fine. When I run the program in an environment with multiple users, the program gives me the above mentioned error. I have tried to set exclusive off before I run the query, which is the only way that the query would work when I ran it in the command window, without any luck. Does anyone have any suggestions? Is the Union all statement trying to grab everything exclusively for some reason? The query I have written is as follows:
SELECT ad.vendno, ad.invno, NVL(ac.checkno,' ') as checkno;
from invoice as ad;
left join check as ac;
ON ad.invno = ac.invno;
and ad.vendno = ac.vendno;
WHERE vendno = 'X';
UNION ALL;
SELECT ad.vendno, ad.invno, NVL(ac.checkno,' ') as checkno;
from invoice2 as ad;
left join check2 as ac;
ON ad.invno = ac.invno;
and ad.vendno = ac.vendno;
WHERE vendno = 'X' into cursor bob

Any help would be appreciated. Thanks