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 Wed Oct 22 2003 - 20:09:26 CDT
