Hello

I am using LLBLGen PRO v3 with Oracle DB.

I want to select form a table all fields that are in a range of fields, but I want them to be caseinsensitive.

This is what I want to do:

RelationPredicateBucket filter = new RelationPredicateBucket();

filter.PredicateExpression.Add(PredicateFactory.CompareRange (SBCG_RESFieldIndex.RES_EXT, new string[] {"ICO", "BMP", "JPG", "PNG", "GIF"}));

In my database, it is possible that some of the fields are written with lower case. I know that for SQLServer DB this is not a problem, but with Oracle DB, if I have for example the value gif instead of GIF, he will not be taken into consideration,

Could you please advise how I can select all the fields even if they are stored in the DB as lowercase or upper case?

Thank You Juli

You have to use the Upper() Oracle function on the specified field. Translating this to LLBLGen: You should use an expression of a DBFunctionCall (which calls Upper() Oracle function), set that expression to the field used in the Predicate.

e.g.

IExpression exp = new DbFunctionCall("UPPER({0})", new object[] { SBCG_RESFields.RES_EXT });

filter.PredicateExpression.Add(SBCG_RESFields.RES_EXT.SetExpression(exp), new string[] {"ICO", "BMP", "JPG", "PNG", "GIF"});    

Thank you for the example

How ever, the statement produces the following error:

filter.PredicateExpression.Add(SBCG_RESFields.RES_EXT.SetExpression(exp), new string[] { "ICO", "BMP", "JPG", "PNG", "GIF" });

Error 241 No overload for method 'Add' takes '2' arguments

I have tryied other ways, but couldn't find the right way.

Also, is it possible to use user functions too in the DbFunctionCall? For example, I have my user function in Oracle DB, called F_DENOM, who elimines all special carateres from the values in a column of type Varchar2?

Something like this: IExpression exp = new DbFunctionCall("F_DENOM({0})", new object[] { TABLEFields.COLUMN});

Sorry that was my fault:

The correct code should be:

IExpression exp = new DbFunctionCall("UPPER({0})", new object[] { SBCG_RESFields.RES_EXT });

filter.PredicateExpression.Add(new FieldCompareRangePredicate(SBCG_RESFields.RES_EXT.SetExpression(exp), new string[] {"ICO", "BMP", "JPG", "PNG", "GIF"}));

Or

IExpression exp = new DbFunctionCall("UPPER({0})", new object[] { SBCG_RESFields.RES_EXT });

filter.PredicateExpression.Add(SBCG_RESFields.RES_EXT.SetExpression(exp) ==  new string[] {"ICO", "BMP", "JPG", "PNG", "GIF"});

And sure you an use any Database finction, please check: Calling a database function

Great. Thank you