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

Question

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.

Answer 1

SELECT    enrollsess.id,
       enrollsess.yr,
       "SP" sess
FROM      enrollsess
WHERE     enrollsess.yr_offset <= 1
UNION
SELECT    enrollsess.id,
       enrollsess.yr,
       "FA" sess
FROM      enrollsess
WHERE     enrollsess.yr_offset > 1
INTO      TEMP enrollsess2 WITH NO LOG;

Answer 2

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

Answer 3

You're correct, you cannot alter a temp table. Adding an extra column with this derived value can be done with a CASE statement, ie:

SELECT enroll.ud, enroll.yr, (CASE
   WHEN enrollsess.yr_offset <=1 THEN "FA"
   ELSE "SP" END)::CHAR(2) AS sess, ...

The casting (ie the parentheses and ::CHAR(2)) are probably not necessary.

If the logic can be expressed as zero/non-zero (it's not clear in your example if yr_offset can be negative), then it's even simpler:

SELECT enroll.id, enroll.yr,
     DECODE(enrollsess.yr_offset, 0, "FA", "SP")::CHAR(2) AS sess, ...

More details on CASE syntax from the manual