Casting in Informix

Question

In Informix, how can I cast a char(8) type into a money type, so that I can compare it to another money type?

Answer 1

try this -->

select (disb_amt::NUMERIC) disb_amt from tmp_kygrants;

You may be able to compare the amounts as numeric.

Answer 2

'tis been a while since I played around with informix and I don't have a running instance handy at the moment. However, there are two things that can cause a problem here:

1) since it is a char(8) it can contain values that can not be casted to numeric without a bit of 'cleanup'. E.g. "abc". Or "1,234,567.00".
2) Trailing spaces. (char as opposed to varchar).

What informix error do you get on your explicit cast (::money)?

Answer 3

First question - why on earth are you not storing a numeric value in a numeric column? This would make the rest of your question moot. It would also mean that your system will perform better. When you need to store data values, use the obvious type; do not use a string type unless the data is a string.

As already noted, you can use the non-standard Informix cast notation:

SELECT some_column::MONEY FROM WhereEver;

You can also be more careful about the cast type - using MONEY(8,2) for example. You can also use the standard notation:

SELECT CAST(some_column AS MONEY(8,2)) FROM WhereEver;

This assumes you are using IDS 9.x or later -- older products do not support casts at all. However, in general, Informix is pretty good about doing conversions automatically (for example, converting numbers to strings). However, strings are compared lexicographically and not numerically, so a CAST is probably wiser in this context -- but avoiding the need for a cast by using the correct type in the first place is wiser still.