Hello,

I am new to the world of Sage ERP.

I have designed this query and it works fine in SQL:

SELECT o1.*, (

Select isnull(SUM(D002_DollarsRented)+SUM(DollarsSold),0)
FROM openquery(TESTSWRMAS, 'select * 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 openquery(TESTSWRMAS, 'select * 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 openquery(TESTSWRMAS, 'select * 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

When I am trying to run this using Sage ERP MAS 200, it gives error:

Error Number: 1005

Description: 

[37000] SQL call failed. Expected lexical element not found: <identifier>

Native Error: 1015

Source: [ProvideX][ODBC Driver]

I think, complex queries are not allowed in ProvideX. How to deal with this?

Thanks!