Is it possible to do a sub query in a from clause? I could not find anything about it in the documentation.

My query looking about like that:

select sourceT.EntryId, targetT.EntryId, targetT.ValueT, sourceT.ValueT from OBJECT_LIST targetT, (select * from OBJECT_LIST where Consumer = 359) sourceT where targetT.SourceId = sourceT.SourceId and targetT.StateId = sourceT.StateId and targetT.ValueT = sourceT.ValueT and targetT.AnalyseId = 2;

Hello,

I think you could have the result you expect using a fieldcompareset predicate and defining an alias to sourceT.

But with a fieldcompareset my subquery would be in the where clause. This works fine if I only have one condition which involves this subquery. My actual query looks a little more complicated than the one I posted before.

I have to compare at least 8 values, or even some. it may look more like that:

select sourceT.EntryId, targetT.EntryId, targetT1.ValueT, sourceT.ValueT1 from OBJECT_LIST targetT, (select * from OBJECT_LIST where Consumer = 359) sourceT where targetT.SourceId = sourceT.SourceId and targetT.StateId = sourceT.StateId and targetT.ValueT1 = sourceT.ValueT1 and targetT.ValueT2 = sourceT.ValueT2 and targetT.ValueT3 = sourceT.ValueT3 and targetT.ValueT4 = sourceT.ValueT4 and targetT.ValueT5 = sourceT.ValueT5 and targetT.ValueT6 = sourceT.ValueT6 and targetT.AnalyseId = 2;

Not I would have to use the fieldcompareset for each line in the where clause. And this would give me a wrong result. This is the reason I move it to the from clause so I can reference it in the where clause.

Is consumer a field of OBJECT_LIST? In that case, why don't use a request only using aliases like that :

select sourceT.EntryId, targetT.EntryId, targetT1.ValueT, sourceT.ValueT1 from OBJECT_LIST targetT,OBJECT_LIST sourceT where sourceT.Consumer = 359 and targetT.SourceId = sourceT.SourceId and targetT.StateId = sourceT.StateId and targetT.ValueT1 = sourceT.ValueT1 and targetT.ValueT2 = sourceT.ValueT2 and targetT.ValueT3 = sourceT.ValueT3 and targetT.ValueT4 = sourceT.ValueT4 and targetT.ValueT5 = sourceT.ValueT5 and targetT.ValueT6 = sourceT.ValueT6 and targetT.AnalyseId = 2;

It's the same no and easyier to code?

Yes you are right that query will work fine too.

I wanted to use the sub query in the from clause to improve the performance. My OBJECT_LIST table may contain millions of record but only a few a certain Consumer Id.

If I can not do it as a subquery in the from clause then I am going to do it like you suggested. Thanks for your help.