Do we have a ROWID or equivalent in HANA ?

[Former Member]

Hi,

We are in the process of putting our HR data into HANA. The existing Oracle system uses a lot of analytical functions like LEAD, LAG, FIRST_VALUE to manipulate data. I m manually coding for these functions which is making my life really tough. My life would become a little easier if I can find an equivalent to the Oracle concept of ROWID. In Oracle ROWID is a hexadecimal value which recognizes a row in the table. For example if a table has 2 duplicate rows then by using ROWID we can distinguish them. Do we have something similar in HANA ?

When HANA is planning to provide analytical function to calculate LAG, LEAD functions to calculate the previous n value and post n values of a row by partitioning the data. These are very essential and can not think of proceeding without these.

Thanks a ton in advance.

Regards,

Samarpan

[sorin_radulescu · Accepted solution]

For RowID it is a variable providing this information.

For example:

select  "$rowid$" from yourtable

is working and it is probably  providing like result what you  are expecting.

Nevertheless: $rowid$ is purely internal and non-documented. Because of that Richard mentioned that  Rowid functionality is not available.

Another option is to use SQL scripting where you have rownum() function fully supported.

Any way it is recommended to use Sql scripting because the calculation engine provide a better parallelisation of your queries.

HANA has also functions like LEAD, LAG ...and others working with MDX calls.

Next step wil be to provide all these analytical functions like SQL functionality.

At his stage any of this function can be achieve using modeling

I hope this will help you.

Kind Regards

Sorin Radulescu

SAP- Customer Solution Adoption

[Former Member · Accepted solution]

Hi Sorin,

Thanks for the reply. We are not in a position to perform non equi join on the views and have to use many workarounds. Do you have any idea on how to perform non equi join on 2 attribute views while creating an analytical view.

Thanks a ton in advance.

Regards,

Samarpan

[mert_karakilic · Accepted solution]

FYI, not sure if something has changed, we are using SPS9 rev 92, but assigning the $rowid$ to a newly created empty column to create that unique ID values corrupts the table irreversably. We had to drop a significant work and recreate the table from scratch. I have ended up using the row_number function to assign an ID for each row and importing into another empty table. That seemed to have worked.

[lbreddemann · Accepted solution]

We had to drop a significant work and recreate the table from scratch

That's a pity - but really: everybody always states that the $rowid$ column is internal and direct access is unsupported. Trying to do it anyway is a bug.

For ID generation there exist other options like the IDENTITY column or sequences.

[former_member656436]

Please just check this link:

I hope it will help!

Regards,

Muhammad

[Former Member]

Hi Samarpan,

Kindly use this SQL block according to your table,

Select * from

(
Select

< column_name>,row_number() OVER (Order by <column_name>) As row_number

from <Your_Table>

)

where 1=1

and row_number <=100    // (any operator that meets with your conditions you may use ..)

Regards,

Emrah KAYA

[Former Member]

Hi Samarpan,

This seems to work. It is using a HANA window function to return the row_id...

select TERM, row_number() over (order by term) as row_num

        from DOCS

        group by term

        order by term;

More info can be found in this HANA acadmey video here: https://www.youtube.com/watch?v=GeuoSa0uhMc

Best regards,

Mark

[Former Member]

Hi all,

I've followed your posts and have the similar problem. In fact I want to pick a random number of rows from a table but the soloution number one to use "$rowid$" doesn't work because first of all it works just on column base tables and even if it works there isn't a clear logic behind it! Literally the rows of a table don't have a sequence of "$rowid$" s. Fore Example the following query may return nothing:

SELECT "$rowid$", FIRST_NAME FROM PERSON

WHERE "$rowid$" = a_random_number

soloution number two as Bijendra Agrawal mentioned (to create a sequence) isn't also suitable for my task because I need it often and I have to create a lot of sequences (i.e. 100 times) and then drop it and ... so it is far a way from the right solution.

The third solution to sort randomly a table and then select exact number of rows isn't also efficient because it's awful on tables with for example one million records. Here I write an example to understand what I mean:

SELECT TOP a_number * FROM (REQUIRED_SQL_QUERY)

ORDER BY RAND()

which REQUIRED_SQL_QUERY is a SQL query and a_number could be any number. It will be executed and the output is exactly what I need but it's too slow.

I'm wondering is ther any other solution at all ? And we have to consider that Oracle or even MSSQL had solved this before.

Cheers,

Aryan

[sorin_radulescu]

Hi Samarpan ,

please provide an example of your join which you want to achieve and base on that I will try to provide a solution.

In this way we will have an example and of course you will be able to create similar model in other cases.

Kind Regards

Sorin Radulescu

[Former Member]

Hi Sorin,

Thanks a ton for replying.

Let me try giving you an example. Say I have an  attribute view for Employee ( EmpId, Job_Name,Begin_Date, End_Date) and an attribute view for my time dimension.

