Sequence does not retrieve results as expected

[former_member199763]

Hi Experts,

Sequence is not giving output as expected when we tried to follow the below steps as in help.sap.com,

CREATE SEQUENCE - SAP HANA SQL and System Views Reference - SAP Library

Instead of giving an output as 12 and 13, it gives as 12 and 12. (Problem occurs in both SP8 and SP9)

Could you please advise us if we need to set any Admin level parameter to make sequence work as in help.sap.com link.

Thanks and Regards,

Ram

[lbreddemann · Accepted solution]

My guess is that this is a documentation error and that the .nextval is actually only evaluated once per result row in your sub-query.

But to get an official statement on this, you may want to open a support incident.

- Lars

[former_member199763 · Accepted solution]

Thanks Lars!

We will raise support incident and update the thread with the reply.

Thanks and Regards,

Ram

[lbreddemann · Accepted solution]

Just stumbled over this by accident.

This is from

ANSI TC NCITS H2 ISO/IEC JTC 1/SC 32/WG 3 Database

WG3:HBA-003 H2-2003-305

Foundation (SQL/Foundation)

0.  4.21.2 Operations involving sequence generators  
   [...] If there are multiple instances of <next value expression>s specifying the same sequence generator within a single SQL-statement, all those instances return the same value for a given row processed by that SQL-statement. 
   

1. So, the behaviour is actually correct according to the SQL standard and the SAP HANA documentation is wrong.
3. - Lars

[ruediger_karl · Accepted solution]

Thanks for the feedback. We will follow up on this with docu development.

Regards, Ruediger

[Former Member]

The behavior you observed is the normal one that you also see on other databases like Oracle (I just double-checked). If the documentation states something different, it's probably a docu bug like already assumed by Lars.

[former_member199763]

yes Martin, Oracle too retrieves similar output...