Checking out the new LIMIT feature available in 7.6.0.34

[Former Member]

Hi!

I am trying to get a limited result from my database.

MAXDB 7.6.00.34

SQLSTUDIO 7.6.00.27

That works fine:

SELECT * FROM MY_TABLE WHERE DESCRIPTON LIKE '%FOO%' LIMIT 10 ORDER BY ID

Now I like to specify an offset according to the documentation:

SELECT * FROM MY_TABLE WHERE DESCRIPTON LIKE '%FOO%' LIMIT 10,10 ORDER BY ID

-> Doesn't work because the comma seems wrong here. (The comma will be recognized in one of the next version properly)

Next try:

SELECT * FROM MY_TABLE WHERE DESCRIPTON LIKE '%FOO%' LIMIT 10 10 ORDER BY ID

->Does not return an error, but none of the two numbers seem to set the offset. The result set has a size of 20 items. Problem seems that all rows before the offset are returned as well.

Any help is highly appreciated!

Best Regards

Sebastian

[roland_mallmann]

Hi Sebastian,

the comma will be recognised from MaxDB version 76.01 Build 00 onwards.

Regarding the LIMIT 10 10 statement, the resultcount (20) is as expected, the offset is working correctly. It's more or less a mental note of the DB that it counts all entries up until the specified number (i.e. 10+10). During a real fetch, only the 10 correct entries will be fetched.

Regards,

Roland

[Former Member]

Hi Roland!

Thank you for the reply. Do you know how to simulate a real fetch with SQLStudio? What does SQLStudio different during sql calls that MAXDB considers them as "not real"?

Best Regards

Sebastian

[roland_mallmann]

Hi again,

I forgot to say that the resultcount might have stated 20, the result (from the fetch) should only display the 10 (=LIMIT) entries.

In other words, in SQL Studio, you would see that 'Rows in Result' would display a value of LIMIT+OFFSET, but the result itself (in the result) window, should display only the number of entries specified by LIMIT (in your case 10).

Hope that clears things up a bit. It might be a bit confusing indeed.

Regards,

Roland

[Former Member]

Hi!

This is exactly my problem. The result window comes up with (Limit + Offset) rows NOT the count.

Regards

Sebastian

[roland_mallmann]

Hi Sebastian,

can you send me a screenshot of your SQL Studio result window?

You can find my emailaddress on my SDN business card.

I have a small test DB which has the exact same DB version, my SQL Studio version is also the same, but I get the correct amount of rows in the result window.

Regards,

Roland

[Former Member]

Okay, I think that I have found my mistake. When you set SQLStudio to SQLMode internal you should have the same problem like me. In SQLMode Oracle LIMIT works fine! Don't ask me why I haven't tried that before.

Thank you for the support, I hope other guys will find a usefull help here if they have the same problem.