Converting a number to Text

Hi, I'm having a tough time with my SQL statement when I try to join two table. One table has a field named doc_no and is numeric while the other table has a field named record_key and is text. When i write "doc_no = record_key" and run the report, I get an error message about data types not being equal. How can I force the doc_no to be casted as text so I can join the tables? Please Help. Thank You, Chaim Bochner

chaboch wrote: > Hi, > > I'm having a tough time with my SQL statement when I try to join two > table. One table has a field named doc_no and is numeric while the > other table has a field named record_key and is text. When i write > "doc_no = record_key" and run the report, I get an error message about > data types not being equal. > > How can I force the doc_no to be casted as text so I can join the > tables? > > Please Help. Thank You, > > Chaim Bochner You are stuffed! text is a dumb blob type and there isn't much you can do with it. Not even cast it. If you can make record_key a (say) lvarchar, or even a char (I am guessing your text is not very long, if all you expect to do with it is to compare it with integers), then you can join the two tables with no other requirement. A word of warning: comparing different data types (and in particular, casting every single row to an integer) may be a rather inefficient business -- Ciao, Marco ______________________________________________________________________________ Marco Greco /UK /IBM Standard disclaimers apply! Structured Query Scripting Language http://www.4glworks.com/sqsl.htm 4glworks http://www.4glworks.com Informix on Linux http://www.4glworks.com/ifmxlinux.htm

On Mon, 2007-07-30 at 16:06 +0000, chaboch wrote: > Hi, > > I'm having a tough time with my SQL statement when I try to join two > table. One table has a field named doc_no and is numeric while the > other table has a field named record_key and is text. When i write > "doc_no = record_key" and run the report, I get an error message about > data types not being equal. > > How can I force the doc_no to be casted as text so I can join the > tables? Do you really mean "text" literally as in "the column type is 'text'" or do you mean it figuratively as in "the column is a char(x) that contains numeric and non-numeric data"? If it's the former, as Marco said, you're out of luck, but I find it *highly* unlikely that a column called "record_key" would be of type "text." If that is actually the case, you have no option but to accept our sincere condolences. If the record_key column is actually a "char(x)" type, you can cast the doc_no with "cast(doc_no as char(x))" or "doc_no::char(x)", where x would be the actual length of the record_key column. If your engine is too old to support casts, you can achieve the same effect with the expression "doc_no||''". Note however, that in either case: * Performance is going to be, um, sub-optimal. * If the number in record_key is anything other than left-aligned, the comparison will fail. Good luck, -- Carsten Haese http://informixdb.sourceforge.net