I have a table something like this table1(int Id, varchar2() Name, varchar2() Details) I am trying to build a predicate expression for the following SQL.

SELECT A.Id, A.Details, score(10) FROM table1 A WHERE CONTAINS (a.Details, 'abc',10) > 0 order by score(10) desc;

SudoCode: IExpression containExression = new DbFunctionCall("Contains({0},'{1}', 10)", new object[] {tabel1Fields.Details, "abc"});

var field = tabel1Fields.Details; field.ExpressionToApply = containExression; predicate.Add(field > 0);

IExpression scoreExp = new DbFunctionCall("Score(10)", new object[] {}); var field2 = tabel1Fields.Details; field2.ExpressionToApply = scoreExp;

ISortClause scoreSortClause = new SortClause(field2, null, SortOperator.Descending); scoreSortClause.EmitAliasForExpressionAggregateField = false; ISortExpression sorter = new SortExpression(scoreSortClause);

Using this i was able to genrate query something like this I tried this my resultant query looks something like this

SELECT A.Id, A.Details, score(10) FROM table1 A WHERE CONTAINS (a.Details, 'abc',10) > 0 order by** score(10)()** desc; but there was an extra brace at the end of score which throws unimplemented error in oracle

I even tried something like this IExpression scoreExp = new DbFunctionCall("Score({0})", new object[] {10}); even this IExpression scoreExp = new DbFunctionCall("Score(10)", null); but ORA-29909: label for the ancillary operator is not a literal number. Think i cant bind variables in score or contains operator

Thanks for your help

What is the generated sql for the last try (the one that gices you the error)?

Its something like this. I am still getting a extra set of braces at the end of score. SELECT A.Id, A.Details, score(10) FROM table1 A WHERE CONTAINS (a.Details, 'abc',10) > 0 order by score(10)() desc;

You're using the latest v3.5 build?

Yes I am using latest build v3.5

Please provide the LLBLGen runtime library used build no.

Sadly, this isn't directly supported: either () are at the end of the function (which are added automatically) or the function uses values (or fields/expressions) which are passed to the function as parameters.

If you want to embed literals, you should use a special case for DbFunctionCall. I've specified the class below. It's the class we also use internally in the linq provider to provide literals as function calls.

public class ProjectionLiteral : DbFunctionCall
{
    /// <summary>
    /// Initializes a new instance of the <see cref="ProjectionLiteral"/> class.
    /// </summary>
    /// <param name="literalAsString">The literal as string.</param>
    public ProjectionLiteral(string literalAsString) : base(literalAsString, null)
    {
    }


    /// <summary>
    /// Retrieves a ready to use text representation of the contained function call
    /// </summary>
    /// <param name="inHavingClause">if set to true, it will allow aggregate functions to be applied to fields.</param>
    /// <returns>
    /// The contained function call in textual format.
    /// </returns>
    /// <exception cref="System.ApplicationException">When IDbFunctionCall.DatabaseSpecificCreator is not set to a valid value.</exception>
    public override string ToQueryText(bool inHavingClause)
    {
        return this.FunctionName;
    }
}

Instead of DbFunctionCall("score(10)", new object[] {}), simply do: ProjectionLiteral("score(10)");

You can give the class any name you like, the above one is called ProjectionLiteral as it's used in the projection, you should give it a name which matches your use case.

Hope this helps.