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.

The original worked because the implicit conversion gets applied to the CHAR column to convert it to a number. So the query is equivalent to:

SELECT cat_type_name FROM loan_product
WHERE TO_NUMBER(acct_type) = 1028
AND TO_NUMBER(int_cat) = 3

... which works when acct_type = '1028' and int_cat is any of '3', '03', '003', '0003'. But of course it can't make use of any index on those columns. Received on Mon Jul 12 2004 - 08:20:24 CDT