Plain_Defn and File_Type are only 1 character and DateOfLoss is D(8); yes, mostly doing it out of habit - in cases where the field is wider than 1 char, I would also TRIM() where necessary for exact matches.

I have actually been trying to "untrain" myself to not use the == where it is not necessary - not always successfully :-)

>What are the lengths and types of the columns Plain_Defn, DateOfLoss and File_Type?
>
>Are you using == just out of habit of there is some reason.
>
>>Hi all,
>>
>>Also working up more queries for this customer and normally the below query would have been probably multi-step with the outer query being worked up first and then the inner query being a SCAN of the results of the outer query to get more detail. So I thought I would try it as a correlated query. Throws an error and on reading the error message, it seems you cannot do anything other than compare two fields in a correlated subquery.
>>
>>Here is what I tried; the last 2 lines of the subquery cause it to choke (the data is a bit "weird" in the table so have to do comparisons against hard-coded values):
>>
>>

>>SELECT ;
>>   File_ID, Plaintf_ID, Lastname, Firstname, Our_FileNo, DateOfLoss, Plain_Defn, File_Type ;
>>   FROM PartFile ;
>>   WHERE EMPTY(Date_Closd) AND ;
>>      EXISTS ;
>>      ( SELECT File_ID FROM PartFile PF ;
>>         WHERE NOT EMPTY(PF.Date_Closd) AND ;
>>            EMPTY(PF.Date_Fund) AND ;
>>            PF.DateOfLoss == PartFile.DateOfLoss AND ;
>>            PF.Plain_Defn == PartFile.Plain_Defn AND ;
>>            ( PF.File_Type == PartFile.File_Type OR ;
>>              PF.File_Type == "T" AND PartFile.File_Type == "C" OR ;
>>              PF.File_Type == "B" AND PartFile.File_Type == "C" ) ) ;
>>   ORDER BY PartFile.File_ID ;
>>   INTO CURSOR Results
>>

>>
>>Got the message "SQL: error correlating fields" (error 1801) and the help text says:
>>

>>An outer reference can be used only in syntax similar to the following:
>>X = Y 
>>Syntax such as X = Y + 1 or X = 5 will produce this error.
>>

>>
>>Is this fixable or do I revert to doing a SCAN to check the values? Just curious - this code will not be used much so it's not like I have to really optimize it.
>>
>>Albert