Oracle error ORA-600 and ORA-1722 using WITH Statement

Hi Experts,
Currently there's an statement using WITH clause which SOMETIMES (only) will cause
ORA-600 [qks3tassert:1] can occur when using a  WITH clause
OR
ORA-1722 Invalid Number

May i know that why would this happend? My oracle version already patch until 9.2.0.7 and this error only happend once a while. (not everytimes)

My sql statement as below
With app As (
     Select  *	From tablea a
     Inner Join tableb b On a.serno = b.serno 
)
Select *
From  (
   Select col1, col2, serno
   From app Where newvalue =1 And oldvalue =0
) aNew
Inner Join (
   Select col1, col2, serno
  From app
  Where newvalue =0 And oldvalue = 1
) aOld On aNew.serno= aOld.serno
Inner Join tablec c On c.Column2 = aNew.serno

Open in new window

LVL 11
yuchingAsked:
Who is Participating?
 
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
Both the errors listed above comes to a single origin. Its with the Numeric comparison in that query. Either newvalue and oldvalue is a character or some other columns involved in joins are not of the same datatype.

So make sure that joining columns are of the same datatype or convert it into a common format.

Try out this modified code:

to_number(newvalue, '9') =1 And to_number(oldvalue, '9') = 0

or Where newvalue ='1' And oldvalue ='0'

And check out whether serno in tablea and tableb are of the same datatype
With app As (
     Select  *  From tablea a
     Inner Join tableb b On a.serno = b.serno 
)
Select *
From  (
   Select col1, col2, serno
   From app Where to_number(newvalue, '9') =1 And to_number(oldvalue, '9') =0
) aNew
Inner Join (
   Select col1, col2, serno
  From app
  Where to_number(newvalue, '9') =0 And to_number(oldvalue, '9') = 1
) aOld On aNew.serno= aOld.serno
Inner Join tablec c On c.Column2 = aNew.serno

Open in new window

0
 
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
The error is because in your CTE app, you have two columns with the same name serno which is available from both tablea and tableb.

So kindly specify the column names instead of * over here:

With app As (
     Select  *  From tablea a
     Inner Join tableb b On a.serno = b.serno
)

Once you change this, you wont face any issues in your query.
0
 
yuchingAuthor Commented:
Hi rrjegan17, i already specify the column name instead of * but still facing the same issue
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
yuchingAuthor Commented:
but how to explain the query works in some of the machine but not in other machine with the same settings?

and is the ORA-600 [qks3tassert:1] can occur when using a  WITH clause also cause by this datatype problem?
0
 
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
Error ORA-600 is a known issue in Oracle..

http://www.mydatabasesupport.com/oracle-ora-00600-known-issues-and-solutions.html
http://askdba.org/weblog/?p=106


You have few solutions available for that and check this out with Network peoples over there..
0
 
yuchingAuthor Commented:
Thanks. it was partially solver for ORA1722
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.