?
Solved

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

Posted on 2009-02-19
6
Medium Priority
?
1,617 Views
Last Modified: 2013-12-19
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

0
Comment
Question by:yuching
  • 3
  • 3
6 Comments
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 23679628
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
 
LVL 11

Author Comment

by:yuching
ID: 23688217
Hi rrjegan17, i already specify the column name instead of * but still facing the same issue
0
 
LVL 57

Accepted Solution

by:
Raja Jegan R earned 1500 total points
ID: 23688385
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 11

Author Comment

by:yuching
ID: 23688436
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
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 23688461
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
 
LVL 11

Author Closing Comment

by:yuching
ID: 31548680
Thanks. it was partially solver for ORA1722
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question