Hello

How do I define the ISNULL in a WHERE clause? Below is an example of what I am trying to achieve. I am using an adapter. I need to somehow specify an ISNULL in my predicate filter within the RelationPredicateBucket.

Regards, Simon

SELECT
  *
FROM
  Table1
WHERE
  ISNULL(Table1.Name, '%') LIKE 'X Value'

Dim bucket as new RelationPredicateBucket bucket.predicateexpression.add(Customerfields.customerName = dbnull.value)

This should do it....

Kind regards, Danny

DvK wrote:

Dim bucket as new RelationPredicateBucket bucket.predicateexpression.add(Customerfields.customerName = dbnull.value)

This should do it....

Kind regards, Danny

Hello Danny

Won't this check all values which are NULL? I need to wrap a ISNULL around the field.

Regards, Simon

You can use the DbFunctionCall expression for this. Check the "Using the generated code -> Calling a database function" in the manual.

OK, sorry...I misunderstood, you're using this as a replacement function. But can the DbFunctionCall handle the second parameter of the ISNULL function ? E.g. the MONTH function works perfectly because it only takes one parameter....or am I wrong ?

gtrz, Danny

Not at the moment, DbFunctioncall can only emit calls to functions which accept parameters, not constants or statement fragments. This means that you have to create your own DbFunctionCall class (pick the sourcecode of DbFunctionCall and alter it a bit and add it to your own project). We've planned to make this possible in v2.1

(edit) I just tried ISNULL with a parameter, and it works. So you can create a DbFUnctionCall with as first parameter the field to test and as second parameter a string '%', and that should result in a proper query.

Hello Otis

Do you have a sample which you could show me how to add it properly to the RelationBucket?

Regards, Simon

You should use a FiledLikePredicate since you are doing a Like predicate. Then you should set the expression of the field used in the FiledLikePredicate to the DBFunctionCall return.

It should look like the following untested example:

EntityField myField = CustomerFields.CompanyName;
myField.ExpressionToApply = new new DbFunctionCall(...);
// C#
bucket.PredicateExpression.Add(new FieldLikePredicate(myField, null, "AnyString"));
// Which is equal to:
bucket.PredicateExpression.Add(myField % "AnyString");

Thanks it worked likea charm!