Re: different implicit casting behaviour

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 -- Received on Mon Jul 12 2004 - 09:01:09 CDT