>I have a SQL query that I am trying to run and it will not return any records in the result set when I run it programatically. When I run the SQL command from the command line, it works fine. My workaround is to run the same query twice in a row. The first time it runs, nothing is returned, the second time it is run, I get the proper results.
>
>I know that this is a bug in VFP. How do I know that other queries that return an empty result aren't a result of a VFP bug? Has anyone else run into this problem? What did you do to resolve the issue?
>
>Here is the SQL:
> SELECT * FROM SourceDBF ;
> WHERE ;
> PRIN + Agent IN (SELECT PRIN + Agent FROM FAT_BASIC_AREAS_EXCLUSIONS) AND ;
> NOT DELETED() ;
> INTO CURSOR curToRemove NOFILTER
>
>
>David
1. Try to remove NOT DELETED() and just run with SET DELETED ON
2. Would you get the same problem if you change it to
SELECT * FROM SourceDBF Source ;
> WHERE EXISTS (select 1 from FAT_BASIC_AREAS_EXCLUSIONS Fat where Fat.PRIN + Fat.Agent = Source.PRIN + Source.Agent) ;
into cursor curToRemove nofilter
I think the problem might be in different SET ANSI settings.
If it's not broken, fix it until it is.
My Blog