I have two tables with a left outer join. Table 1 contains invoice line items and table 2 contains serial numbers for those items. Not all lines on the invoice have serial numbers, so there are some null records in the second table.

I cannot join the tables based on the order number because the field in the second table contains extra characters.

Example:

Table 1: 0118465
Table 2: 0118465 / 00

The only field that I can join the tables on is the part #. Because of this, I have to do a selection based on the order number to return only those serial numbers for that particular order. My selection formula:

{oep65.ordn65}=left({inp95.refn95},7)

When I include the selection, the invoice lines from table 1 with no data in table 2 are excluded. I think this means that it's converting this to an inner join, but I don' t know how to get around this.

Any ideas?



Edited by aeckaren - 02 Oct 2012 at 5:36am

I did try this but for some reason it's really bogging down the server (CR freezes up while trying to run).