It is not so much that complex queries are not allowed, it is just that the ODBC driver cannot handle them.  You best option is to create a query for each table you are going to use, for example SELECT * FROM AR_Customer and then use a complex query based on those queries. I normally do that in Access

@BigLouie, Thank you for the answer.

Will you please explain me how to build a complex query later based on those small individual queries? (Will each query be a separate job?)

@BigLouie, Thank you for the answer.

Will you please explain me how to build a complex query later based on those small individual queries? (Will each query be a separate job?)

could you please elaborate on what you're doing exactly? it sounds like you ran the query from sql server and it worked. what did you then do afterwards? what do you mean by "using sage erp mas 200"? for one thing, i know that "OPENQUERY" is not supported by providex's odbc driver.

roastbeast...

I am trying to achieve following:

For each unique combination of ARDivisionNo and CustomerNo from AR_Customer, I am trying to get 3 things:

1.  SalesPTD: Sum of D002_DollarsRented and DollarsSold from AR_CustomerSalesHistory for current month and current year.

2. SalesYTD: Sum of D002_DollarsRented and DollarsSold from AR_CustomerSalesHistory for current year.

3. SalesPYR: Sum of D002_DollarsRented and DollarsSold from AR_CustomerSalesHistory for previous year.

You are correct. The query mentioned in the Q, which I initially constructed in SQL server, worked properly in SQL Server.

Later, I tried to following version of the same in Scribe workbench in source, where source was Sage ERP MAS:

SELECT o1.*, (

Select isnull(SUM(D002_DollarsRented)+SUM(DollarsSold),0)

FROM AR_CustomerSalesHistory o2

WHERE FiscalYear=DATEPART(YYYY, GETDATE())

AND FiscalPeriod=DATEPART(MM, GETDATE())

AND o1.ARDivisionNo=o2.ARDivisionNo

AND o1.CustomerNo=o2.CustomerNo) AS SalesPTD,

(Select isnull(SUM(D002_DollarsRented)+SUM(DollarsSold),0)

FROM  AR_CustomerSalesHistory o3

WHERE FiscalYear=DATEPART(YYYY, GETDATE())

AND o1.ARDivisionNo=o3.ARDivisionNo

AND o1.CustomerNo=o3.CustomerNo) AS SalesYTD,

(Select isnull(SUM(D002_DollarsRented)+SUM(DollarsSold),0)

FROM AR_CustomerSalesHistory o4

WHERE FiscalYear=DATEPART(YYYY, GETDATE())-1

AND o1.ARDivisionNo=o4.ARDivisionNo

AND o1.CustomerNo=o4.CustomerNo) AS SalesPYR

FROM AR_Customer o1

And yes...you are correct:  "OPENQUERY" is not supported by ProvideX's odbc driver. Hence, I have not included that in the query that I was trying in Scribe workbench on source side, where source is Sage ERP MAS.

Please let me know if you need any other information from my side. 

Thanks!