ProvideX/Sage ODBC Driver with GROUP BY and Column Alias

Question

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:

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

Summary:

Is this a bug with the ProvideX driver, or an error with my SQL query?
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.

For this example, yes, technically that would work - but not if the first column was a calculated column that needed to be grouped by, i.e. If you wanted to extract the month from the InvoiceDate field, and wanted to then group by that month. This is possible in MySQL, doesn't work here with the same SQL. — vbnet3d, Oct 28 '15 at 18:31

score 0 · Accepted Answer · answered Nov 02 '15 at 16:12

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.