ProvideX/Sage ODBC Driver with GROUP BY and Column Alias

Scenario:

• Sage 100 ERP
• ProvideX ODBC Driver (32 bit)
• Standard SQL statement containing a column alias and GROUP BY statement.

---

Comments:

We are using the PVX/Sage ODBC connector with a standard Sage 100 ERP environment. The driver is working normally otherwise.

---

Examples:

1. Simple Column Alias

SELECT  InvoiceDate as TheColumn,
        SUM(NonTaxableSalesAmt) as Total    
    FROM AR_InvoiceHistoryHeader
    WHERE InvoiceDate <= {d[startdate]} AND InvoiceDate >= {d[enddate]}
    GROUP BY TheColumn

2. Calculated Column Alias

SELECT  {fn MONTH(InvoiceDate)} as TheColumn,
        SUM(NonTaxableSalesAmt) as Total    
    FROM AR_InvoiceHistoryHeader
    WHERE InvoiceDate <= {d[startdate]} AND InvoiceDate <= {d[enddate]}
    GROUP BY TheColumn

3. GROUP BY Calculation

SELECT  {fn MONTH(InvoiceDate)} as TheColumn,
        SUM(NonTaxableSalesAmt) as Total    
    FROM AR_InvoiceHistoryHeader
    WHERE InvoiceDate <= {d[startdate]} AND InvoiceDate <= {d[enddate]}
    GROUP BY {fn MONTH(InvoiceDate)}

Results:

1. ERROR [S0000] [ProvideX][ODBC Driver]Column not found: TheColumn
2. ERROR [S0000] [ProvideX][ODBC Driver]Column not found: TheColumn
3. ERROR [37000] [ProvideX][ODBC Driver]Expected lexical element not found: (blank)

---

Summary:

1. Is this a bug with the ProvideX driver, or an error with my SQL query?
2. Are there any known work-arounds for this scenario?

---

Clarification: I am not specifically looking for the solution for this exact use case, but a general solution for the Column Alias/GROUP BY combination.

After a long back-and-forth with Sage and ProvideX, here is the conclusion on GROUP BY with the Sage MAS 100 ERP connector: The ProvideX documentation is not accurate for the Sage driver because of customizations on the part of Sage. The ODBC driver used with Sage MAS 100 ERP does not support aliases or functions with GROUP BY statements. The GROUP BY statement must be in the form of GROUP BY [actual column].

An alternative work around to use a simple query (SELECT without GROUP BY), and either 1) do the GROUP BY operation in your application or 2) Import the data into Microsoft Access or another DBMS as a pass-through query, and do the complex query in Access.