How to cast between DECIMAL 15.2 and 17.3?

[martin_lehmann4]

Hello experts,

from a DB2 database via SDA to hdb I get a value with format in DECIMAL with precision 15 scale 2. My table function has to deliver DECIMAL 17.3. How to get this? When I use the SELECT clause

value152 AS value173

I receive the error message

SQL0440N  No authorized routine named "TO_DECIMAL" of type "FUNCTION" having compatible arguments was found.  SQLSTATE=42884

Any suggestions?

Best wishes, Martin Lehmann

[Vitaliy-R]

The error indicates it is IBM DB2 database, not SAP HANA. You might want to tag your question with correct tags.

[martin_lehmann4]

Hi Witalij, thanks for the hint - I corrected the information about the source db...

[Frank-Martin · Accepted solution]

Hi,

I am not too familiar with HANA SDA.

Are you looking for a Db2 native SQL solution or for an ABAP OPEN SQL solution?

In Db2 native SQL you can just use a CAST.

> db2 " create table zDEC ( D DECIMAL(15,2 ) ) "
DB20000I The SQL command completed successfully.
> db2 " insert into zDEC values ( 1234567890123.45 )"
DB20000I The SQL command completed successfully.
> db2 " select * from zDEC "

D
-----------------
1234567890123.45

1 record(s) selected.

> db2 " select CAST( D AS DECIMAL(17,3 )) from zDEC "

1
-------------------
1234567890123.450

1 record(s) selected.

In CDS you could also use a CAST opaertor. Depending on your SAP BASIS version CAST should also be available in OPEN SQL. I would need to check when this has been introduced.

Regards

Frank