Nested Query - Syntax Problem

[JRB-Bldr]

I have been trying to replace some working code which currently uses multiple table relationships to produce separate results eventually coming together into a single end-result table.

The code that I would like to replace it with would be a single 'nested query'.

The code I attempted to use is as follows:

 SELECT LoadHist.List_ID,;
        LoadHist.Load_Date AS LastLoad;
   FROM LoadHist;
   WHERE LoadHist.Agent_Code IN ;
     (SELECT AgtMaster.Agent_Code,;
         AgtOrder.Quota;
       From AgtMaster, AgtOrder;
       Where AgtOrder.Agent_Code = AgtMaster.Agent_Code;
       AND EMPTY(AgtOrder.End_Date);
       AND AgtOrder.Appl = "ABCD")

I know that the 'internal' query works since I executed it independently before putting it inside the 'nest'. And I know that the fields all exist in the appropriate tables since they are all used and referenced in the above mentioned working code.

But when I execute the above code I get an error message
SQL: Subquery is invalid

Your insight and advice would be most welcome.

Thanks,
JRB-Bldr

 

[baltman]

VFP 9 or before?

 

[JRB-Bldr]

My apologies for not saying...
VFP7

 

[Dan Freeman]

Take the second column out of the subquery. I'm pretty sure you can only have one column in a subquery used with IN.

 

[baltman]

Here's a good SQL reference... even if it is focused on VFP9

http://foxcentral.net/microsoft/WhatsNewInVFP9_Chapter08.htm

which can be found off of VFP's homepage at

http://www.msdn.com/vfoxpro

Brian