Flexible search expressions not working after JOIN

[former_member767841]

Hello, we have this working flexible search query:

 select {pk} from {DealerDescriptionProduct as dp} inner join ({{
 select {code} as icode, max({creationtime}) as maxtime
 from {DealerDescriptionProduct} WHERE  
     {creationtime}>='2010-01-01' 
 AND      {dealerStatus}='8796131983451' 
 group by {code}}}) x 
 on x.icode=code and x.maxtime=createdTS

Look at the last line of the query. We would like to use the alias 'dp' in order to access the createdTS field using the flexible search expression:

 x.maxtime={dp.createdtime}

But here we get the message

type code 'dp.creationtime' invalid

What are we doing wrong ?

Thank you in advance!

[Former Member · Accepted solution]

Hello Sven,

Instead of

 select {pk} from {DealerDescriptionProduct as dp} 

you can write

 ({{ select {pk}, {createdtime} as createdtime from {DealerDescriptionProduct} }}) dp 

And then dp.createdtime (without brackets) should be accessible outside.
As a whole this should look like this

 select * from
  ({{
  select {pk}, {createdtime} from {DealerDescriptionProduct}
   }}) dp
  inner join 
  ({{
   //inner query
 }}) x
  on x.icode = code and x.maxtime = dp.createdtime

Regards, Pawel