We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you a podcast all about Citrix Workspace, moving to the cloud, and analytics & intelligence. Episode 2 coming soon!Listen Now

x

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

yuching
yuching asked
on
Medium Priority
1,661 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

Comment
Watch Question

Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
CERTIFIED EXPERT
Awarded 2009
Distinguished Expert 2019

Commented:
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.

Author

Commented:
Hi rrjegan17, i already specify the column name instead of * but still facing the same issue
SQL Server DBA & Architect, EE Solution Guide
CERTIFIED EXPERT
Awarded 2009
Distinguished Expert 2019
Commented:
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

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Author

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?
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
CERTIFIED EXPERT
Awarded 2009
Distinguished Expert 2019

Commented:
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..

Author

Commented:
Thanks. it was partially solver for ORA1722
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.