So, lets say my requirement is to show the details of the job the employee id  changes between 2 given dates. So I need to use a between clause while joining two attribute views. But currently I think only I can do equi join. So for the given requirement I can only join the table on a particular date  from date dimension but I am not able to do it for a range.

Could you please explain me how do I do it ?

Thanks again in advance for taking your time to reply me.

Best Regards,

Samarpan

[sorin_radulescu]

Hi Samarpan,

Let's ssuppose you have the between option for join. How you will write the select in sql (because into sql you have

the between option also for join) to achieve your requirementnts.

I'm asking just to be sure I will provide you the right solution.

In your example from my point of view between can be added into where condition not necessary to be part of join.

Please post your select and we will see if we really need the between to be part of join or we can put the between into where condition.

Regards

Sorin Radulescu

SAP Customer Solution Adoption

[Former Member]

Hi Sorin,

Thanks for replying.

In this case I will write the join as

select E.Job_name from employee E , Dim_Date D1, Dim_Date D2

where E.Begda >=  d1.date_key and         

and E.Endda < d2.date_key

and d1.date_key = V_Start_Date

and d2.date_key = V_End_Date

Please note the V_Start_Date and V_End_Date values are passed from BO Universe by the user.

Assume Employee and Dim_Dates are attribute views.

Hope I am clear.

Thanks,

Samarpan

[sorin_radulescu]

It is what I expected but in that case actaully you don't need dim_date (D1 and D2)

actually you will have just one attribute view containing 3 tables:

Employee, Dim_Date, Dim_Date_Alias

join between Employee and Dim_Date will be Begda = date_key

join between Employee and Dim_Date_Alias will be Endda = date_key

Dim_Date_Alias is a table with the same data with Dim_Date

(create table Dim_Date_Alias like Dim_Date WITH DATA) an example about how to create this table.

you select from attribute will be:

select Attribute.job_name from Attribute where Attribute.begda<= V_start_Date and Attribute.Endda > V_end_date

This will  provide the same result like your select.

I hope this will help you.

Regards

Sorin

[Former Member]

This works but my question is why SAP does not provide non equi join, is it something that if we use non equi join then the performance will be compromised on columnar tables ? With non equi joins I can manage with one dimensional table where as my modelling becomes difficult.

[sorin_radulescu]

Hi Samarpan,

You are right in many cases when you have non equi join HANA engine will involve the row engine and that will have impact regarding the performances.

It is actually mentioned into SAP HANA Development Guide - How to Use SQL and SQL Script for Data Modeling” at http://help.sap.com/hana/hana_dev_en.pdf (from https://help.sap.com/hana_appliance), section 4.3.2 on page 72 states .

So even if it will be possible it will be recommended to be avoided because the performances in this case will not be in line with expectations.

Regards

Sorin

[Former Member]

Dear Samarpan,

You can use sequence number as a workaround solution.

Step 1

Create a Sequence by following command

CREATE SEQUENCE sqROWN START WITH 1

Step 2

Write the query and use Sequence.Nextval for rowed like :

select sqROWN.nextval ROWID, Dealer_ID, Dealer_Name  from car_dealers

Regards

Bijendra Agarwal

[Former Member]

Hi Bijendra,

This is not a solution ...

Put an outer query to the sql where you use the sequence and see the magic .. SAP does not allow it.

Try this !!!

select rowid,Dealer_ID, Dealer_Name

from

(

select sqROWN.nextval ROWID, Dealer_ID, Dealer_Name  from car_dealers

)

Thanks,

Samarpan

[Former Member]

Hi Samarpan,

I do not understand what new you have provided here.

Base solution is that “you required sequence number to handle this problem”.

You need to add outer query or join any query based on your requirement.

Thanks

Bijendra

[Former Member]

HI Bijendra,

I never claimed that I produced something new here neither I want to.

The sequence thing is to produce serial numbers but as I have stated earlier I want to produce these numbers, so that I can play around with the numbers by creating a view (sql) on top of it or I can use these as sub-queries. For example think of writing a LAG or LEAD functions. It can not be done in my case without generating sequence numbers.

The idea of sequence does not help me because I can not use this in a sub query or I can not create a sql view of this sql statement. So this solution does not work.

Look at the below sql snippet which gives you actual sequence numbers.

input_table_2 = CE_PROJECTION

  ( :input_table_1,

   [

    "MGR_ID"

    ,"MGR_POS"

    ,"BEGDA"

    ,"ENDDA"

    ,CE_CALC('rownum()',integer) AS "RNUM"

   ]

Thanks for reading this thread and taking time to reply.

Thanks,

Samarpan

[richard_bremer]

Dear Samarpan,

HANA does not yet offer standard analytical SQL extensions, but we are working on such functions. A Row-ID is not exposed to SQL developers (or SQL-Script/stored procedure developers).

Kind regards,

Richard

--

Dr. Richard Bremer

SAP AG, Customer Solution Adoption (CSA)