Sage 200c SQL View Question

I am trying to create a SQL view that shows the order details from the SOPOrderReturn table, the email address on the order (if not using invoice address) from the SOPDocDelAddress table, and the main email address for the account (for use when there is no email on the order). So far I've managed to get it kind of working but it's showing the same lines multiple times. 

Can anyone suggest the best way to do this?

  • 0

    The multiple lines are probably due to multiple contacts or roles - the easiest way is to use SLCustomerContactDefaultsVw to get the customer's main email:

    SELECT  SOPOrderReturn.DocumentNo, SOPOrderReturn.UseInvoiceAddress, SOPDocDelAddress.EmailAddress, SLCustomerContactDefaultsVw.DefaultEmail
    FROM SOPOrderReturn INNER JOIN
        SOPDocDelAddress ON SOPOrderReturn.SOPOrderReturnID = SOPDocDelAddress.SOPOrderReturnID INNER JOIN
        SLCustomerContactDefaultsVw ON SOPOrderReturn.CustomerID = SLCustomerContactDefaultsVw.SLCustomerAccountID
    WHERE (SLCustomerContactDefaultsVw.IsDefaultRole = 1) AND (SLCustomerContactDefaultsVw.IsPreferredContactForRole = 1)
    ORDER BY SOPOrderReturn.DocumentNo