Looking for equivalent to "next value" in View

[Former Member]

Hi community,

I'm looking for a way to create a view with an autoincremented column, starting at the max value of a column in another table.

As an example the TPC-DS view "item_view" has the same requirement.

CREATE VIEW item_view as

SELECT next value for item_seq i_item_sk

,item_item_id....

I tried item_seq.NEXTVAL , but it turns out that HANA doesn't support sequences in Views.

How can I solve this problem??

Thanks for your help!

Kind Regards

Demian

[former_member182114 · Accepted solution]

Hi Demian,

Per nature views are read-only, the sequence is something that need changes.

You can obtain a row counter using two other options:

Window functions, look for ROW_NUMBER and others here: http://help.sap.com/hana/html/_esql_functions_window.html

Using CE_CALC with expression ' rownum() '

Regards, Fernando Da Rós

[rindia · Accepted solution]

Hi Demian,

Based on Fernado idea, you have to create the SQLscript like this:

 /********* Begin Procedure Script ************/ 
 BEGIN 
     c1 = CE_COLUMN_TABLE ("SRK"."BSE",["Date","Open","High","Low","Close"]);
     var_out = CE_PROJECTION (:c1, [CE_CALC('rownum()', integer) as "seq",  "Open"]);
END /********* End Procedure Script ************/

Here is my sample data, sqlscript and output as shown below

Regards

Raj

[Former Member]

Thanks Fernando and Raj.

Your answers really helped to solve the issue!

Regards

Demian

[rindia]

Good to know and credit should go to Fernado