Group By with Functions?

[Fuzzmaster]

Any suggestions how one might accomplish the following?:

SELECT
 LN.account,
 LN.vendor,
 LN.model,
 YEAR(LN.date) 'Year',
 MONTHNAME(LN.date) 'Month',
 SUM(LN.qty) 'Units',
 SUM(LN.price * LN.qty) 'Sales'
FROM
 PUB.sales AS LN
WHERE
 (YEAR(LN.date) IN ('200X','200X'))
GROUP BY
 LN.account,
 LN.vendor,
 LN.model,
 YEAR(LN.date),
 MONTHNAME(LN.date)
ORDER BY
 LN.account,
 LN.vendor,
 LN.model,
 LN.date

Connecting to 9.1D via ODBC (MERANT 3.7/IIS/PHP). I've tried several variations of this somewhat simple query to no avail, it would seem Progress is incapable of grouping by function (which seems contrary to the documentation). Also, you cannot simply group by LN.date as that merely groups the results by day.

Am I missing something simple or simply doomed to convoluted UNIONS and post processing?

 

[RealHeavyDude]

IIRC, the UNION statement was not supported in Progress V9 on the SQL engine. Plus, Progress 9.1d is ( to say it with Tom's word ) ancient, obsolete and unsupported.

You should go with a more recent version of OpenEdge 10.


Regards, RealHeavyDude.

 

[Fuzzmaster]

Unfortunately, upgrade is not currently possible. Also, I think UNION is supported, just incredibly poorly. Must say I've few hairs left at this point.