Stuck on getting correct results

Posts   
 
    
arschr
User
Posts: 893
Joined: 14-Dec-2003
# Posted on: 29-Dec-2005 22:40:17   

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.

Paul.Lewis
User
Posts: 147
Joined: 22-Aug-2005
# Posted on: 30-Dec-2005 04:38:45   

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.

Walaa avatar
Walaa
Support Team
Posts: 14946
Joined: 21-Aug-2005
# Posted on: 30-Dec-2005 07:02:48   

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.

Database Views are a magical solution that I always prefer. simple_smile

arschr
User
Posts: 893
Joined: 14-Dec-2003
# Posted on: 30-Dec-2005 12:33:37   

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?

arschr
User
Posts: 893
Joined: 14-Dec-2003
# Posted on: 30-Dec-2005 12:49:52   

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.

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 30-Dec-2005 13:04:47   

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.

Frans Bouma | Lead developer LLBLGen Pro
arschr
User
Posts: 893
Joined: 14-Dec-2003
# Posted on: 30-Dec-2005 17:00:32   

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);


Paul.Lewis
User
Posts: 147
Joined: 22-Aug-2005
# Posted on: 31-Dec-2005 04:19:10   

I'm glad you got your solution.

Happy New Year's to you as well!