- Home
- LLBLGen Pro
- LLBLGen Pro Runtime Framework
Stuck on getting correct results
Joined: 14-Dec-2003
Sorry for the lengthy code.
I'm building a dynamic list, but am having trouble getting correct results, and haven't found a way in the help.
I want the equivelant of:
SELECT
[LPA_I4].[InvoiceID] AS [Invoice ID],
[Action Date],
[Milestone Date],
[Reaction Date]
FROM ((( [CABSoft_ForkliftDB].[dbo].[tCGM_BAN_Invoices] [LPA_I4]
INNER JOIN
(
Select InvoiceID, Max(ActivityDate) as [Action Date] from [CABSoft_ForkliftDB].[dbo].[tInvoiceActivity] [LPA_M3] where ActvityID = 8 group by InvoiceID
) [LPA_A2] ON [LPA_I4].[InvoiceID]=[LPA_A2].[InvoiceID])
LEFT JOIN
(
Select InvoiceID, Max(ActivityDate) as [Reaction Date] from [CABSoft_ForkliftDB].[dbo].[tInvoiceActivity] [LPA_M3] where ActvityID = 7 group by InvoiceID
) [LPA_M3] ON [LPA_I4].[InvoiceID]=[LPA_M3].[InvoiceID])
LEFT JOIN
(
Select InvoiceID, Max(ActivityDate) as [Milestone Date] from [CABSoft_ForkliftDB].[dbo].[tInvoiceActivity] [LPA_M3] where ActvityID = 13 group by InvoiceID
) [LPA_R1] ON [LPA_I4].[InvoiceID]=[LPA_R1].[InvoiceID])
but the closest i've gotten is:
SELECT
[LPA_I4].[InvoiceID] AS [Invoice ID],
MAX([LPA_A2].[ActivityDate]) AS [Action Date],
MAX([LPA_M3].[ActivityDate]) AS [Milestone Date],
MAX([LPA_R1].[ActivityDate]) AS [Reaction Date]
FROM ((( [CABSoft_ForkliftDB].[dbo].[tCGM_BAN_Invoices] [LPA_I4]
INNER JOIN [CABSoft_ForkliftDB].[dbo].[tInvoiceActivity] [LPA_A2]
ON [LPA_I4].[InvoiceID]=[LPA_A2].[InvoiceID])
LEFT JOIN [CABSoft_ForkliftDB].[dbo].[tInvoiceActivity] [LPA_M3]
ON [LPA_I4].[InvoiceID]=[LPA_M3].[InvoiceID])
LEFT JOIN [CABSoft_ForkliftDB].[dbo].[tInvoiceActivity] [LPA_R1]
ON [LPA_I4].[InvoiceID]=[LPA_R1].[InvoiceID])
WHERE (
( [LPA_A2].[ActivityDate] >= '2005-12-01' AND [LPA_A2].[ActvityID] = 8)
AND
( [LPA_R1].[ActvityID] = 7 OR [LPA_R1].[ActvityID] IS NULL)
AND
( [LPA_M3].[ActvityID] = 13 OR [LPA_M3].[ActvityID] IS NULL)
)
GROUP BY [LPA_I4].[InvoiceID]
by using this code:
DataTable QueueItems = new DataTable();
ResultsetFields fields = new ResultsetFields(15);
fields.DefineField(InvoiceFieldIndex.InvoiceId, 0, "Invoice ID", "Invoice");
fields.DefineField(InvoiceActivityFieldIndex.ActivityDate, 1, "Action Date", "ActionActivity", AggregateFunction.Max);
fields.DefineField(InvoiceActivityFieldIndex.ActivityDate, 2, "Milestone Date", "MilestoneActivity", AggregateFunction.Max);
fields.DefineField(InvoiceActivityFieldIndex.ActivityDate, 3, "Reaction Date", "ReactionActivity", AggregateFunction.Max);
fields.Contract();
IRelationPredicateBucket bucket = new RelationPredicateBucket();
bucket.Relations.Add(InvoiceEntity.Relations.InvoiceActivityEntityUsingInvoiceId, "Invoice", "ActionActivity", JoinHint.Inner);
bucket.Relations.Add(InvoiceEntity.Relations.InvoiceActivityEntityUsingInvoiceId, "Invoice", "MilestoneActivity", JoinHint.Left);
bucket.Relations.Add(InvoiceEntity.Relations.InvoiceActivityEntityUsingInvoiceId, "Invoice", "ReactionActivity", JoinHint.Left);
IPredicateExpression ActionFilter = new PredicateExpression();
ActionFilter.Add(PredicateFactory.CompareValue(InvoiceActivityFieldIndex.ActivityDate, ComparisonOperator.GreaterEqual, EarliestActivityDate, "ActionActivity"));
ActionFilter.AddWithAnd(PredicateFactory.CompareValue(InvoiceActivityFieldIndex.ActvityId, ComparisonOperator.Equal, ActionActivityID, "ActionActivity"));
IPredicateExpression ReactionFilter = new PredicateExpression();
ReactionFilter.Add(PredicateFactory.CompareValue(InvoiceActivityFieldIndex.ActvityId, ComparisonOperator.Equal, ReactionActivityID, "ReactionActivity"));
ReactionFilter.AddWithOr(PredicateFactory.CompareNull(InvoiceActivityFieldIndex.ActvityId, "ReactionActivity"));
IPredicateExpression MilestoneFilter = new PredicateExpression();
MilestoneFilter.Add(PredicateFactory.CompareValue(InvoiceActivityFieldIndex.ActvityId, ComparisonOperator.Equal, MilestoneActivityID, "MilestoneActivity"));
MilestoneFilter.AddWithOr(PredicateFactory.CompareNull(InvoiceActivityFieldIndex.ActvityId, "MilestoneActivity"));
bucket.PredicateExpression.Add(ActionFilter);
bucket.PredicateExpression.AddWithAnd(ReactionFilter);
bucket.PredicateExpression.AddWithAnd(MilestoneFilter);
IGroupByCollection groupByClause = new GroupByCollection();
groupByClause.Add(fields[0]);
The nut seems to be the ability to join on virtual tables. Any help?
Thanks.
Joined: 22-Aug-2005
arschr wrote:
The nut seems to be the ability to join on virtual tables. Any help?
Unfortunately, as you've discovered, virtual tables are not supported at this time. If you re-write your select statement using sub-queries you'll have better success.
Joined: 14-Dec-2003
Unfortunately, as you've discovered, virtual tables are not supported at this time.
What is the plan/schedule for supporting them?
If you re-write your select statement using sub-queries you'll have better success.
What do you mean by a sub-query in this case, compared to what I have done?
How do I combine the definition of a typed view/typed list (the subquery) via a join to the definition of another?
Joined: 14-Dec-2003
Or you may use Database Views to replace the virtual tables. Or implement the whole query in a database view then map it to a TypedView.
I'll try the view approach, but as I write this, it's not clear how it will work.
I have this logic in a strored procedure already, some of the hard coded values in my sample are really parameters.
Joined: 17-Aug-2003
arschr wrote:
Unfortunately, as you've discovered, virtual tables are not supported at this time.
What is the plan/schedule for supporting them?
Not defined yet. It's a cornercase, as often you can rewrite these queries to normal queries.
If you re-write your select statement using sub-queries you'll have better success.
What do you mean by a sub-query in this case, compared to what I have done?
How do I combine the definition of a typed view/typed list (the subquery) via a join to the definition of another?
You want to join to a select on another table. That's currently not supported and you can only do that by moving the select into a view or by joining to the table and move the fields selected to the main select clause.
Joined: 14-Dec-2003
O.K. I got it working, there were two things I had to do.
1). use views for the logic of the virtual tables 2) use the ability to add custom filters into the joins
As far as #1 goes, I don't think it is a corner case to want or need to build more complicated selects from simpler components.
Just like you allow building complicated where logic by building pieces into predicate expressions and combining them into more complicated predicate expressions. It would add greatly to your product if you could find a way to combine typed lists/typed views/dynamic lists together via relationships.
Anyway here is my code. Have a happy new year everyone.
ResultsetFields fields = new ResultsetFields(15);
fields.DefineField(InvoiceFieldIndex.InvoiceId, 0, "Invoice ID", "Invoice");
fields.DefineField(InvoiceActvityLatestByActivityIdFieldIndex.ActivityDate, 1, "Action Date", "ActionActivity");
fields.DefineField(InvoiceActvityLatestByActivityIdFieldIndex.ActivityDate, 2, "Milestone Date", "MilestoneActivity");
fields.DefineField(InvoiceActvityLatestByActivityIdFieldIndex.ActivityDate, 3, "Reaction Date", "ReactionActivity");
fields.Contract();
IRelationPredicateBucket bucket = new RelationPredicateBucket();
bucket.Relations.Add(InvoiceEntity.Relations.InvoiceActvityLatestByActivityIdEntityUsingInvoiceId, "Invoice", "ActionActivity", JoinHint.Inner);
IPredicateExpression customFilterMilestone = new PredicateExpression();
customFilterMilestone.Add(PredicateFactory.CompareValue(InvoiceActvityLatestByActivityIdFieldIndex.ActivityId, ComparisonOperator.Equal, MilestoneActivityID, "MilestoneActivity"));
customFilterMilestone.Add(PredicateFactory.CompareValue(InvoiceActvityLatestByActivityIdFieldIndex.ActivityDate, ComparisonOperator.GreaterEqual, EarliestActivityDate, "MilestoneActivity"));
bucket.Relations.Add(InvoiceEntity.Relations.InvoiceActvityLatestByActivityIdEntityUsingInvoiceId, "Invoice", "MilestoneActivity", JoinHint.Left).CustomFilter = customFilterMilestone;
IPredicateExpression customFilterReaction = new PredicateExpression();
customFilterReaction.Add(PredicateFactory.CompareValue(InvoiceActvityLatestByActivityIdFieldIndex.ActivityId, ComparisonOperator.Equal, ReactionActivityID, "ReactionActivity"));
customFilterReaction.Add(PredicateFactory.CompareValue(InvoiceActvityLatestByActivityIdFieldIndex.ActivityDate, ComparisonOperator.GreaterEqual, EarliestActivityDate, "ReactionActivity"));
bucket.Relations.Add(InvoiceEntity.Relations.InvoiceActvityLatestByActivityIdEntityUsingInvoiceId, "Invoice", "ReactionActivity", JoinHint.Left).CustomFilter = customFilterReaction;
IPredicateExpression ActionFilter = new PredicateExpression();
ActionFilter.Add(PredicateFactory.CompareValue(InvoiceActvityLatestByActivityIdFieldIndex.ActivityDate, ComparisonOperator.GreaterEqual, EarliestActivityDate, "ActionActivity"));
ActionFilter.AddWithAnd(PredicateFactory.CompareValue(InvoiceActvityLatestByActivityIdFieldIndex.ActivityId, ComparisonOperator.Equal, ActionActivityID, "ActionActivity"));
bucket.PredicateExpression.Add(ActionFilter);
Joined: 22-Aug-2005