I love the new SQL features of VFP9, but have encountered a problem with SQL Correlation in Projection (field select) sub-queries. I have encountered this problem on several occasion, but this is the first one I haven’t been able to work around.

I am constructing a query based on an inherited data design I can’t change. It includes a main query on table1 and a sub-query to populate a field base on the value in table2. The return will be a character string result depending on relative values of a field in Table1 and a field in Table2. I will use an ICASE to determine which value to return, and that works fine.

There can only be 1, 2, or 3 records in table2, and It is easy to determine the correct return from the sub-query when there are 1 or 2 records to evaluate, by using MIN() and MAX() in the SELECT statement. However, finding the “middle” record is more difficult. It can be done my running a sub-query in the WHERE clause with a NOT IN comparison against MIN() and MAX() which also works fine up to a point.

The problem is that the source table in the WHERE clause sub-query on table2 must have a WHERE clause to limit the return to a single record, as required in a projection, the comparison value can only be found in tbalke1 which is not in the immediate query.. Yeah, I know this is confusing <g>.

Here is some pseudo code:

SELECT *;
   CAST(;
      ICASE(;
         <condition 1>, <result1>,;
         <condition 2>, <result2>,;
         (SELECT COUNT(*) 
            FROM table2 cursor1 
            WHERE cursor1.fkey1 = table1.field1) > 2 AND pct >= 
               (SELECT threshold 
                FROM table2 cursor2 
                WHERE cursor2.field = table1.field1 AND threshold NOT IN;
                   (SELECT MIN(threshold) ;
                       FROM table2 cursor3;
                       WHERE cursor3.field = table1.field1) AND threshold NOT IN;
                   (SELECT MAX(threshold);
                       FROM table2 cursor4;
                       WHERE cursor4.field = table1.field1)), <result3>;
      ) ;
   AS C(3) AS <field name>;
FROM table1

The problem appears to be that table1.field1 is not accessible to the nested sub-queries because while it is in the outer containing query it is not in the intermediate containing sub-query which has the WHERE clause, resulting in a correlation error

Anyone have any ideas on how to work around this kind of correlation issue?