Re: Another query problem in 9i

Well, if what you said is true then this query would also fail:

select distinct to_number(Curationdetails.text) from Curationdetails, termlist
where context = 3001817 and

        CurationDetails.Text=to_char(termlist.TermID)
and     Curationdetails.text='3357971';

But it did not fail in 9i. So I don't think it is data issue in this particular case.

Guang

"nobody" <nobody_at_nowhere.com> wrote in message news:<aTFlb.17823$h61.11522_at_news01.bloor.is.net.cable.rogers.com>...
> your to_number(Curationdetails.text) fails because of the data in your 9i
> table not the version.
>
> "G M" <zlmei_at_hotmail.com> wrote in message
> news:50a5e6b6.0310221616.482b909e_at_posting.google.com...
> > Hi:
> >
> > I have another problem that I have been trying to solve. I have this query
> > that works perfectly in 8i, but does not work in 9i. The query is
> >
> > -- not working in 9i but works in 8i:
> > select distinct arc.TermID, arc.ParentTermID
> > from arc
> > connect by prior arc.ParentTermID=arc.TermID
> > start with arc.TermID in
> > (select distinct to_number(Curationdetails.text)
> > from Curationdetails, termlist
> > where context = 3001817 and
> >
> CurationDetails.Text=to_char(termlist.TermID)
> > and Curationdetails.text='3357971'
> > );
> >
> > The error I got from 9i is:
> >
> > ERROR at line 5:
> > ORA-01722: invalid number
> >
> >
> > However, The following sqls work both in 8i and 9i:
> >
> >
> > select distinct to_number(Curationdetails.text)
> > from Curationdetails, termlist
> > where context = 3001817 and
> > CurationDetails.Text=to_char(termlist.TermID)
> > and Curationdetails.text='3357971';
> >
> >
> > select distinct arc.TermID, arc.ParentTermID
> > from arc
> > where arc.TermID in
> > (select distinct to_number(Curationdetails.text)
> > from Curationdetails, termlist
> > where context = 3001817 and
> >
> CurationDetails.Text=to_char(termlist.TermID)
> > and Curationdetails.text='3357971'
> > );
> >
> >
> > select distinct arc.TermID, arc.ParentTermID
> > from arc
> > connect by prior arc.ParentTermID=arc.TermID
> > start with arc.TermID in (to_number('3357971'));
> >
> > So I guess I am a bit confused. Does anyone know why the orginal query
> > failed in 9i?
> >
> > TIA.
> >
> > Guang
Received on Thu Oct 23 2003 - 09:12:29 CDT