Converting Datatypes

[elsenorjose]

I am working with a call management system built on Informix 2000. There is a 'synonyms' table which the system's reporting application uses to match ID values to more useable, real world descriptions. The interesting dilemma is that in a table that captures call center agent activity (dagent), a value called LOGID (agent login ID) is in SMALLINT format while the corresponding lookup field in 'synonyms' is CHAR. When querying the table and joining on dagent.logid=synonyms.value, I get a data type mismatch. I can't change the underlying data type in either table but I need the dagent.logid to display as the description stored in 'synonyms', not the numeric value. I am not using the built in VERY LIMITED reporting application which is why I need to do this conversion manually. The system somehow does it since reports generated from within the reporting application shows the value I need. How can I accomplish this via an SQL query?

Thank you.

J.R.

 

[vpshriyan]

Hi,

Should the need arise, informix does the conversions of datatypes internally, without intimating the end-user about it, however, when it is not possible, it complaints; like char datatype can be used to store alphanumeric characters and when comparison is make with a smallint or int data type, it will rejects those data that have alphas or embedded with alphabets.

Check your data table for such cases:
select * from synonyms where upper(value) matches '*[A-Z]*';

Regards,
Shriyan
"The hidden flaw never remains hidden."

 

[elsenorjose]

I found the answer. Here is the correct syntax:

...WHERE new_col = CAST(old_col AS newtype)

so my code is

selct t1.fldname, t2.fldname
from t1, t2
WHERE t1.fldname= CAST(t2.fldname AS t1.fldname datatype)

Thanks for the response.