I need to create a llblgen method for this sql statement. Thanks in advance

SELECT r.ProductId, prev.prevq, curr.currq FROM GroupStoreOrderRequest r, ( SELECT ProductId, Sum(Quantity) as prevq FROM GroupStoreOrderRequest WHERE DateCreated >= '2010/03/01 12:00:00 AM' AND DateCreated <= '2010/03/31 11:59:59 PM' GROUP BY ProductId ) prev, ( SELECT ProductId, Sum(Quantity) as currq FROM GroupStoreOrderRequest WHERE DateCreated >= '2010/04/01 12:00:00 AM' AND DateCreated <= '2010/04/30 11:59:59 PM' GROUP BY ProductId ) curr WHERE r.productId = prev.ProductId and r.productId = curr.ProductId Group by r.productid, prev.prevq, curr.currq

You should use Derived Tables and Dynamic Relations

Walaa wrote:

You should use Derived Tables and Dynamic Relations

Thanks. That is helpful.

I am a newbie so if someone could put together something in llblgen for me i will be very gratefull.

I suggest you try putting something together and consult us if it doesn't work, that's better for learning.

Thanks Walaa, the DerivedTable approach worked.

I can get order request data for the current month and the previous month.

I then try to add additional related entities to filter on and get the following error:

Error: Relation at index 2 doesn't contain an entity already added to the FROM clause. Bad alias?

Something is amiss with my relation setup...

How can I relate GroupStoreOrderEntity and ProductEntity to the derived tables which are constructed using the GroupStoreOrderRequestEntity?

Code: // bucket RelationPredicateBucket bucket = new RelationPredicateBucket();

                //  relations
                bucket.Relations.Add(GroupStoreOrderRequestEntity.Relations.GroupStoreOrderEntityUsingGroupStoreOrderId);
                bucket.Relations.Add(GroupStoreOrderRequestEntity.Relations.ProductEntityUsingProductId);

                //  filters
                bucket.PredicateExpression.Add(GroupStoreOrderFields.IsActive == true);
                if (pGroupStoreId != Guid.Empty) //optional
                    bucket.PredicateExpression.Add(GroupStoreOrderFields.GroupStoreId == pGroupStoreId);

                //  sort
                SortExpression sorter = new SortExpression(
                    ProductFields.Metric | SortOperator.Ascending);

region DERIVED TABLES (for 2 nested SQL statements)

                // SELECT Elements of derived tables
                ResultsetFields dtFields = new ResultsetFields(2);
                dtFields.DefineField(GroupStoreOrderRequestFields.ProductId, 0);
                dtFields.DefineField(new EntityField2("Total",
                             (GroupStoreOrderRequestFields.Quantity*1), AggregateFunction.Sum), 1);

                //FILTER of derived tables
                DateTime firstDayPrevMonth = DateHelper.GetStartOfDay(DateHelper.GetFirstDayOfLastMonth(DateTime.Now));
                DateTime lastDayPrevMonth = DateHelper.GetEndOfDay(DateHelper.GetEndOfLastMonth(DateTime.Now));
                DateTime firstDayThisMonth = DateHelper.GetStartOfDay(DateHelper.GetFirstDayOfMonth(DateTime.Now));

                IPredicateExpression predCurr = new PredicateExpression();
                predCurr.Add(GroupStoreOrderRequestFields.DateCreated >= firstDayThisMonth);
                predCurr.AddWithAnd(GroupStoreOrderRequestFields.DateCreated <= DateTime.Now);

                IPredicateExpression predPrev = new PredicateExpression();
                predPrev.Add(GroupStoreOrderRequestFields.DateCreated >= firstDayPrevMonth);
                predPrev.AddWithAnd(GroupStoreOrderRequestFields.DateCreated <= lastDayPrevMonth);

                // Elements in GROUP BY of derived tables
                GroupByCollection dtGroupBy = new GroupByCollection(dtFields[0]); //productid

                // Create derived tables specs + FILTERs + aliases
                DerivedTableDefinition dtDefinitionCurrent = 
                    new DerivedTableDefinition(dtFields, "Current", predCurr, dtGroupBy);

                DerivedTableDefinition dtDefinitionPrevious =
                    new DerivedTableDefinition(dtFields, "Previous", predPrev, dtGroupBy);

                // Set Dynamic relation with outside query (Select r.* from GroupStoreOrderRequest r)
                // Params= Derivedtable spec, join type, end entity type, alias first element, alias end element, on clause filter
                DynamicRelation relationCurrent = new DynamicRelation(dtDefinitionCurrent, JoinHint.Inner,
                                    EntityType.GroupStoreOrderRequestEntity, "r",
                                   (new EntityField2(GroupStoreOrderRequestFieldIndex.ProductId.ToString(), 
                                       "Current", 
                                       typeof(Guid)) == GroupStoreOrderRequestFields.ProductId.SetObjectAlias("r")));

                DynamicRelation relationPrevious = new DynamicRelation(dtDefinitionPrevious, JoinHint.Inner,
                                    EntityType.GroupStoreOrderRequestEntity, "r",
                                   (new EntityField2(GroupStoreOrderRequestFieldIndex.ProductId.ToString(),
                                       "Previous",
                                       typeof(Guid)) == GroupStoreOrderRequestFields.ProductId.SetObjectAlias("r")));


                //relate derived table relations to outside query (GroupStoreOrderRequestEntity)
                bucket.Relations.Add(relationCurrent);
                bucket.Relations.Add(relationPrevious);

                bucket.SelectListAlias = "r";

endregion

                //  fields
                ResultsetFields fields = new ResultsetFields(5);
                fields.DefineField(GroupStoreOrderFields.GroupStoreId, 0);
                fields.DefineField(GroupStoreOrderRequestFields.ProductId, 1);
                fields.DefineField(ProductFields.Name, 2);
                fields.DefineField(new EntityField2("Total", "Current", typeof(Double)), 3, "CurrentMonth");
                fields.DefineField(new EntityField2("Total", "Previous", typeof(Double)), 4, "PreviousMonth");

                //  fetch
                adapter.FetchTypedList(fields, dataTable, bucket, 0, sorter, false, null);

We appreciate your support.

In your sql above, where you put the extra predicate with different entity? What is the predicate expression you are adding and causing the error? What is the generated sql for that piece of code? Anyway, if you want to add extra relations inside a table definition, you can pass a RelationCollection to the DynamicTableDefinition constructor.