Steve,

Try

         (SELECT COUNT(*) 
            FROM table2 cursor1 
            WHERE cursor1.fkey1 = table1.field1) > 2 AND pct >= 
               (SELECT threshold 
                FROM table2 cursor2 
                WHERE cursor2.field = cursor1.fkey1 AND threshold NOT IN;
                   (SELECT MIN(threshold) ;
                       FROM table2 cursor3;
                       WHERE cursor3.field = cursor2.field) AND threshold NOT IN;
                   (SELECT MAX(threshold);
                       FROM table2 cursor4;
                       WHERE cursor4.field = cursor2.field)), <result3>;

>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?