Multiple Left Joins in Providex SQL Query

Question

i would like to create a query using providex sql which gives me the current year to date and prior year dollars sold total for each customer. my query currently has the AR_CustomerSalesHistory table left -joined twice to the AR_Customer table; one instance for the current year to date and another for the prior year. it is then grouped in order to obtain the sums from the appropriate fields. i did some research and found that performing more than one left join in a query is a little tricky.

here is my sql - can someone please let me know what i need to do to get what i want? the values i get for ytdDollarsSold and pyDollarsSold are way too high. if i take out the second left join and just try to get the ytdSales amount it works fine.

SELECT

AR_Customer.CustomerNo, AR_Customer.CustomerName, AR_Customer.AddressLine1, AR_Customer.AddressLine2, AR_Customer.AddressLine3, AR_Customer.City, AR_Customer.State, AR_Customer.ZipCode, AR_Customer.CountryCode, SY_Country.CountryName, AR_Customer.CustomerStatus, AR_Customer.UDF_LATITUDE, AR_Customer.UDF_LONGITUDE, SUM(ytdSales.DollarsSold) ytdDollarsSold, SUM(pySales.DollarsSold) pyDollarsSold

FROM

SY_Country,
{oj AR_Customer LEFT OUTER JOIN AR_CustomerSalesHistory ytdSales ON (AR_Customer.ARDivisionNo=ytdSales.ARDivisionNo) AND (AR_Customer.CustomerNo=ytdSales.CustomerNo)},
{oj AR_Customer custPy LEFT OUTER JOIN AR_CustomerSalesHistory pySales ON (custPy.ARDivisionNo=pySales.ARDivisionNo) AND (custPy.CustomerNo=pySales.CustomerNo)}

WHERE

AR_Customer.ARDivisionNo = custPy.ARDivisionNo AND
AR_Customer.CustomerNo = custPy.CustomerNo AND
ytdSales.FiscalYear = '2013' AND
ytdSales.FiscalPeriod <= '11' AND
pySales.FiscalYear = '2013' AND
AR_Customer.CountryCode = SY_Country.CountryCode AND
AR_Customer.UDF_LATITUDE <> '' AND
AR_Customer.UDF_LONGITUDE <> '' AND
AR_Customer.CustomerStatus = 'A' AND
AR_Customer.CustomerNo = '#1DISC'

GROUP BY

AR_Customer.CustomerNo, AR_Customer.CustomerName, AR_Customer.AddressLine1, AR_Customer.AddressLine2, AR_Customer.AddressLine3, AR_Customer.City, AR_Customer.State, AR_Customer.ZipCode, AR_Customer.CountryCode, SY_Country.CountryName, AR_Customer.CustomerStatus, AR_Customer.UDF_LATITUDE, AR_Customer.UDF_LONGITUDE

jcl · Accepted Answer

When using the Providex ODBC driver, there is a limitation of only one left outer join allowed per query, or you will get wonky results. Within Crystal Reports, you work around this by putting the tables for the second left outer in a subreport. With Access, you work around it by doing two queries separately, each with one left outer, then do a third query or report that pulls data from those two. I'm sure you can do something similar in SQL. 
 Most of the data you want is already standard in the Customer Sales Analysis Report within MAS. That report and its underlying data file include currently year and prior year information. You could just customize that report to add the SY_Country and AR_Customer tables in. Or most likely you can just use the Custom Office Module to add all the fields you need from both tables as UDF's in the AR_CustomerSalesAnalysisWrk table, and end up with a flat table that has every field in it you need. 
 Best of luck to you!

Sage 100

Multiple Left Joins in Providex SQL Query

COMMUNITY

COMPANY

PARTNERS

SUPPORT & TRAINING