This can be easily done using JDBC connector in Cloud Application Integration.
1. Create a JDBC connector on the lookup source table. Save and publish
2. Create a process and define a temp field of type drawn from the JDBC connection.
3. Use the assignment step to assign value to this variable. Since this is a variable of row type from a table (through JDBC), you get to see a query option along with field and formula.
4. Put the right query in the text box.
Hope this helps.
Informatica Cloud Product Management
Thank you for your help. We were able use lookup as query after creating a JDBC connection in Application Integration.