Tim,

I am discovered Derived tables but I am have trouble making them work in a program.

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

* drop a TEDBQuery component on a form
* set the SQL to the above
* define the persistent fields in the Field editor.

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;

The open fails as the persistent fields are no longer available.  If the Derived table is
replaced by the real table (JobFields) then the persistent fields are available.

I can make it work by removing the persistent fields.

Richard Harding.

Richard,

<< The open fails as the persistent fields are no longer available.  If the
Derived table is replaced by the real table (JobFields) then the persistent
fields are available. >>

What is the error message that you're seeing ?

--
Tim Young
Elevate Software
www.elevatesoft.com

>>What is the error message that you're seeing ?

EDatabaseError with message 'qyJobFields: Field 'X' not found'

I will send you a example project shortly.


Richard Harding