Adding value to column

[Former Member]

Dear Experts,

we have requirement like below,

We have POSNR Column type VARCHAR. We need to add +10 to every POSNR.

How can i do in calculated column.

Ex - 

POSNR	ZPOSNR
10	20
20	30
30	40

Please guide me.

Thanks,

Nag

[anindya_bose · Accepted solution]

Hi Nag

Create a Calculated Column type VARCHAR or NVARCHAR and use the expression below.

string(int("POSNR") + 10)

What we are doing here is to convert POSNR to Integer before adding 10 .  Once added, we are again converting back to string type.

Cheers

Anindya

[Former Member · Accepted solution]

Thank you Anindya,

It is working fine.

Acutally POSNR values are displaying like 000010,

After adding the above logic to ZPOSNR(Calculate column) getting the data like 20 only.

I am not getting 0000 in front. I need exactly 00020.

I have an idea to add 0000 in front, but for some documents 000130, if add 0000 it will be wrong.

Please suggest me.

Thanks,

Nag

[former_member217622 · Accepted solution]

Hi Nag,

Directly add 10 to POSNR, no need of converting to string. string(int("POSNR") + 10) is converting it into string that is why you are getting 20, instead of 000020.

Regards,

Rahul

[anindya_bose · Accepted solution]

That's easy.  You need to add another function for padding with zero.

lpad ( string(int("POSNR") + 10),6,'0000')

Here is the documentation for this function.  LPAD - SAP HANA SQL and System Views Reference - SAP Library

You will have zeroes still length is 6 character long .  You might change it to other values as well.

Cheers

Anindya

[Former Member · Accepted solution]

Thank you very much Anindya,

Can you please tell me, Do i need to learn only sql or i need to learn PL SQL also to perform above tasks.

Thanks,

Nag

[anindya_bose · Accepted solution]

Hi Madhu

For this you need neither.  These are SQL functions and just like you learn functions in Excel, you can learn these.

Go through the below link and try each function in your system.  SAP HANA SQL and System Views Reference - SAP Library

However, learning SQLScript is necessary to do job in HANA modeling, not every scenario you can achieve with graphical modeling tool.  In those cases, you need SQLScripting knowledge.

"HANA Academy"  in youtube is a good place to start.

Cheers

Anindya