Re: different implicit casting behaviour

Mark.Powell_at_eds.com (Mark D Powell) wrote in message news:<2687bb95.0407120601.787d5138_at_posting.google.com>...
> rooty_hill2002_at_yahoo.com.au (Gary) wrote in message news:<171bd226.0407112252.6d16b42_at_posting.google.com>...
> > All,
> >
> > This was from one of my programmers. She claimed one of her statements
> > was working in system testing region, but after being promoted to user
> > acceptance testing region it stopped working, so it became mu problem.
> >
> > She uses the following statement to simply retrieve one field
> > "cat_type_name" from table "loan_product":
> >
> > SELECT cat_type_name FROM loan_product
> > WHERE acct_type = 1028
> > AND int_cat = 003
> >
> > Unfortunately, both acct_type and int_cat are defined as CHAR(4) and
> > NOT NULL. So apparently, this statement should not work at all.
> > However, in system testing region it DID work. I rejected to look any
> > further unless she change the statement to:
> > SELECT cat_type_name FROM loan_product
> > WHERE acct_type = '1028'
> > AND int_cat ='0003'
> >
> > She did and her application is working happily in user testing region
> > now. However, I still can't answer the question why it worked in
> > system testing region but doesn't work in user acceptance region. I
> > compared the table in two regions, couldn't find any difference. Looks
> > like the implicit casting behaves differently in these two databases.
> >
> > Any idea about what I should check to find out the reason.
> >
> > Sorry guys, I am a newbie.
> >
> > Cheers,
> >
> > Gary
>
> Gary, the answer to why the statement worked in one test environment
> and did not work in the other is most like strongly tied to the Oracle
> ERROR message returned by Oracle, which you did not post. I suspect
> the answer is due to the difference in data and/or statistics on the
> tables and that a numeric or value error was being returned because of
> the direction of the implicit conversion from character to number that
> would have resulted from the missing sinle quotes around the character
> fields. The most likely answer based on the data given is "bad code":
> never compare data of different type without explicitly performing a
> data conversion via to_char, to_number, to_date, etc....
>
> IMHO -- Mark D Powell --

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. So I updated the problematic row with
an unique number '9876' (so I can change back to four spaces since I don't want to change the business data just because somebody wants bad coding work).

This time, when I did "select * from loan_product where acct_type = 1028 and int_cat=003", it worked.

Well, now we see how bad the original code was. Oracle has to use to_number() for acct_type and int_cat to come up a number then compare the result with the number she put in the where clause. I have been programmers for about 10 years now, I found I really can't stand this kind of rogue coding any more. I updated my database development standards for the programmers so they will hassle me less in the future.

Thanks for you input.

Cheers,

Gary Received on Mon Jul 12 2004 - 19:30:16 CDT