andrewst_at_onetel.net.uk (Tony) wrote in message news:<c0e3f26e.0407120520.233eb5bd_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.
>
> 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.
Tony, thanks for this. Of course the code was bad, but the question
remains that why it doesn't work in use acceptance testing region in
which the database is identical.
Received on Mon Jul 12 2004 - 18:17:24 CDT
