SELECT COUNT(*) AS Expr1, u2.UserName, u2.UserId FROM Rating AS r1 INNER JOIN [User] AS u1 ON u1.UserId = r1.RatingUserID INNER JOIN Movie AS m1 ON m1.MovieID = r1.RatingMovieID INNER JOIN Rating AS r2 ON r2.RatingMovieID = m1.MovieID INNER JOIN [User] AS u2 ON u2.UserId = r2.RatingUserID AND u1.UserName = 'Carlton' AND r2.RatingUserID <> u1.UserId AND ABS(r2.Rating - r1.Rating) < 3 GROUP BY u2.UserName, u2.UserId ORDER BY Expr1 DESC

Dear Sirs,

The above SQL 2005 utilizes the ABS function. I would like to incorporate this in a predicate expression. I've searched the help file and your site without any hits. Just wondering. Thank you.

Carl

Hi Carl. You can achieve that via DBFunctionCalls. Please read LLBLGenPro Help - Using generated code - Calling a database function.

A sample below. I want all the categories where ABS(categoryID) > 3..

// define fields
ResultsetFields fields = new ResultsetFields(2);
fields.DefineField(MyCategoryFields.CategoryId, 0);
fields.DefineField(MyCategoryFields.Name, 1);


fields[0].ExpressionToApply = new DbFunctionCall("ABS", new object[] { MyCategoryFields.CategoryId });

// relations
IRelationPredicateBucket bucket = new RelationPredicateBucket();
bucket.PredicateExpression.Add(new FieldCompareValuePredicate(fields[0], null, ComparisonOperator.GreaterThan, 3));

// fetch results
DataTable results = new DataTable();
using (DataAccessAdapter adapter = new DataAccessAdapter())
{
    adapter.FetchTypedList(fields, results, bucket);
}

Thank you David...much appreciated. I will read what you've recommended. Carl

One little wrinkle was that the parameter to the object int he DBFunctionCall method consisted of an already establised expression. After some permutations my solution was to create a new EntityField2 that held the expression and then use this as the parameter and everything worked just fine.

ResultsetFields fields = new ResultsetFields(2); fields.DefineField(MovieFields.Title, 0, "Title", "Rating1"); fields.DefineField(RatingFields.Rating, 1); IExpression leftOperand = new Expression(RatingFields.Rating.SetObjectAlias("Movie1"), ExOp.Sub, RatingFields.Rating);

        EntityField2 tmpAbs = new EntityField2("ABS2",leftOperand);  // <-- create this did it

        fields[1].AggregateFunctionToApply = AggregateFunction.Count;
        fields[1].ExpressionToApply = new DbFunctionCall("ABS", new object[] { tmpAbs });) ;

Anyway, thanks again David.