cancel
Showing results for 
Search instead for 
Did you mean: 

Datatype change of a Column

Former Member
0 Kudos

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?

Accepted Solutions (0)

Answers (6)

Answers (6)

chandan_praharaj
Contributor
0 Kudos

This message was moderated.

Former Member
0 Kudos

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);

0 Kudos

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
0 Kudos
rindia
Active Contributor
0 Kudos

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
Participant
0 Kudos

Hi Rahul,

Can you try the below,

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

Regards,

Nithin