You can write any SQL query you want as long as it returns a single column of value. Lists can't have more than one column. I always test the query out in SSMS and then copy/paste it over to the Admin tool list setup.
To answer your question, I see no reason why you can't have a complex Where clause as long as the Select statement has a single value (that could be concatenated).
Thank you, Steve.
I should have mentioned that I need to query a different database on a different server and a data card variable is not static - I'd like a user to enter a value for the data card variable first and then pass the variable value to the query.
Found now a similar thread here Using a Card Variable in a Card list Query but was hoping I could do it without an add-in.