I'm running a sql scalar and using subqueries to pull some counts... is there as way to specify a IsNull or a where clause in the subquery.. eg sql

select customerid, (select isnull(count(orderid),0) from orders where customer.customerid = orders.customerid where order active =1) as ordercount

I modeled my code out of the help and it works fine.. was just wondering about where in the subquery and the isnull

Hi KenP,

For the subqueries you should use Scalar Query Expressions. And for the ISNULL function, you could use the DBFunctionCall object.

That could look something like this:

// the fields
ResultsetFields fields = new ResultsetFields(2);
fields.DefineField(CustomersFields.CustomerId, 0, "IdCustomer");
fields.DefineField(CustomersFields.OrderId, 0, "IdCustomer");

// the isNull db function
IExpression isNullFunctionExp = new DbFunctionCall("ISNULL,
     new object[] {OrderFields.OrderId.SetAggregateFunction(AggregateFunction.Count),
     0});

// the subquery
IExpression subQueryExp= new ScalarQueryExpression(
    OrderFields.OrderId.SetExpression(isNullFunctionExp ),
     CustomerFields.CustomerId == OrderFields.OrderId);

fields[1].ExpressionToApply = subQueryExp;

// other stuff (order, filter, etc.)....

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