Progress SQL idiosyncracies ... please help

T

Tony@DataLynx

New Member
Hi, I am a total newb working with Progress however thus far I have found the implementation of SQL to be rather nicer than some other databases I have used, however I am having a problem with a particular query and I wonder if somebody could please explain to me why it doesn't work?

SELECT
COUNT(*) AS OCCURRENCES,
TRANSLATE( UPPER ("fnam" ),
'ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789',
'AAAAAAAAAAAAAAAAAAAAAAAAAA9999999999' ) AS COL_1,
​
min('<DLX_QUERY_END>') AS END_QUERY_MARKER
​
FROM PUB.people
GROUP BY
TRANSLATE( UPPER ("fnam" ),
'ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789',
'AAAAAAAAAAAAAAAAAAAAAAAAAA9999999999' )
​
ORDER BY 2
I get the following error:

SQL State = S1000
Native Error Code = -20022
Error Message = [DataDirect][ODBC Progress OpenEdge Wire Protocol driver][OPENEDGE]Non-group-by expression in select clause (7641)
I know it looks a bit odd but the code is generated dynamically from a a software product which I have developed, hence the end query marker thing.

The query will run against almost every other database I have tried it against (though sometimes I need to change the function call to TRANSLATE if that function is not supported) so there must be an idiosyncracy of Prorgess SQL that I need to be aware of. I'd be very grateful if somebody could point out the error of my ways!
 
Thanks RealHevyDude, I've been looking at the SQL Language Reference but I can't see anything which should stop my SQL from working.
 
Well, I've managed to resolve the problem; it seems that Progress doesn't like to use functions in the group by clause. So the following SQL does what I want:

select count(*), "fnam", '<DLX_END_MARKER>' AS END_QUERY_MARKER
from (
select TRANSLATE( UPPER ("fnam" ),
'ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789',
'AAAAAAAAAAAAAAAAAAAAAAAAAA9999999999' ) as fnam
FROM PUB.people ) x
group by fnam

It's a bit frustrating as I will need to rewrite my application to cope with this and tbh the original SQL should be valid by any standards you care to name, but hey ho, so long as I can make it work.

On the whole, I agree that Progress does a pretty bloody good job considering it is not an SQL database. Some of the queries my software writes are horrendously complicated and it has coped admirably until I stumbled across this problem.
 
You must log in or register to reply here.
Back
Top