LLBLGEN Version 2.0.0.0 Final Released on 3/21/07

Hi,

Our database has the following tables and relationships: Batch 1:m Bill [Batch.Id == Bill.BatchId] Bill m:1 BillError 1:n Error [Bill.Id == BillError.BillId; Error.Code == BillError.Code]

ISSUE: I want to make a query to find "how many how many bills that has error per a particular batch". The SQL statement and the Llblgen code segment are listed below.

PROBLEM: The SQL statement returns a correct number of bill-error but the llblgen code segment does not. It seemed to returns all Bill-Error instead of the number of bill-error for a particular batch.

The SQL statement is: SELECT COUNT(distinct Claim_Bill_Error.Claim_Bill_Id ) FROM Claim_Bill_Error INNER JOIN Claim_Bill ON Claim_Bill_Error.Claim_Bill_Id = Claim_Bill.Id INNER JOIN Batch on Batch.Id = Claim_Bill.Batch_Id WHERE Batch.BatchNumber = '002676'

The Llblgen statements are: IRelationCollection relations = new RelationCollection(); relations.Add(BatchEntity.Relations.ClaimBillEntityUsingBatchId); relations.Add(ClaimBillEntity.Relations.ClaimBillErrorEntityUsingClaimBillId); relations.ObeyWeakRelations = true; ScalarQueryExpression sqe = new ScalarQueryExpression(ClaimBillErrorFields.ClaimBillId.SetAggregateFunction(AggregateFunction.CountDistinct), BatchFields.Id == ClaimBillFields.BatchId & ClaimBillFields.Id == ClaimBillErrorFields.ClaimBillId, relations );

Any help is greatly appreciated.

Thank you,

BZ

Can you post the LLBLGenPro SQL Generated code? (See LLBLGenPro Help - Using generated code - Troubleshooting and debugging).

Why are you doing **relations.ObeyWeakRelations = true **? Copied from LLBLGenPro Help - Using generated code - Adapter - Advanced filter usage:

LLBLGenPro wrote:

When ObeyWeakRelations is set to true for a typed list with these three entities (and which are using these relations), the entity Product is joined using LEFT JOIN, even though the relation is strong. This is because the OrderDetails entity is joined using LEFT JOIN, and will probably contain NULL values because of that.

Daelmo,

Can you post the LLBLGenPro SQL Generated code? What generated code? This code is handwritten by myself.

Why are you doing relations.ObeyWeakRelations = true ? Oh I just happened to have it there to try to see if it makes any differences. It turned out that it did not make any differences so having it there or not does not matter.

Here's the code snippet that I have:

1. THIS WORKS (returns a correct number of bills belong to a batch)

fields.DefineField(new EntityField2("NumberOfBills", new scalarQueryExpression(ClaimBillFields.Id.SetAggregateFunction(AggregateFunction.Count), (BatchFields.Id == ClaimBillFields.BatchId_))), 1);

1. THIS DOESN'T WORK (returns all BillError instead of BillError belong to a batch)

IRelationCollection relations = new RelationCollection(); relations.Add(BatchEntity.Relations.ClaimBillEntityUsingBatchId); relations.Add(ClaimBillEntity.Relations.ClaimBillErrorEntityUsingClaimBillId); relations.ObeyWeakRelations = true; fields.DefineField(new EntityField2("NumberOfBillsWithError",new ScalarQueryExpression (ClaimBillErrorFields.ClaimBillId.SetAggregateFunction(AggregateFunction.CountDistinct), BatchFields.Id == ClaimBillFields.BatchId & ClaimBillFields.Id == ClaimBillErrorFields.ClaimBillId, relations ) ), 2);

Thanks.

PROBLEM: The SQL statement returns a correct number of bill-error but the llblgen code segment does not. It seemed to returns all Bill-Error instead of the number of bill-error for a particular batch.

The SQL statement is:

SELECT COUNT(distinct Claim_Bill_Error.Claim_Bill_Id ) FROM Claim_Bill_Error
INNER JOIN Claim_Bill ON Claim_Bill_Error.Claim_Bill_Id = Claim_Bill.Id
INNER JOIN Batch on Batch.Id = Claim_Bill.Batch_Id
WHERE Batch.BatchNumber = '002676'

