Search code examples
sqltypesinformixalter

SQL - Informix - Changing the datatype of a column from numeric to character

I have a temp table that has numeric integer values in one column. I want to either replace the integer values with character values based on some criteria or I want to add another column of character type that automatically inserts values into itself based on some criteria.

If x <= 1, change to "SP" or make new column and store "SP" in that row
If x > 1, change to "FA" or make new column and store "FA" in that row

Also, alter commands are not allowed on temp tables in my version of Informix.

Solution

  • SELECT id, yr, CASE WHEN yr_offset <= 1 THEN "SP" ELSE "FA" END CASE

    http://publib.boulder.ibm.com/infocenter/idshelp/v10/index.jsp?topic=/com.ibm.sqls.doc/sqls909.htm