HANA Changing the type of a column

[Former Member]

Hi All,

Can we change the data type of a column using any function in HANA using a calculated column...

There is a function called Date() which changes a date type of a field which is loaded from SLT into HANA(The fields loaded from SLT are by default given type as Nvarchar), but this function does not change the type of the field.

Is there a way to change the type of a field.

Thanks,

Sneha

[henrique_pinto · Accepted solution]

Do you mean to alter the type in the table?

If so, you could use the ALTER command, check the alter_column_clause:

http://help.sap.com/hana/html/sql_alter_table.html#alter_table_alter_column_clause

CREATE TABLE t (a INT, b INT);
ALTER TABLE t ALTER (b INT DEFAULT 10);

If, alternatively, you mean to change the type just in the view, then you can achieve that in the calculated column.

Note that you actually define the calculated column's type, so you can have the output in any format you want.

Best regards,

Henrique.

[Former Member · Accepted solution]

Hi Henrique,

This solution worked to me.

Thanks

[Former Member · Accepted solution]

This message was moderated.

[Former Member · Accepted solution]

This message was moderated.

[chandan_praharaj]

This message was moderated.

[Former Member]

If you wish to change the data type in the base tables, you may also go for changing the type in SAP LT configurations as well

Refer the SAP LT configuration guides here http://help.sap.com/hana_appliance/

[Former Member]

Thanks Shreepad,

Do you mean using the configuration in SLT is IUCC_REPL_TYPMAP......where you can change the datatype, But a question again from myside

1. IF i change the type here will my tables in HANA which are already replicated also have a change of datatype or only the tables which are replicated after changing the configurations will have this datatype change

Thanks,

Sneha

[Former Member]

Hi Sneha,

I have not explored the SAP LT to that extent.

But for your query, I think it will not touch the already replicated data. It might not even allow to apply the type transformation as tables already have data. You might have to restart the replication for tables having date. But really not sure on this. There is a separate community totally for SAP LT in SCN. You can raise this query here http://scn.sap.com/community/replication-server.

In our scenario we are keeping the date fields as NVARCHAR(8) and left it to that. In case of any calculations based on dates, we have used time functions and/or Time Attribute Views in HANA. If you want to go for a similar logic, I might be able to help further.

--Shreepad

[Former Member]

This message was moderated.

[Former Member]

Hi Sneha,

The functions which you are referring to are the Conversion Functions that can be found under the 'Calculated Columns'.

These functions actually create a new column in the views-NOT in the persistence layer(actual tables).

These functions will just create a new column with the datatype you want.

--Shreepad

[RiccardoBrogi]

Hi Sneha,

you can't modify the "deep" structure of a table (Names, SQL Data Types an Column Store Data Types).

Cheers,

Riccardo