Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: different implicit casting behaviour

Re: different implicit casting behaviour

From: Tony <andrewst_at_onetel.net.uk>
Date: 13 Jul 2004 03:50:45 -0700
Message-ID: <c0e3f26e.0407130250.3813155f@posting.google.com>


rooty_hill2002_at_yahoo.com.au (Gary) wrote in message news:<171bd226.0407121517.56b453ff_at_posting.google.com>...
> 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.

Please elaborate on "doesn't work" - do you mean it returned no row when there was indeed a row for '1028','0003', or that it raised an error? You could certainly get "ORA-01722: invalid number" if the table contained any rows where the implicit TO_NUMBER would fail. Received on Tue Jul 13 2004 - 05:50:45 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US