Aggregate With or Without Problem

Aggregate With or Without Problem

  

I have a problem with an Aggregate whenever I joined 2 tables. But first I'll explain what Tables that I use.

There are 2 tables that I used. In both of those tables, Information.ReviewerDivId and Workflow.ApproverDivId have a same data type, which is Division Identifier.


In Information table, There's a row with PurchaseRequestId = 64


And those are the aggregate with its filters and test values before I join them. In Workflow table, it has 5 rows that have workflowTypeId = 9. So far there's no error at all, but


Whenever I using LEFT JOIN(With or Without) in Workflow table and Information Table, on their ApproverDivId = ReviewerDivId, It only shows 3 rows with workflow level 1, 3, and 5. Shouldn't it show 5 rows instead of 3? From what I understand, if we are using With or Without in our JOIN, It will show all of the data in our left side of join table even their condition doesn't match. Can someone help me on this? Thank you so much.

Update :

I think I know whats wrong. This is the aggregate without PurchaseRequestId Filter

My filter was taking Information.PurchaseRequestId = PurchaseRequestId Input parameter or Information.PurchaseRequestId = NullIdentifier(). It took only the one that has PRId 64 or 0. Now I'm still thinking about the solution. If someone could help me, I'd really appriciate it. Thanks

Solution

Hello Hentry,

If you want all the Workflow from some type, no matter if there is information associated, but if it exists it should be from some PurchaseRequestId, than you can't filter, or it will remove lines with the information that are not of the id you're looking for, as this id will be different, not null.

Instead, delete this filter and append the PurchaseRequestId comparison (without the null comparison)  to the join condition with an AND, like this:

workflow....id=information....id AND information.PurchaseRequestId=PurchaseRequestId

This will have the desired effect.

Cheers.


Solution

Hi Eduardo,

Thank you so much! I tried what you suggest and it works perfectly. Really appreciate your help sir!