I have a report which shows product in the first column, and sales in the second. How can I ensure that column 1 shows all products i.e. even those which have no Sales values. I thought using a outer join would do this but it doesn’t seem to be working that way???
Listserv Archives (BOB member since 2002-06-25)
An outer join WILL do what you describe. Are you sure you have it defined properly?
In Designer, when you define a join, say, between PRODUCT table and SALES table, you see two checkboxes labeled Outer Join, one for PRODUCT and one for SALES. In your case, you want to make sure the box assigned to PRODUCT is checked on. If you are using an Oracle database, this produces a WHERE clause like: PRODUCT.PRODUCT_ID = SALES.PRODUCT_ID(+). The “(+)” goes on the table which is optional in the outer join – I can never remember if that is considered the “inner” table or the “outer” table.
Now, an outer join can be “voided” (for lack of a better term) by other joins or conditions in your query. For example, if you joined SALES to a SALESPERSON table through an inner (non-outer) join, you’ll void your PRODUCT-SALES outer join. Or, if you have a condition like SALES.SALESDATE
‘1/1/1998’, again, you’ll void your outer join.
Erich Hurst
Compaq Computer Corporation
Listserv Archives (BOB member since 2002-06-25)
Erich,
Yes, I realised later that there was another join coming into play causing the ‘problem’ you describe. I have changed my report to use linked data providers - with a link between a query returning the complete product list, and one returning the sales results. Is there a better way to do this?
jl
Listserv Archives (BOB member since 2002-06-25)