Hi there, I'm stuck on one aspect of using a ScalarQueryExpression.

The desired SQL is as follows:

SELECT DISTINCT 
  DD.DebtorDetailsId AS Debtor,
  (SELECT COUNT(DISTINCT DDI.DebtorDetailsId)
    FROM DebtorDetails DDI
    WHERE DDI.VendorID <> @VendorId
    AND DDI.DebtorGroupId = DD.DebtorGroupId) AS OtherDebtorsInGroup
FROM DebtorDetails DD
WHERE DD.VendorId = @VendorId
AND DD.DebtorGroupId IS NOT NULL

I've scratched up this code so far:

ResultsetFields dtFields = new ResultsetFields(2);
            dtFields.DefineField(DebtorDetailsFields.DebtorDetailsId, 0);
            dtFields.DefineField(new EntityField("OtherDebtorsInGroup",
                new ScalarQueryExpression(DebtorDetailsFields.DebtorDetailsId.SetAggregateFunction(AggregateFunction.Count),
                (DebtorDetailsFields.VendorId != VendorId))), 1);

The part I'm stuck on is how to implement the line AND DDI.DebtorGroupId = DD.DebtorGroupId, i.e. how to filter the ScalarQueryExpression using a value from the outer query (especially as it refers to the same table name).

How should I approach this please?

(Self Servicing, v2.6 Final C# .NET 2.0 SQL Server 2005)

Thanks

Try with SetObjetcAlias:

ResultsetFields dtFields = new ResultsetFields(2);
dtFields.DefineField(DebtorDetailsFields.DebtorDetailsId, 0);
dtFields.DefineField(new EntityField("OtherDebtorsInGroup",
     new ScalarQueryExpression(DebtorDetailsFields.DebtorDetailsId
          .SetAggregateFunction(AggregateFunction.Count).SetObjectAlias("DDI"),
          DebtorDetailsFields.VendorId.SetObjetcAlias("DDI") != VendorId
               && DebtorDetailsFields.DebtorGroupId == DebtorDetailsFields.DebtorGroupId.SetObjectAlias("DDI"))
     ), 1);

Here are other examples: http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=12966 http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=10507&StartAtMessage=0&#58566

Thanks David. I got it working with this code in the end:

            int VendorId = 37;

            PredicateExpression scalarFilter = new PredicateExpression();
            scalarFilter.Add(DebtorDetailsFields.VendorId.SetObjectAlias("DDI") != VendorId);
            scalarFilter.AddWithAnd(DebtorDetailsFields.DebtorGroupId.SetObjectAlias("DDI")
                     == DebtorDetailsFields.DebtorGroupId);

            ResultsetFields fields = new ResultsetFields(2);
            fields.DefineField(DebtorDetailsFields.DebtorDetailsId, 0);
            fields.DefineField(new EntityField("OtherDebtorsInGroup",
                 new ScalarQueryExpression(DebtorDetailsFields.DebtorDetailsId
                     .SetAggregateFunction(AggregateFunction.Count).SetObjectAlias("DDI"),
                     scalarFilter)
                 ), 1);

            PredicateExpression outerFilter = new PredicateExpression();
            outerFilter.AddWithAnd(DebtorDetailsFields.VendorId == 37);
            outerFilter.AddWithAnd(new FieldCompareNullPredicate(DebtorDetailsFields.DebtorGroupId, true));

            // fetch the data
            DataTable dt = new DataTable();
            TypedListDAO dao = new TypedListDAO();
            dao.GetMultiAsDataTable(fields, dt, 0, null, outerFilter, null, true, null, null, 0, 0);

(magic numbers only for test code!)

My next task is to wrap that lot in an outer query that does some grouping and summing.

As always, thanks for your time.

Nope, I'm still stuck

The full query I'm trying to implement is this:

SELECT DISTINCT A.OtherDebtorsInGroup, COUNT(A.Debtor) AS NumberOfDebtors
FROM
(
    SELECT 
        DISTINCT DD.DebtorDetailsId AS Debtor,
        (SELECT COUNT(DISTINCT DDI.DebtorDetailsId)
            FROM DebtorDetails DDI
            WHERE DDI.VendorID <> 37
            AND DDI.DebtorGroupId = DD.DebtorGroupId) AS OtherDebtorsInGroup
    FROM DebtorDetails DD
    WHERE DD.VendorId=37
    AND DD.DebtorGroupId IS NOT NULL
) AS A
GROUP BY A.OtherDebtorsInGroup
ORDER BY A.OtherDebtorsInGroup

The code I posted before will do the inner part of it (that gets the table called "A").

Problem is, in SQL Server I had to wrap the outer query around it because I'm grouping and sorting by the "OtherDebtorsInGroup" field. SQL Server won't allow me to use that field alias in a group by or order by clause directly, nor will it work if I use the field expression.

So in raw SQL I solved it by adding that outer query.

How could I do the equivalent with LLBLGen please? I explored the sample code in the DerivedTableDefinition help topic, but that uses a Collection class, whose GetMulti() method obviously doesn't support GROUP BY.

I need to be able to take the data I got into the datatable using the code I posted before, and apply grouping and sorting to it. I can do that using post-query C#/LINQ but I'm hoping there's a way to implement it as a SQL query within the LLBLGen space. I can't see it yet though.

Thanks again

I've to check if you could use a DerivedTableDefinition without a DynamicRelation (you don't have joins).

