I guess this is a general database question, but I hope someone can answer it as it's more of a design question.

Is it better for complex cases to just get a List of results and then filter them out using Java instead of trying to query it out exactly out of the database?

I have a complex case where I need to analyze several variables, all with varying values, to determine if it should be in the list or not. I'm not saying getting rid of all the where clauses - I don't want all the rows. I'm just saying that I may get back a 100 or so rows, and with code, I will probably reduce it to 50 (or maybe not if all the results follow the complex condition).

When I say complex logic, I mean something that could take several nested conditions where it reflects on 3 or more variables to determine a state.

Which leads me to my next question: should I just store the calculated state to the database and re-calculate it whenever one of the important variables change? This would lead to a nice query where I can go "state = 'MYSTATE'" in the where clause.

I guess the problem with the above is that the state isn't part of the domain model. It's not something the business really deals in, but ironically I need to calculate it anyway for this problem. It's really a problem where the people who designed how the business worked didn't exactly create good business rules, so I have to analyze a gazillion different things to determine the state. I usually need to query based on this state, but because of how complex the logic is, the queries I'm trying out are returning duplicates and aren't working as I had hoped. It's just not something SQL is good for I don't think.

I'd like for you to give your thoughts on this. Thank you.