I *suspect* that Nuno is correct - if both join columns were of the same
data type (number) then there wouldn't be any problems with the query
and wouldn't be any need to explicit conversion. The fact that either to_number
or to_char solves the problem means that one of the columns involved is
varchar and the other is number (despite the claim that they both are numeric -
this may mean they are *logically* numeric). Implicit conversion is not
guaranteed to convert correctly in all cases since it uses default format for
conversion which may not be suitable for particular data instance. The fact
that the query works on another instance probably means that data types
match there (I doubt that 8.1.7 has different default format for char to
number and/or number to char conversion than that of 8.1.6).
--
Vladimir Zakharychev (bob@dpsp-yes.com) http://www.dpsp-yes.com
Dynamic PSP(tm) - the first true RAD toolkit for Oracle-based internet applications.
All opinions are mine and do not necessarily go in line with those of my employer.
"Heinz Kiosk" <no.spam_at_ntlworld.com> wrote in message
news:fHRc8.123789$H37.15889586_at_news2-win.server.ntlworld.com...
>
> "Nuno Souto" <nsouto_at_optushome.com.au.nospam> wrote in message
> news:3c7375e6.3467294_at_news-vip.optusnet.com.au...
> > Buck Turgidson doodled thusly:
> >
> > >I am running the following code on an 8.1.6 database, and the join on
> > >two numeric fields (cal_year) only works with either a to_number or
> > >to_char function.
> > >
> >
> > Buck:
> >
> > if you take out explicit conversions in predicates, you'll find that
> > Oracle will take default conversions. These can take any direction
> > and/or priority and/or default depending on what version of the
> > software you're talking about. Ie, Oracle will NOT guarantee that
> > without explicit datatype conversions your SQL will always default the
> > same way.
> >
> > Bottom line: always (and I mean *ALWAYS*) make sure that if you have
> > to compare two values of different datatypes, you do an explicit
> > conversion to one of the datatypes. And if one of the values is a
> > column name and the other a constant/variable/bind variable, then
> > always convert the latter. Or else index use will suffer. But I
> > digress...
> I am curious about your response. He said that both of his fields were
> numeric and reading the SQL they both look like plain columns to me. Is the
> implication that you believe he is wrong? You didn't come right out and say
> it. Or do you think his fields are different precision and therefore
> confusing the query in some way? Or something else that I am missing? Is he
> using a standard sample schema that you know the types of?
> Just a bit baffled.
> >
> >
> >
> > Cheers
> > Nuno Souto
> > nsouto_at_optushome.com.au.nospam
>
>
Received on Thu Feb 21 2002 - 03:56:37 CST
