Tim,

Another derived table example.

The SQL is:

SELECT *
  FROM UserDefinedFields
     LEFT OUTER JOIN (SELECT * FROM JobFields WHERE JobID = 8004) T1
        ON T1.UserFieldID = UserDefinedFields.ID   
  WHERE UserDefinedFields.ClientType = 2
  ORDER BY Sequence

In the program:

 qyJobFields.SQL.Clear;
 qyJobFields.SQL.Add('SELECT * FROM UserDefinedFields AS UDF ');
 qyJobFields.SQL.Add('LEFT OUTER JOIN (SELECT * FROM JobFields WHERE JobID =  ');
 qyJobFields.SQL.Add(dmData.qyJobsForContact.FieldByName('ID').AsString);
 qyJobFields.SQL.Add(') T1 ON T1.UserFieldID = UserDefinedFields.ID ');
 qyJobFields.SQL.Add('WHERE UserDefinedFields.ClientType = 2 ');
 qyJobFields.SQL.Add('ORDER BY Sequence');
 qyJobFields.Open;

 If I then do a Locate on other table (taX.Locate('ID', SomeValue, []), then an AV occurs.

If I replace the derived table with the table JobFields the AV does not occur.

Does this make any sense?  



Richard Harding.

Richard,

<< If I then do a Locate on other table (taX.Locate('ID', SomeValue, []),
then an AV occurs.

If I replace the derived table with the table JobFields the AV does not
occur.

Does this make any sense?  >>

Not really.

Could you possibly send me an example of this, including the data ?

--
Tim Young
Elevate Software
www.elevatesoft.com