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 is it possible to generate it.

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

Using the FieldFullTextSearchPredicate

predExp.Add(new FieldFullTextSearchPredicate( table1Fields.Details, FullTextSearchOperator.Contains, 'abc'));

i was only able to get something like this

SELECT A.Id, A.Details FROM table1 A WHERE CONTAINS (a.Details, 'abc');

but unable to use the score operator.

LLBLgen pro V 3.5. Oracle DB

There is 2 parts into this. First the predicate then the Order clause.

For the predicate you need to use a normal FieldCompareValuePredicate ... (SomeField > 0) And for the field you need to use a DBFunctionCall as an expression.

Pseudo code:

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

var field = tabel1Fields.Details;
field.ExpressionToApply = containExression;

predicate.Add(field > 0);

For the second part you can try a DBFunctionCall to generate the Score part.

Something like:

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);

Thanks walaa for your reply

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;

I am getting an extra set of braces after this.

instead of IExpression scoreExp = new DbFunctionCall("Score(10)", new object[] {});

i used IExpression scoreExp = new DbFunctionCall("Score({0})", new object[] {10});

it worked.

Thanks a lot !!

I am receiving a error

ORA-29909: label for the ancillary operator is not a literal number.

Looks like i cant bind variables in score or contains operator

Try

IExpression scoreExp = new DbFunctionCall("Score(10)", null);

Followed here: http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=23210&StartAtMessage=0&#131518

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;

Please don't reopen the old thread, you've started 2 threads on the same topic, this one is closed, please continue with the new one.