Datatype change of a Column

[Former Member]

Hi,

          I have uploaded data from flat file.One of the column is in Nvarchar. Now i want to do some operations on it.How to change the data type from NVARCHAR to DECIMAL via query? or is there any other way?

[chandan_praharaj]

This message was moderated.

[Former Member]

Have you tried as below :

ALTER TABLE Table

ALTER (Column Data_Type DEFAULT Default_Value,

Column Data_Type NULL);

Also below one :

ALTER TABLE

   You can change the definition of an existing column.

   For columns without a default value, you can specify one.

   For columns with a default value, you can change the existing value.

   The new (or first time) defined default value only affects newly inserted rows.

   For columns that do not allow NULL values so far, NULL values can be allowed.

ALTER TABLE Official

ALTER (Overtime INTEGER DEFAULT 7,

Salary VARCHAR(3) DEFAULT 'A01',

Name VARCHAR(20) NULL);

[christianschuer]

Hi Rahul,

if the solutions offered above do not fit, have a look at the TO_DECIMAL conversion:TO_DECIMAL - SAP HANA SQL and System Views Reference - SAP Library

Kind regards

Christian

[Former Member]

Hi Rahul,

You can alter the data type of column in table, check HANA Changing the type of a column | SCN, https://help.sap.com/saphelp_hanaplatform/helpdata/en/20/d329a6751910149d5fdbc4800f92ff/content.htm.

Regards,

Shireesha

[rindia]

Hi Rahul,

Below are the restrictions in changing the column definitions:

• For column table, only increasing the size of a column data type is allowed to prevent data loss. For example, changing from nvarchar(20) to nvarchar(10) or from integer to tinyint will raise an error.
• For row table, only increasing the size of VARCHAR and NVARCHAR type column is allowed. Other data type changes are not allowed.
• ALTER does not currently follow data type conversion rules.
• Adding NOT NULL constraint to an existing column is allowed if either of the following cases are true:
  • The table is empty.
  • The default value is specified when the table contains data.
  • The table does not contain a NULL-value in that column.

For more details, please refer to link.

Regards

Raj

[nithinu]

Hi Rahul,

Can you try the below,

ALTER TABLE schema.table  ALTER (column_name decimal(?,?))

Regards,

Nithin