• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 553
  • Last Modified:

Multiple outer joins

I'm trying to create a spreadsheet that displays purchase order details and corresponding invoice details where they exist. I'm using MS Excel 2003 and MS Query to access Sage Line 100 via ODBC.

Basically, there are two sets of master and detail tables: order_header and order_detail; and invoice_header and invoice_detail.

I can easily display all the order_detail and coresponding invoice_detail where it exists -- a simple outer join -- but there are several column I need from the header tables.

I've got this partially working, but as soon as I try to add values from the invoice_header table, only complete rows are selected -- the outer join functionality seems to be lost.

If we forget the actual columns I'm selecting for the moment, the SQL query looks something like this (actual tables and column names changed for brevity):

SELECT *
FROM ORDER_HEADER, ORDER_DETAIL, INVOICE_DETAIL
WHERE ORDER_HEADER.ORDER_ID = ORDER_DETAIL.ORDER_ID AND ORDER_DETAIL.ITEM_ID = INVOICE_DETAIL.ITEM_ID(+)

I now need to add INVOICE_HEADER into the query. For your information, each INVOICE_DETAIL row will have a matching entry in INVOICE_HEADER. Not all INVOICE_HEADER rows have matching INVOICE_DETAIL.

I know this isn't exactly clear, but if you need any other info, I'll do my best to explain.
0
matbeard
Asked:
matbeard
  • 3
  • 2
1 Solution
 
matbeardAuthor Commented:
I thought it might help if I uploaded the query I'm using now. This works, but does not show order detail unless there is matching invoice detail. I need to also show the orders where the invoice hasn't arrived yet.

SELECT *
FROM CCS_O_PURCHASE_ORDER, CCS_O_INVOICE, CCS_O_PUR_ORD_DETAIL, CCS_O_INVOICE_DETAIL
WHERE CCS_O_PUR_ORD_DETAIL.CCS_OLD_OLN_ITEM_NUM = CCS_O_PURCHASE_ORDER.CCS_OLN_ITEM_NUMBER AND CCS_O_PURCHASE_ORDER.CCS_OLN_ITEM_NUMBER = CCS_O_INVOICE.CCS_OLN_POP_ITEM_NUM(+) AND CCS_O_PUR_ORD_DETAIL.CCS_OLD_ITEM_NUMBER = CCS_O_INVOICE_DETAIL.CCS_OLD_POP_LINE_NUM(+)
0
 
infolurkCommented:
My god that is some clumsy field naming you have there. Going crosseyed just looking at it, let alone trying to think about it.
Anyways, try  left joins on the CCS_O_PURCHASE_ORDER.CCS_OLN_ITEM_NUMBER = CCS_O_INVOICE.CCS_OLN_POP_ITEM_NUM(+) AND CCS_O_PUR_ORD_DETAIL.CCS_OLD_ITEM_NUMBER = CCS_O_INVOICE_DETAIL.CCS_OLD_POP_LINE_NUM(+) relationships.

HTH
SFH
0
 
matbeardAuthor Commented:
It's a nightmare isn't it. That's Sage Line 100 for you. Trying to work out the relationships between these tables is so difficult (and I've even got a manual of data definitions).

Anyway... I thought the (+) suffix on those relationships means that they are left joins.

The same query with some comments...

SELECT *
FROM
CCS_O_PURCHASE_ORDER,  //The order header table. Includes order number and supplier
CCS_O_INVOICE,  //The invoice header table. Includes invoice number and date.
CCS_O_PUR_ORD_DETAIL,  //Order detail. Includes items ordered, quantities and costs.
CCS_O_INVOICE_DETAIL  //Includes items invoiced, quantities and costs
WHERE
CCS_O_PUR_ORD_DETAIL.CCS_OLD_OLN_ITEM_NUM = CCS_O_PURCHASE_ORDER.CCS_OLN_ITEM_NUMBER //Links order detail rows to their order header
AND CCS_O_PURCHASE_ORDER.CCS_OLN_ITEM_NUMBER = CCS_O_INVOICE.CCS_OLN_POP_ITEM_NUM(+) //Left joins order headers with invoice headers if they have been received
AND CCS_O_PUR_ORD_DETAIL.CCS_OLD_ITEM_NUMBER = CCS_O_INVOICE_DETAIL.CCS_OLD_POP_LINE_NUM(+) //Left joins order detail lines with invoice detail lines, if they exist

At some point, the order detail lines that have no matching invoice detail lines are being dropped, and I don't know why.
0
 
matbeardAuthor Commented:
I've solved this.

I have a feeling that the ODBC driver and/or Sage Line 100 simply can't cope with the query once I add a further join. It doesn't give me an error, it simply doesn't do anything for a long long time, until I force it to close.

So, I've gone about it in a different way. I've added a second sheet to my Excel workbook and linked it to a simple query that extracts the columns I need from the INVOICE_HEADER table. I then use a VLOOKUP funtion on the main worksheet to pull out the data I need from the second worksheet.

It may not be the most elegant solution, but it works and it's much quicker than waiting for the SQL query to pull out the required data.

Not sure what hapens to these points now though.
0
 
infolurkCommented:
Well done working it out yourself.
I think there is an option to close the question and have the points refunded, it has to be done by a mod though.

From the help link top left under faq

"How do I close a question?
 
Generally, there are four ways:

Select a comment (or comments) as the answer
If there are no expert comments, you have the option to delete your own question by clicking the Delete Question link at the bottom of the page
Leave a request in the Community Support topic area for a refund to close the question
Leave a request in the Community Support topic area to delete the question "

Try option 3.

Cheers
SFH
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now