Tim,

I am executing the following queries in EDB Manager (V2.02 b6).

SELECT *
 FROM UserDefinedFields
    LEFT OUTER JOIN (SELECT * FROM ContactFields WHERE ContactID = 10852) T1
        ON T1.UserFieldID = UserDefinedFields.ID
  WHERE UserDefinedFields.ClientType = 1

Works Fine.

If it is followed by

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

whilst the first query is prepared, then the columns in the derived table are all nulls
(and they are not).

If I unprepare the first query and execute the second query all is well.

If I change the correlation name in the second query to something else then I can execute
the 2 queries at the same time.
Hopefully it makes more sense this time.

Richard Harding

Richard,

<< If I unprepare the first query and execute the second query all is well.

If I change the correlation name in the second query to something else then
I can execute the 2 queries at the same time. Hopefully it makes more sense
this time. >>

Sorry about the delay in getting back to you on this.

Yes, you cannot use the same correlation name at the same time.  Derived
tables are implemented as temporary views, therefore naming conflicts will
result in clashes between the two temporary views.

I thought that I made a note in the manual about this, but I don't see one
so I'll have to add it.

--
Tim Young
Elevate Software
www.elevatesoft.com

Thanks Tim

>>Yes, you cannot use the same correlation name at the same time.  Derived
tables are implemented as temporary views, therefore naming conflicts will
result in clashes between the two temporary views.

So that would mean if I am executing the query from an application or stored procedure
then I should ensure that the correlation name is unique by appending the date/time (or
something) to the correlation name??

Is it closing the query that removes the temporary View?


Richard Harding

Richard,

<< So that would mean if I am executing the query from an application or
stored procedure then I should ensure that the correlation name is unique by
appending the date/time (or something) to the correlation name?? >>

Yes, that or use a fairly unique name for each derived table that will be
used concurrently in the same session.

<< Is it closing the query that removes the temporary View? >>

It's the Unprepare that removes it.  If you didn't manually prepare the
query, however, then a simple Close will also Unprepare the query.

--
Tim Young
Elevate Software
www.elevatesoft.com