Error with SQL-Statement : 5016 POS(38) Missing delimiter

[lbreddemann]

Dear MaxDB Experts,

why does the following happen?:

insert into test (select * from test order by owner, tablename)

---- Error -------------------------------
Auto Commit: On, SQL Mode: Internal, Isolation Level: Committed
 General error;-5016 POS(38) Missing delimiter: )
insert into test (select * from test order by owner, tablename)

When the statement is changed the error also changes, but still the statement is not executed.

create table test2 as select * from test order by owner, tablename

---- Error -------------------------------
Auto Commit: On, SQL Mode: Oracle, Isolation Level: Committed
 Syntax error or access violation;-3014 POS(42) Invalid end of SQL statement
create table test2 as select * from test order by owner, tablename

The error appears for SQL Mode oracle as well as for mode internal.

Tested on systems:

7.5.00.39

7.3.00.48

7.6.00.33

7.4.03.32

Thanks,

Lars

[alexander_schroeder · Accepted solution]

Dear Lars,

ORDER BY in sub-select clauses are not supported by MaxDB. An ORDER BY would also have absolutely no effect on the result of the operation that should be

performed by your examples.

It is indeed not so good, of course, that you get different and not very helpful messages on both attempts.

Regards

Alexander Schröder

[Former Member · Accepted solution]

Hi,

it's quite a coincidence that I stumbled into nearly the same problem recently, and immediately found a matching topic here.

I need to create histograms on tables (for performance optimization - I started writing a program that will hopefully automate that task for me, in a DBMS-independent way), and tried something like:

select * from (select * from (select count(*) occ, vbeln, posnr from sapm11.vbap group by vbeln, posnr) order by occ desc) where rownum<11

or:

create view ZZZZHISTO as select count(*) occ, tabclass from dd02l group by tabclass order by occ desc

The usual errors are:

• -5016: POS(75) Missing delimiter: ) SQLSTATE: 42000

• -3014: POS(85) Invalid end of SQL statement SQLSTATE: 42000

Creating temporary tables for sorting is not really useful:

- the tables could be extremely large in certain cases

- read-only access is preferred

So, my questions are:

- Are the misleading error messages considered to be a bug that should be fixed?

- Could you think of a work-around in SQL? (I tried with Open and Native SQL in ABAP before, but the syntax limitations there much worse)

- Could that limitation for the "ORDER BY" clause be removed? I think its use makes sense in my example statements.

Bye

Michael Olbricht