Home » SQL & PL/SQL » SQL & PL/SQL » ORA-1722 "inavlid number" occured in case of valid number.
ORA-1722 "inavlid number" occured in case of valid number. [message #201853] Tue, 07 November 2006 01:39 Go to next message
deepayan
Messages: 51
Registered: December 2005
Member
Dear All,

When I'm running im getting the error ORA-1722 invalid number. But I dont have any explanation for that.

select COUNT(DISTINCT aia.INVOICE_ID)
from ap_invoices_all aia,
ap_invoice_distributions_all aid,
hr_operating_units hou
where aia.invoice_id = aid.invoice_id and HOU.SET_OF_BOOKS_ID = 85 AND
((NVL(AID.MATCH_STATUS_FLAG, 'N') <> 'A' OR
(EXISTS (SELECT 'x'
FROM AP_HOLDS_ALL AHA
WHERE ((AHA.INVOICE_ID = AIA.INVOICE_ID) AND
AHA.RELEASE_REASON IS NULL))))) AND
AIA.ORG_ID = aid.org_id and aia.org_id = hou.organization_id
group by aia.ORG_ID

Any ideas regarding this will be highly appreciated.
The description of the tables are in the attached excel..
Thanks in advance...
-----------------
Deepayan..
Re: ORA-1722 "inavlid number" occured in case of valid number. [message #201856 is a reply to message #201853] Tue, 07 November 2006 01:57 Go to previous messageGo to next message
Littlefoot
Messages: 20894
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Sorry, but I don't open potentially dangerous files from unknown sources (including XLS ones).

As for invalid number error, check whether columns used in the WHERE clause are numbers. This one might be a problem:

HOU.SET_OF_BOOKS_ID = 85

If this column is of a CHARACTER data type, 85 should be enclosed into single quotes.
Re: ORA-1722 "inavlid number" occured in case of valid number. [message #201879 is a reply to message #201853] Tue, 07 November 2006 03:09 Go to previous messageGo to next message
deepayan
Messages: 51
Registered: December 2005
Member
Dear littlefoot,

I found the erronious part of the where clause is
"aia.org_id = hou.organization_id" . If I omit this ,query is running fine. Now org_id in aia & organaization_id in hou both having datatype number(15).So cant find the reason for this peculiar error.

..............
Re: ORA-1722 "inavlid number" occured in case of valid number. [message #201891 is a reply to message #201879] Tue, 07 November 2006 04:20 Go to previous messageGo to next message
Littlefoot
Messages: 20894
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
This is what Oracle says about it:
Oracle
ORA-01722 invalid number

Cause: The attempted conversion of a character string to a number failed because the character string was not a valid numeric literal. Only numeric fields or character fields containing numeric data may be used in arithmetic functions or expressions. Only numeric fields may be added to or subtracted from dates.

Action: Check the character strings in the function or expression. Check that they contain only numbers, a sign, a decimal point, and the character "E" or "e" and retry the operation.

What would this return: a number or an error:

SELECT COUNT(*) FROM ap_invoices
WHERE TO_NUMBER(org_id) = org_id;

SELECT COUNT(*) FROM hr_operating_units
WHERE TO_NUMBER(organization_id) = organization_id;

Re: ORA-1722 "inavlid number" occured in case of valid number. [message #201896 is a reply to message #201891] Tue, 07 November 2006 04:35 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
The erroneous column does NOT necessarily be one of the two mentioned. It can still be HOU.SET_OF_BOOKS_ID.
Removing the condition probably changed the execution plan, maybe causing some records with non-numeric HOU.SET_OF_BOOKS_ID to be filtered out before coming to the HOU.SET_OF_BOOKS_ID=85 bit.
Re: ORA-1722 "inavlid number" occured in case of valid number. [message #201899 is a reply to message #201853] Tue, 07 November 2006 04:44 Go to previous messageGo to next message
deepayan
Messages: 51
Registered: December 2005
Member
Dear Littlefoot,

As per your instruction when im running

select COUNT(DISTINCT aia.INVOICE_ID)
from ap_invoices_all aia,
ap_invoice_distributions_all aid,
hr_operating_units hou
where aia.invoice_id = aid.invoice_id and HOU.SET_OF_BOOKS_ID = '85' AND
((NVL(AID.MATCH_STATUS_FLAG, 'N') <> 'A' OR
(EXISTS (SELECT 'x'
FROM AP_HOLDS_ALL AHA
WHERE ((AHA.INVOICE_ID = AIA.INVOICE_ID) AND
AHA.RELEASE_REASON IS NULL))))) AND
AIA.ORG_ID = aid.org_id and aia.org_id = hou.organization_id
group by aia.ORG_ID

is running fine.

but this query

select COUNT(DISTINCT aia.INVOICE_ID)
from ap_invoices_all aia,
ap_invoice_distributions_all aid,
hr_operating_units hou
where aia.invoice_id = aid.invoice_id and HOU.SET_OF_BOOKS_ID = 85 AND
((NVL(AID.MATCH_STATUS_FLAG, 'N') <> 'A' OR
(EXISTS (SELECT 'x'
FROM AP_HOLDS_ALL AHA
WHERE ((AHA.INVOICE_ID = AIA.INVOICE_ID) AND
AHA.RELEASE_REASON IS NULL))))) AND
AIA.ORG_ID = aid.org_id /*and aia.org_id = hou.organization_id*/
group by aia.ORG_ID

is running fine also..

If the problem is with HOU.SET_OF_BOOKS_ID = 85 then why the second query is running..

Can u tell where I can get some idea why its happening so..
-------------

Deepayan
Re: ORA-1722 "inavlid number" occured in case of valid number. [message #201916 is a reply to message #201899] Tue, 07 November 2006 06:24 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
See my previous reply for a possible explanation.
Execute the following to find conflicting values:
select HOU.SET_OF_BOOKS_ID
from   hr_operating_units hou
where  length(trim(translate(HOU.SET_OF_BOOKS_ID, '0123456789', '          '))) != 0


What is the result of this?
Re: ORA-1722 "inavlid number" occured in case of valid number. [message #201932 is a reply to message #201899] Tue, 07 November 2006 07:52 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
And while you're at it, DESCribe the following tables:

ap_invoices_all
ap_invoice_distributions_all
hr_operating_units
AP_HOLDS_ALL
Re: ORA-1722 "inavlid number" occured in case of valid number. [message #202021 is a reply to message #201853] Tue, 07 November 2006 21:58 Go to previous messageGo to next message
deepayan
Messages: 51
Registered: December 2005
Member
Dear Frank,

NO rows are being selected from the query given by you.. It seems no records are erronious.

So, What can we conclude......???
Re: ORA-1722 "inavlid number" occured in case of valid number. [message #202048 is a reply to message #202021] Wed, 08 November 2006 00:33 Go to previous messageGo to next message
Littlefoot
Messages: 20894
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
It means that you entered into the Twilight Zone ./fa/1706/0/
Re: ORA-1722 "inavlid number" occured in case of valid number. [message #202053 is a reply to message #202021] Wed, 08 November 2006 00:49 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
hm..
And what about joy_divisions request?
Re: ORA-1722 "inavlid number" occured in case of valid number. [message #202144 is a reply to message #201853] Wed, 08 November 2006 06:50 Go to previous messageGo to next message
deepayan
Messages: 51
Registered: December 2005
Member
Dear frank,
The description of the tables are already in the attached xls..
Re: ORA-1722 "inavlid number" occured in case of valid number. [message #202178 is a reply to message #202144] Wed, 08 November 2006 09:58 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
I'm with Littlefoot, I won't open a .xls file from a stranger.
Re: ORA-1722 "inavlid number" occured in case of valid number. [message #202182 is a reply to message #201853] Wed, 08 November 2006 10:56 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
the message is not misleading, it is what it says.

One of your character columns is being converted to a number during execution of the query, but at least one entry is not a number.

Point about different plans visiting different rows in different orders and thus filtering rows at different times is a good one. A plan change could cause this to go away.

But the fact remains, when you get the error it is a character column that is the culprit, so don't bother looking at the number fields; they ain't where your resolution is.

Paste in table describes and query plans for failing query and I'll look at it. don't ask me to open your xls file, paste the information into this discussion directly.

Good luck, Kevin
Re: ORA-1722 "inavlid number" occured in case of valid number. [message #202187 is a reply to message #202182] Wed, 08 November 2006 11:42 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
114 topic views, 2 downloads of your xls.
nuff said I'd say..
Previous Topic: String IN
Next Topic: A question about roles
Goto Forum:
  


Current Time: Mon Dec 05 08:46:02 CST 2016

Total time taken to generate the page: 0.05563 seconds