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!