How to convert Informix char to decimal(15, 5) in a join?

I am new to Informix and I would like to know how I can convert an Informix char to decimal(15,5) ( need to join a char column to a decimal (15,5) column ). In Sybase, I know I would use the convert() function, but I would like to know the equivalent function in Informix. Would appreciate if someone can give me a hand soon... Sent via Deja.com http://www.deja.com/ Share what you know. Learn what you don't.

pipi9@hotmail.com wrote: > I am new to Informix and I would like to know how I can convert an > Informix char to decimal(15,5) ( need to join a char column to a > decimal (15,5) column ). In Sybase, I know I would use the convert() > function, but I would like to know the equivalent function in > Informix. Informix will do a conversion for you automatically. SELECT * FROM Table1, Table2 WHERE Table1.dec_15_5 = Table2.char_col; -- Jonathan Leffler (jleffler@informix.com, jleffler@earthlink.net) Guardian of DBD::Informix v0.60 -- see http://www.perl.com/CPAN #include <disclaimer.h>

In article <375CCD91.3E2A@earthlink.net>, jleffler@earthlink.net wrote: > pipi9@hotmail.com wrote: > > I am new to Informix and I would like to know how I can convert an > > Informix char to decimal(15,5) ( need to join a char column to a > > decimal (15,5) column ). In Sybase, I know I would use the convert() > > function, but I would like to know the equivalent function in > > Informix. > > Informix will do a conversion for you automatically. > > SELECT * FROM Table1, Table2 WHERE Table1.dec_15_5 = Table2.char_col;> > -- > Jonathan Leffler (jleffler@informix.com, jleffler@earthlink.net) > Guardian of DBD::Informix v0.60 -- see http://www.perl.com/CPAN > #include <disclaimer.h> > > Thanks for getting back to me! Actually, I have tried your way but it is giving me a "1213: Character to numeric conversion error". I am using Informix 7.30 I have also tried using substr() ( I am not even sure if this is a built-in Informix function or a function that the previous developer wrote ) to convert the decimal column to string but it ended up joining a "111" to "111.000" and no rows are returned. That's why I am looking for a way to convert the char column to decimal or to integer instead... Sent via Deja.com http://www.deja.com/ Share what you know. Learn what you don't.

pipi9@hotmail.com wrote: > > I am new to Informix and I would like to know how I can convert an > Informix char to decimal(15,5) ( need to join a char column to a decimal > (15,5) column ). In Sybase, I know I would use the convert() function, > but I would like to know the equivalent function in Informix. Informix will perform all reasonable conversions for column comparison during joins on the fly with no CONVERT() function. So if you have: create table_1 ( char_key char(15), ... ); create table_2 ( ... dec_value decimal(15,5), ... ); Or some such, as long as char_key contains only numeric data then it is perfectly valid to do: SELECT * FROM table_1, table_2 WHERE dec_val = key_val; The requirement that char_key only contain numeric characters is because the optimizer will normally convert the char column to a decimal(15,5) for comparison purposes because a) the decimal comparison is 2x faster than the string compare and b) it avoids formatting difficulties so that if char_key contains "25" it will match a row with dec_val containing 25.00000. Art S. Kagel

pipi9@hotmail.com wrote: > > In article <375CCD91.3E2A@earthlink.net>, > jleffler@earthlink.net wrote: > > pipi9@hotmail.com wrote: > > > I am new to Informix and I would like to know how I can convert an > > > Informix char to decimal(15,5) ( need to join a char column to a > > > decimal (15,5) column ). In Sybase, I know I would use the convert() > > > function, but I would like to know the equivalent function in > > > Informix. > > > > Informix will do a conversion for you automatically. > > > > SELECT * FROM Table1, Table2 WHERE Table1.dec_15_5 = Table2.char_col;> > > > -- > > Jonathan Leffler (jleffler@informix.com, jleffler@earthlink.net) > > Guardian of DBD::Informix v0.60 -- see http://www.perl.com/CPAN > > #include <disclaimer.h> > > > > > > Thanks for getting back to me! > > Actually, I have tried your way but it is giving me a "1213: Character > to numeric conversion error". I am using Informix 7.30 > > I have also tried using substr() ( I am not even sure if this is a > built-in Informix function or a function that the previous developer > wrote ) to convert the decimal column to string but it ended up joining > a "111" to "111.000" and no rows are returned. That's why I am looking > for a way to convert the char column to decimal or to integer > instead... You have some rows with non-numeric characters in them which is why you are getting -1213, numeric conversion error. If you have no rows with alpha in them there may be rows with all spaces or with NULLS try adding a filter to remove those from the comparison. Art S. Kagel