Hi,

I have assigned an expression to a field and given the field an alias like this...

fields.DefineField(JobFieldIndex.JobBatchId, 5, "KeywordRanking");
            
IExpression keywordExpression = new CaseLikeExpression(JobFields.JobTitle, "hello");

fields[5].ExpressionToApply = keywordExpression;

Now, how do I filter on the alias? I tried this but the error suggests the framework expects the alias to refer to a table.

bucket.PredicateExpression.Add(JobFields.JobBatchId.SetObjectAlias("KeywordRanking") > 0);

Cheers, Ian.

Just use the field in the dynamic list "fields[5]" in your filter instead of "JobFields.JobBatchId"

Thanks for your reply.

I did...

bucket.PredicateExpression.Add((EntityField2)fields[5] > 0);

..but the where clause then contained the entire expression instead of the alias name!

Its quite a complicated expression so I really don't want to repeat it.

You specified a field alias in the DefineField line, not a table alias. So you don't have to specify any alias in the line: bucket.PredicateExpression.Add(JobFields.JobBatchId.SetObjectAlias("KeywordRanking") > 0);

just do: bucket.PredicateExpression.Add(JobFields.JobBatchId > 0);

But that just puts 'WHERE JobBatchID > 0' into the query.

Here's the expression....

CASE WHEN [jwdata].[dbo].[tbl_Job].[JobTitle] LIKE ''%hello%'' THEN 10 ELSE 0 END AS [KeywordRanking]

So the fact that the expression is attached to JobBatchID is arbitrary but I'm under the impression that the expression needs to be assigned to an existing field so I just picked JobBatchID.

Ok, this isn't going anywhere.

Please state the query you want to have because I'm now lost what you want . "CASE" statements aren't supported btw.

(edit): Walaa's answer will give you the expression again but that's ok, it will evolve to the same value.

Its OK, the query I wanted isn't legal anyway. I think I can get a legal version to work though.

Just in case you're interested, I'm trying to do this....

http://sqlteam.com/forums/topic.asp?TOPIC_ID=64625

Although I'm wondering now how I'm going to get the alias for the calculated field into the ORDER BY list. Isn't this going to be the same problem as before?

Ian wrote:

Its OK, the query I wanted isn't legal anyway. I think I can get a legal version to work though.

Just in case you're interested, I'm trying to do this....

http://sqlteam.com/forums/topic.asp?TOPIC_ID=64625

I'll check it out.

Although I'm wondering now how I'm going to get the alias for the calculated field into the ORDER BY list. Isn't this going to be the same problem as before?

No that's fine. You just specify the SAME field as you have in the resultsetfields and the ORDER BY emitter logic will use the alias for that field, in your case "KeywordRanking"

Yes its working. Thanks.