Hi all,

Have not been on in a bit but have had a request for a new report from a client. I had to do the below code to get the results they wanted but I am sure this could be done via a correlated subquery or maybe even just a subquery. But I could not get a correlated subquery working. I will put my code below although I will simplify the scenario as I don't think it needs the complexity to actually figure out the subquery. I would really like to know how to do this even though this report is working as I am sure I can use it other places.

Scenario: law firm will have files open for both plaintiff and defence files (under different lawyers). They want a list of plaintiffs where the file is settled (field "Settled", boolean) but funds have not yet been received (field Date_Fund, date) and where there is no file funded for this plaintiff. e.g. the plaintiff side has been settled but not funded and they do not want this file on the report if the defence file for this plaintiff has funded. Defence files have field Plain_Defn = "D" and plaintiff files have this as a "P".

- both files have to be from the same Plaintiff (field Plaintf_ID)
- files cannot be the same File_Type (one must be a "P" and the other a "D")
- File_ID is the primary key
- PartFile is the table that holds one record for each "file" (law firms sometimes call this a "matter")
- I am going to put "???"s where I cannot figure out the syntax

This is what I was trying to get to work:

SELECT ;
   Plaintf_ID, File_ID, Lastname, Firstname, Partner, Our_FileNo, Plain_Defn,  ;
     ( SELECT File_ID FROM PartFile WHERE PartFile.Plaintf_ID = ???a AND PartFile.Settled = .T. AND ;
       NOT EMPTY(PartFile.Date_Fund) AND PartFile.Plain_Defn # ???b.Plain_Defn ) AS OtherSideFunded ;
   FROM PartFile ;
   WHERE Settled = .T. AND ;
      EMPTY(Date_Fund) AND ;
      EMPTY(Date_Closd) ;
   INTO CURSOR SettledNF READWRITE

???a - how do I relate the Plaintf_ID in the outer part of the fields list to the subquery i.e. the subquery must only select records with the same Plaintf_ID
???b - similar to the above: how do I write the subquery such that the outer part Plain_Defn is not the same as the field in the inner query

Any help appreciated!

Albert

*** how I did this with multiple queries; note that it includes another filter to do with File type which the above does not do for simplicity ***

*** Step 1 ***

* get the settled files that are not yet funded

* Notes:
* 1) for simplicity, call this cursor "SettledNF" which means Settled not funded
* 2) add column HasOtherFunded to flag on 2nd step which ones have a funded, matching file
* 3) add columns for other side File_Type just for checking as the "combined" files need checking

SELECT ;
   Plaintf_ID, File_ID, Lastname, Firstname, Partner, Our_FileNo, Plain_Defn, File_Type, ;
      .F. AS HasOtherFunded, " " AS OtherFileType ;
   FROM PartFile ;
   WHERE Settled = .T. AND ;
      EMPTY(Date_Fund) AND ;
      EMPTY(Date_Closd) ;
   INTO CURSOR SettledNF READWRITE


*** Step 2 ***

* for a small data set, it is easier to just scan the above results looking for funded files that
* match up with the one that is settled but not funded

* Notes:
* 1) file must be same plaintiff; omit self-join that file_ID not the same
* 2) cannot have the same Plain_Defn i.e. plaintiff files only match with defense and visa versa

SCAN

   STORE Plaintf_ID TO lnPlaintf_ID
   STORE File_ID TO lnFile_ID
   STORE Plain_Defn TO lcPlain_Defn
   STORE File_Type TO lcFile_Type

   SELECT ;
      File_Type ;
      FROM PartFile ;
      WHERE Partfile.Plaintf_ID == lnPlaintf_ID AND ;
         PartFile.File_ID # lnFile_ID AND ;
         NOT EMPTY(PartFile.Date_Fund) AND ;
         NOT PartFile.Plain_Defn == lcPlain_Defn AND ;
         ( PartFile.File_Type == lcFile_Type OR ;
         ( PartFile.File_Type == "C" AND lcFile_Type $ "BT" ) OR ;
         ( PartFile.File_Type $ "BT" AND lcFile_Type == "C" ) ); 
      INTO ARRAY laOtherFiles

   * if nothing returned, no matching files, do nothing; if something returned, mark the record
   * that it has an "other side" funded file and insert the File_Type

   IF _Tally > 0

      REPLACE ;
         HasOtherFunded WITH .T., ;
         OtherFileType WITH laOtherFiles[1]

   ENDIF

ENDSCAN


*** Step 3 ***

* only want those that do not have a funded file on the "other side" of this file don't need all of the
* columns so explicitly state; probably do not even need Plain_Defn or File_Type but leave in for now

SELECT ;
   File_ID, Lastname, Firstname, Partner, Our_FileNo, Plain_Defn, File_Type ;
   FROM SettledNF ;
   WHERE HasOtherFunded = .F. ;
   ORDER BY Partner, Lastname, Firstname ;
   INTO CURSOR Results