It's possible to do this. You have to create a DerivedTableDefinition for the 'A' table (which is a derived table). Then, you do the following: - Create a DynamicRelation by using the constructor which accepts a DerivedTableDefinition. You have specified as alias for the derived table definition 'A'. - Create a RelationCollection, and add the created dynamic relation to that relationcollection. - Create a ResultsetFields object add two fields to it, which both have their ObjectAlias set to "A". - Create a groupbycollection and SortExpression and both use the first field in the resultsetfields. - Pass it all to the dynamiclist fetch method to fetch the query. With Linq this is much easier (and doable all in the db), but I see you use .NET 2.0, so you've to use our native query system

I've posted below a tryout on Northwind which produces a similar query. (I didn't include the is null filter, but that's trivial)

// derived table elements. Define a derived query where all orderdetails rows with productid1 are fetched and the count of the other products
// in that query. 
int productId = 1;

PredicateExpression scalarFilter = new PredicateExpression();
scalarFilter.Add(OrderDetailFields.ProductId.SetObjectAlias("ODI") != productId);
scalarFilter.AddWithAnd(OrderDetailFields.OrderId.SetObjectAlias("ODI")
         == OrderDetailFields.OrderId);

ResultsetFields dtFields = new ResultsetFields(2);
dtFields.DefineField(OrderDetailFields.OrderId, 0);
dtFields.DefineField(new EntityField("OtherProductsInGroup",
     new ScalarQueryExpression(OrderDetailFields.OrderId
         .SetAggregateFunction(AggregateFunction.Count).SetObjectAlias("ODI"),
         scalarFilter)
     ), 1);

PredicateExpression dtFilter = new PredicateExpression();
dtFilter.AddWithAnd(OrderDetailFields.ProductId == productId);

DerivedTableDefinition dt = new DerivedTableDefinition(dtFields, "A", dtFilter);
dt.AllowDuplicates = false;

RelationCollection relations = new RelationCollection(new DynamicRelation(dt));
ResultsetFields fields = new ResultsetFields(2);
fields.DefineField(new EntityField("OtherProductsInGroup", "A", typeof(int)), 0);
fields.DefineField(new EntityField("OrderId", "A", typeof(int)).SetAggregateFunction(AggregateFunction.Count), 1);
GroupByCollection groupBy = new GroupByCollection(fields[0]);
SortExpression sorter = new SortExpression((EntityField)fields[0] | SortOperator.Ascending);
DataTable results = new DataTable();
TypedListDAO dao = new TypedListDAO();
dao.GetMultiAsDataTable(fields, results, 0, sorter, null, relations, false, groupBy, null, 0, 0);

SQL:

SELECT  DISTINCT [LPA_A1].[OtherProductsInGroup], COUNT([LPA_A1].[OrderId]) AS [OrderId] 
FROM 
(
    SELECT  DISTINCT [Northwind].[dbo].[Order Details].[OrderID] AS [OrderId], 
            (
                SELECT  COUNT([ODI].[OrderID]) AS [OrderId] 
                FROM    [Northwind].[dbo].[Order Details] [ODI]  
                WHERE ( [ODI].[ProductID] <> @ProductId1 
                        AND [ODI].[OrderID] = [Northwind].[dbo].[Order Details].[OrderID])
            ) AS [OtherProductsInGroup] 
    FROM    [Northwind].[dbo].[Order Details]  
    WHERE ( [Northwind].[dbo].[Order Details].[ProductID] = @ProductId2)
) [LPA_A1] 
GROUP BY [LPA_A1].[OtherProductsInGroup] 
ORDER BY [LPA_A1].[OtherProductsInGroup] ASC

David and Frans, thanks very much for all your help. I think it's time for me to get our system up to .net 3.5. LINQ makes some things so much easier. Short term I will use Frans' solution above. Thanks again

Good Let us know if you need further help. Cheers.