OraFAQ Forum: SQL & PL/SQL » ORA-1722 "inavlid number" occured in case of valid number.

Home » SQL & PL/SQL » SQL & PL/SQL » ORA-1722 "inavlid number" occured in case of valid number.

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

ORA-1722 "inavlid number" occured in case of valid number. [message #201853]

Tue, 07 November 2006 01:39

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

Attachment: description of tables.xls
(Size: 33.00KB, Downloaded 1962 times)

Report message to a moderator

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

Littlefoot
Messages: 21823
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.

Report message to a moderator

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

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.

..............

Report message to a moderator

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

Littlefoot
Messages: 21823
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;

Report message to a moderator

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

Frank
Messages: 7901
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.

Report message to a moderator

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

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

Report message to a moderator

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

Frank
Messages: 7901
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?

Report message to a moderator

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

joy_division
Messages: 4963
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

Report message to a moderator

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

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......???

Report message to a moderator

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

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

It means that you entered into the Twilight Zone /forum/fa/1706/0/

/forum/fa/1706/0/

Report message to a moderator

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

Frank
Messages: 7901
Registered: March 2000

Senior Member

hm..
And what about joy_divisions request?

Report message to a moderator

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

deepayan
Messages: 51
Registered: December 2005

Member

Dear frank,
The description of the tables are already in the attached xls..

Report message to a moderator

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

joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA

Senior Member

I'm with Littlefoot, I won't open a .xls file from a stranger.

Report message to a moderator

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

Kevin Meade
Messages: 2103
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

Report message to a moderator

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

Frank
Messages: 7901
Registered: March 2000

Senior Member

114 topic views, 2 downloads of your xls.
nuff said I'd say..

Report message to a moderator

Previous Topic:	String IN
Next Topic:	A question about roles

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Dec 04 19:37:07 CST 2024