0

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?

Zach
  • 37
  • 8
  • Which RDBMS are you using : oracle, mysql, sqlserver, ... ? – GMB Dec 06 '18 at 21:02
  • Aster is all I know. Should I look for more information than that? – Zach Dec 06 '18 at 21:06
  • Actually - my HUNCH is that given I am trying to alter a table that there may also be permission issues going on? – Zach Dec 06 '18 at 21:28
  • never heard of Aster before today but a quick google of Aster Datatypes gave me https://docs.tibco.com/pub/sfire-analyst/7.7.0/doc/html/en-US/TIB_sfire-analyst_UsersGuide/connectors/tera-as/tdaster_teradata_aster_data_types.htm From here it appears there is not a data type INT. They ahve BIGINT, SMALLINT, or INTEGER try using one of these instead. – Shaun Peterson Dec 06 '18 at 21:44

1 Answers1

0

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;
GMB
  • 216,147
  • 25
  • 84
  • 135
  • Thank you GMB. All works, I think, except for row 2. The error is: Executed as Single statement. Failed [34 : 42000] [AsterData][ASTERJDBCDSII](34) ERROR: syntax error at or near "table" () Elapsed time = 00:00:00.281 – Zach Dec 07 '18 at 18:08
  • Oh ok, there was a missing « ; » on that line, fixed it – GMB Dec 07 '18 at 18:13
  • Still not working: Executed as Single statement. Failed [34 : 42000] [AsterData][ASTERJDBCDSII](34) ERROR: syntax error at or near "table" () – Zach Dec 07 '18 at 18:28
  • Fixed 4th statement – GMB Dec 07 '18 at 20:12