lookup_values.lookups_id = (SELECT looktups_id FROM lookups WHERE lookups.lookups_type = 'STATUS')You can do it using inline queries and bind variables etc.
I realize this is an old thread but I stumbled across a solution to this. It looks like in order to use an SQL statement in the value field of your view criteria you need to go in to the view object source and set IsSqlFragment="true" for that particular view criteria item. For instance if you wanted to use a view criteria where the salary was greater than the average salary then your view criteria item would look like
Value="(SELECT AVG(SALARY) AVG_SAL FROM EMPLOYEES)">