Re: different implicit casting behaviour

Gary wrote:
>
>
> Mark, I think I found out why.
>
> First of all, I checked the ORA-01722 message and found out that if
> Oracle has problem with converting a string to a number, it will
> report this error. So I think I need to find out on which column it
> gets this problem.
>
> The statement "select * from loan_product where acct_type = 1028 and
> int_cat='0003'" works fine, but "select * from loan_product where
> acct_type ='1028' and int_cat= 003" still doesn't work. This tells me
> we might have some data in field "int_cat" in this table stopping
> Oralce from successfully converting a string to a number.
>
> Then, when I did
> "select int_cat from loan_product", I actually got one row of ' '
> (four spaces). int_cat acutally stands for "Interest Category", how
> the heck it can have four spaces in it, I have no idea.
[...]

You never posted the structure of the table, (or I missed it) but a blind shot in the dark is that int_cat is char(4), which gets automatically and always rightpadded with spaces as soon as the field has a value other than null.

Why you have obviously a character field to hold numerical data is another matter.

HTH Holger Received on Tue Jul 13 2004 - 02:18:54 CDT