Smarter filtering in prefilters using columns

Hi ​@Robin Liu,

I would say this is more of an SQL issue than a Thinkwise specific issue.

The reason you can’t use the alias in your WHERE clause is because of SQL’s execution order. The WHERE clause is evaluated before the SELECT list is processed, which means the alias you define in the SELECT statement doesn’t exist yet at the time the WHERE is applied.

This is a simplified example, showing this behavior:

I did not test this, but perhaps you could work around this by creating a function that you can call both in the SELECT (expression-part) and in the WHERE (prefilter-part) clause? In my example, the end result would look something like this:

Please let me know if this helps.

Hi ​@Jeroen van den Belt , thanks for the reply!
I understand that I cannot use the alias because of the execution order.
I could make a function that returns a bool, that would solve the problem of code duplication.

But I am still unsatisfied that then the function likely would be evaluated twice, so it would not improve the efficiency.
I discussed my problem with my friend Cas, and we came to the agreement that in my case the best solution at the moment would be to split the filter up into two prefilters and put them in an OR prefilter group, and set them boh to On by default. However, since we haven’t fully moved to the Unviersal GUI yet, I can’t rely on OR prefilter groups.

Once you’ve switched to the Universal GUI, it should even be possible to reference t1.[your_expression_column], as long as you use that t1 alias.

I don’t expect a solution for this to be implemented in the 2-tier Windows GUI anymore.