/* JOIN WITH ... TO ... FOR ... — uses non-reserved alias names. */

PROCEDURE Main()
   LOCAL aStruct, n

   FErase( "cust.dbf" )
   FErase( "ord.dbf" )
   FErase( "out.dbf" )

   /* Customers (master) */
   aStruct := { ;
      { "CID",   "N", 4, 0 }, ;
      { "CNAME", "C", 12, 0 } }
   dbCreate( "cust.dbf", aStruct )
   USE cust.dbf NEW EXCLUSIVE ALIAS cu
   dbAppend() ; FieldPut(1, 1) ; FieldPut(2, "Alice")
   dbAppend() ; FieldPut(1, 2) ; FieldPut(2, "Bob")
   dbAppend() ; FieldPut(1, 3) ; FieldPut(2, "Carol")
   dbCommit()
   dbCloseArea()

   /* Orders (detail) */
   aStruct := { ;
      { "OID",   "N", 4, 0 }, ;
      { "CID",   "N", 4, 0 }, ;
      { "AMT",   "N", 8, 2 } }
   dbCreate( "ord.dbf", aStruct )
   USE ord.dbf NEW EXCLUSIVE ALIAS od
   dbAppend() ; FieldPut(1, 100) ; FieldPut(2, 1) ; FieldPut(3, 50)
   dbAppend() ; FieldPut(1, 101) ; FieldPut(2, 1) ; FieldPut(3, 30)
   dbAppend() ; FieldPut(1, 102) ; FieldPut(2, 2) ; FieldPut(3, 200)
   dbAppend() ; FieldPut(1, 103) ; FieldPut(2, 4) ; FieldPut(3, 99) /* no-match cid */
   dbCommit()
   dbCloseArea()

   /* Open both */
   USE cust.dbf NEW EXCLUSIVE ALIAS cu
   USE ord.dbf  NEW EXCLUSIVE ALIAS od
   SELECT cu

   ? "--- JOIN WITH od TO out FIELDS cid, cname, oid, amt FOR cu->cid = od->cid ---"
   JOIN WITH od TO out.dbf FIELDS cid, cname, oid, amt FOR cu->cid = od->cid

   /* Close both source areas */
   SELECT cu
   dbCloseArea()
   SELECT od
   dbCloseArea()

   USE out.dbf NEW EXCLUSIVE
   COUNT TO n
   ? "Joined rows =", n, "(expect 3)"

   dbGoTop()
   ? "Field count =", FCount(), "(expect 4: cid, cname, oid, amt)"
   ? ""
   ? "rows:"
   DO WHILE !Eof()
      ? "  cid:", FieldGet(1), "name:", AllTrim(FieldGet(2)), "oid:", FieldGet(3), "amt:", FieldGet(4)
      dbSkip()
   ENDDO
   ? "(expect: 1 Alice 100 50 / 1 Alice 101 30 / 2 Bob 102 200)"

   dbCloseArea()

   FErase( "cust.dbf" )
   FErase( "ord.dbf" )
   FErase( "out.dbf" )
   ? "DONE"
   RETURN