For above query (you only want the result of one Batch, so the number of results will be one) you simply should use:

PredicateExpression filter = new PredicateExpression(BatchFields.BatchId == "002676");
IRelationCollection relations = new RelationCollection();
relations.Add(BatchEntity.Relations.ClaimBillEntityUsingBatchId);
relations.Add(ClaimBillEntity.Relations.ClaimBillErrorEntityUsingClaimBillId);

int numberOfErros = 0;

using (DataAccessAdapter adapter = new DataAccessAdapter())
{               
    numberOfErros = (int) adapter.GetScalar(ClaimBillErrorFields.ClaimBillId, null, AggregateFunction.Count, filter, null, relations);
}

If you want the NumberOfErrors of the total collection of batch, your query would look like:

SELECT   Batch.BatchId, COUNT(Claim_Bill_Error.Claim_Bill_Id) 

FROM Claim_Bill_Error INNER JOIN Claim_Bill ON Claim_Bill_Error.Claim_Bill_Id = Claim_Bill.Id
    INNER JOIN Batch on Batch.Id = Claim_Bill.Batch_Id

GROUP BY Batch.BatchId

This in LLBLGenPro should look like (you don't need scalarQueryExpression in this case):

ResultsetFields fields = new ResultsetFields(2);

fields.DefineField(BatchFields.BatchId, 0);
fields.DefineField(ClaimBillErrorFields.ClaimBillId, 1, AggregateFunction.Count);

GroupByCollection groupBy = new GroupByCollection();
groupBy.Add(fields[0]);

IRelationPredicateBucket filter = new RelationPredicateBucket();    
filter.relations.Add(BatchEntity.Relations.ClaimBillEntityUsingBatchId);
filter.relations.Add(ClaimBillEntity.Relations.ClaimBillErrorEntityUsingClaimBillId);   

DataTable resulset = new DataTable();
using (DataAccessAdapter adapter = new DataAccessAdapter())
{               
    adapter.FetchTypedList(fields, resulset, filter, 0, null, false, groupBy);
}

bunzee wrote:

Can you post the LLBLGenPro SQL Generated code? What generated code? This code is handwritten by myself.

I was talking about LLBLGenPro tracing, sorry if that wasn't obvious (See LLBLGenPro Help - Using generated code - Troubleshooting and debugging)

I hope this was helpful.

Hm,

Can I actually use "(int) adapter.GetScalar(ClaimBillErrorFields.ClaimBillId, null, AggregateFunction.Count, filter, null, relations)" in "fields.DefineField"?

In other words, does this statement going to work?: fields.DefineField(new EntityField2("NumberOfBillsWithError", (int) adapter.GetScalar(ClaimBillErrorFields.ClaimBillId, null, AggregateFunction.Count, filter, null, relations)), 2);

Thanks

bunzee wrote:

Hm,

Can I actually use "(int) adapter.GetScalar(ClaimBillErrorFields.ClaimBillId, null, AggregateFunction.Count, filter, null, relations)" in "fields.DefineField"?

In other words, does this statement going to work?: fields.DefineField(new EntityField2("NumberOfBillsWithError", (int) adapter.GetScalar(ClaimBillErrorFields.ClaimBillId, null, AggregateFunction.Count, filter, null, relations)), 2);

Thanks

No you can't do that. DataAccessAdapter.GetScalar can't be called in that way. I you want to include a scalar expression in a DefineField method, use the second approach I gave to you:

ResultsetFields fields = new ResultsetFields(2);

fields.DefineField(BatchFields.BatchId, 0);
fields.DefineField(ClaimBillErrorFields.ClaimBillId, 1, AggregateFunction.Count);

GroupByCollection groupBy = new GroupByCollection();
groupBy.Add(fields[0]);

IRelationPredicateBucket filter = new RelationPredicateBucket();    
filter.relations.Add(BatchEntity.Relations.ClaimBillEntityUsingBatchId);
filter.relations.Add(ClaimBillEntity.Relations.ClaimBillErrorEntityUsingClaimBillId);   

DataTable resulset = new DataTable();
using (DataAccessAdapter adapter = new DataAccessAdapter())
{               
    adapter.FetchTypedList(fields, resulset, filter, 0, null, false, groupBy);
}