x Want to participate? Join the group to interact
  • State Not Answered
  • Replies 2 replies
  • Subscribers 15 subscribers
  • Views 457 views
  • Users 0 members are here
Browse Forums
Announcements
105
topics
4
replies
General Discussion
1k
topics
2k
replies
Jorawar Johl

Sage 200 ODBC

Posted By

Hi,

I am trying to amend a query from Sage as needed to add in the delivery address details but doesn't seem to be working. Below is the original code that I need to amend and have highlighted in yellow in the table below what I am trying to add - any help would be much appreciated?

SELECT SOPInvoiceCredit.DocumentDate, SOPInvoiceCredit.DocumentNo, SOPInvoiceCreditLine.ItemName, SOPInvoiceCreditLine.ItemPrice, SOPInvoiceCreditLine.InvoiceCreditQuantity, SOPInvoiceCreditLine.LineTotalValue, SOPInvoiceCreditLine.OrderReturnNo, SOPOrderReturn.AnalysisCode1, SOPOrderReturn.DocumentCreatedBy, SOPOrderReturn.AnalysisCode2, month(SOPInvoiceCredit.DocumentDate) AS 'Month', SOPOrderReturn.CustomerDocumentNo, SOPOrderReturnLine.ItemCode, SOPInvoiceCredit.SOPInvoiceCreditTypeID, SLCustomerAccount.CustomerAccountNumber AS 'CustomerAccountNumber', SLCustomerAccount.CustomerAccountName, SOPOrderReturn.AnalysisCode5, SOPOrderReturn.AnalysisCode6, SOPOrderReturn.AnalysisCode8, SOPOrderReturnLine.NominalCostCentre AS 'Cost Centre', SOPInvoiceCredit.DocumentStatusID
FROM XXX.dbo.SLCustomerAccount SLCustomerAccount, XXX.dbo.SOPInvoiceCredit SOPInvoiceCredit, XXX.dbo.SOPInvoiceCreditLine SOPInvoiceCreditLine, XXX.dbo.SOPOrderReturn SOPOrderReturn, XXX.dbo.SOPOrderReturnLine SOPOrderReturnLine
WHERE SOPInvoiceCreditLine.SOPInvoiceCreditID = SOPInvoiceCredit.SOPInvoiceCreditID AND SOPOrderReturnLine.SOPOrderReturnLineID = SOPInvoiceCreditLine.SOPOrderReturnLineID AND SOPOrderReturn.SOPOrderReturnID = SOPOrderReturnLine.SOPOrderReturnID AND SLCustomerAccount.SLCustomerAccountID = SOPOrderReturn.CustomerID

Thanks,

Jo

  • 0

    Hi.

    You should change the SQL to use JOINs as it makes the query easier to work with. Try this:

    SELECT SOPInvoiceCredit.DocumentDate, SOPInvoiceCredit.DocumentNo, SOPInvoiceCreditLine.ItemName, SOPInvoiceCreditLine.ItemPrice, SOPInvoiceCreditLine.InvoiceCreditQuantity, 
SOPInvoiceCreditLine.LineTotalValue, SOPInvoiceCreditLine.OrderReturnNo, SOPOrderReturn.AnalysisCode1, SOPOrderReturn.DocumentCreatedBy, SOPOrderReturn.AnalysisCode2, 
month(SOPInvoiceCredit.DocumentDate) AS 'Month', SOPOrderReturn.CustomerDocumentNo, SOPOrderReturnLine.ItemCode, SOPInvoiceCredit.SOPInvoiceCreditTypeID, 
SLCustomerAccount.CustomerAccountNumber AS 'CustomerAccountNumber', SLCustomerAccount.CustomerAccountName, SOPOrderReturn.AnalysisCode5, SOPOrderReturn.AnalysisCode6, 
SOPOrderReturn.AnalysisCode8, SOPOrderReturnLine.NominalCostCentre AS 'Cost Centre', SOPInvoiceCredit.DocumentStatusID, SOPInvCredAddress.AddressLine1, SOPInvCredAddress.AddressLine2,
SOPInvCredAddress.AddressLine3, SOPInvCredAddress.AddressLine4, SOPInvCredAddress.PostCode
FROM SLCustomerAccount SLCustomerAccount
INNER JOIN SOPInvoiceCredit SOPInvoiceCredit on SLCustomerAccount.SLCustomerAccountID = SOPInvoiceCredit.CustomerID
INNER JOIN SOPInvoiceCreditLine SOPInvoiceCreditLine on SOPInvoiceCredit.SOPInvoiceCreditID = SOPInvoiceCreditLine.SOPInvoiceCreditID
INNER JOIN SOPOrderReturn SOPOrderReturn on SLCustomerAccount.SLCustomerAccountID = SOPOrderReturn.CustomerID
INNER JOIN SOPOrderReturnLine SOPOrderReturnLine on SOPOrderReturn.SOPOrderReturnID = SOPOrderReturnLine.SOPOrderReturnID
INNER JOIN SOPInvCredAddress SOPInvCredAddress on SOPInvoiceCredit.SOPInvoiceCreditID = SOPInvCredAddress.SOPInvoiceCreditID
WHERE SOPInvoiceCreditLine.SOPInvoiceCreditID = SOPInvoiceCredit.SOPInvoiceCreditID AND SOPOrderReturnLine.SOPOrderReturnLineID = SOPInvoiceCreditLine.SOPOrderReturnLineID 
AND SOPOrderReturn.SOPOrderReturnID = SOPOrderReturnLine.SOPOrderReturnID AND SLCustomerAccount.SLCustomerAccountID = SOPOrderReturn.CustomerID

  • 0

    Jo,

    You will need to add the SOPInvCredDelAddress with a link on SOPInvoiceCreditID to SOPInvoiceCredit.  I would make it a LEFT OUTER JOIN to ensure that it doesn't exclude records without a different delivery address.

*Community Hub is the new name for Sage City