TOP keyword in insert subQuery - difference between 7.08 and 7.07 SAPDB

[Former Member]

Hi all,

simple example below, on database 7.7 works fine but on 7.8 raises error: 'Table or index dropped or content deleted'. Is it known bug or is it feature/wrong syntax? Is TOP keyword fully supported by SAPDB? I know, it is similar to keyword 'rowno', but it is not the same.

Best regards,

Lukasz.

--------------------

create table "Test" ("Nr" FIXED (10))

//

INSERT INTO "Test" VALUES (1)

//

INSERT INTO "Test" VALUES ((SELECT TOP 1 "Nr" FROM "Test"))

-----------------

[thorsten_zielke]

Hi Lukasz,

thank you for submitting this comprehensible example.

I can confirm this  to be a bug beeing worked on with 'medium' priority per PTS 1241289 (you can access our error links via the Web-PTS for a status update).

Thorsten

[Former Member]

Hi Thosten,

thank You very much for help.

Bdw - could You tell me why select with TOP keyword can not be nested? For example: select * from (select TOP 1 * from "Test").

Best regards,

Lukasz.

P.S.

And the last - is anyone interested in some courious tricks in SAPDB sql syntax? For example: how to execute below command without 3021 'Invalid mixture of function' Error? For me is difficult to find and check right topic via Web-PTS system, and I do not know whether post this, probably known, 'bug' or not.

select min("Nr") from "Test"

group by min("Nr")

[thorsten_zielke]

Hi Lukasz,

nesting TOP does not work, because that functionality has not been implemented in MaxDB. As it seems to be a rather rare use case, we are not likely to support it anytime soon.

Regarding the 'Invalid mixture of function' error of...

select min("Nr") from "Test" group by min("Nr")

... let me add that this statement does not look like valid SQL to me. Why would you 'group by' the 'min' value?

[Former Member]

Hi Thorsten,

it's true - statement makes no sense. But if You add:

HAVING 1=1

MAXDB query is executed without any error.  Maybe it is wrong statement, but now for validator is correct. Why are two diffrent interpretations?

I know, SapDB validator is very flexible  - in 'group by' You can post expressions, and run own functions (and modify other data), therefore even comma is not needed (you can use +). But someone add 3021 error code, and sometimes it does not work.

Best regards,

Lukasz.