Alter Table Column Type Does not Work in Aster - I am Probably Screwing It Up

Question

I am using Aster as there are some groovy Random Forest Functions to use. My dependent, or response variable, is a boolean dichotomous variable; a 0 or 1.

When I run it through the Random Forest Function of choice it creates a predicted value of the response variable. It calls this variable prediction and it automatically creates it as a VARCHAR(REALLY BIG INTEGER IN HERE).

To do some of my calculations I simply wish to cast or convert it to an integer from a string. All of the resulting character strings are either a 0 or a 1:

alter table a0q892.zf_predict alter column prediction int;

does not work. The error message I receive is:

Executed as Single statement.
Failed [34 : 42000] [AsterData][ASTERJDBCDSII](34) ERROR: syntax error at or near "int" ()

I am pretty sure there are lots of fancy & elegant ways to do this. But I would think I could simply just make it an integer for future calculations?

Answer 1

As per the aster docs, there are limited options to manipulate colums. You cannot change a column data type.

However aster allows you to change the size of a varchar column. You mentioned that you want to cast to INTEGER, but I guess that in your use case VARCHAR(1) would be fine too. If yes, then you can go :

ALTER TABLE a0q892.zf_predict ADD prediction VARCHAR(1);

If you really need an INTEGER (or any other type than VARCHAR(n)), then you have to proceed the old way :

• create a new column in the table with the correct type
• fill it from the old column
• drop the old column
• rename the new column

SQL Aster :

ALTER  TABLE a0q892.zf_predict ADD prediction_new int;
UPDATE TABLE a0q892.zf_predict SET prediction_new = CAST(prediction AS int);
ALTER  TABLE a0q892.zf_predict DROP prediction;
ALTER  TABLE a0q892.zf_predict RENAME prediction_new TO prediction;