Join Outer does not work - why?

[jerichob]

In my report, I want to compare invoices with payments. All these info (all history) is stored in SQL table 'Debtors Operations'.

First I ran a simple report with only one table (no links though) where I simply list all invoices (using Select Expert to list invoices exclusively). It works fine.

Now I want to add payments next to invoices on the same level (Details section). I created an alias of the table 'Debtors Operations' and linked the original table to the alias using 'document number' field.

I put the fields from my alias table next to the fields of the original table (Details section). Using Select Expert I defined that the alias table will only show payments.

Now as a result I get invoices with the corresponding payments.

My problem is that the report does not show the invoices, which were not yet paid or partly-paid.

I tried to solve the problem by changing the link's options to 'Left Outer Join' to see all invoices, but the outcome of the report does not change at all.

What seems to be the problem? Maybe it's the problem with NULL fields, but I am not sure. Any suggestions, please?

 

[Naith]

I don't think you aren't seeing unpaid or part-paid invoices because of your join. You just said that in your Selection Criteria, you elected to only show payments from the alias.

If you want to see all invoice statuses inclusive of unpaid and part-paid, you shouldn't have this rule specified in the Select Expert.

Naith

 

[jerichob]

Thanks! You are absolutely right. Outer Join cannot work, because I am working on the table and its alias, so the same thing. If I limit the scope to payments only, automatically I get payments only in the main table. And so Outer Join will not change anything.

I changed Select Expert and it works. Thanks. Problem solved.