Getting ORA-01722 error on execution of following query
A query to be used in my implemetation is giving me ORA-01722 error. Following is the query.
select ao.account_oid,ao.website_oid,ob.oid,ob.status_code,pi.oid,pi.render_status,ao.invoiced_date
from acct_order ao,order_basket ob, order_line_item oli,order_line_item_xml olix,project_info pi
where ao.oid = ob.account_order_oid and
ob.oid = oli.order_basket_oid and
oli.oid = olix.order_line_item_oid and
pi.oid = olix.xml_value and
olix.xml_key_code='PROJECT_INFO_OID' and
pi.oid = 10000450011; // this is not working
Note:"olix.xml_value" may contain string values also.
I modified it by replacing "=" operator with "in" operator and tried,even then it failed. But if the no of values to the "in" operator is more than 1, then its working.
select ao.account_oid,ao.website_oid,ob.oid,ob.status_code,pi.oid,pi.render_status,ao.invoiced_date
from acct_order ao,order_basket ob, order_line_item oli,order_line_item_xml olix,project_info pi
where ao.oid = ob.account_order_oid and
ob.oid = oli.order_basket_oid and
oli.oid = olix.order_line_item_oid and
pi.oid = olix.xml_value and
olix.xml_key_code='PROJECT_INFO_OI开发者_高级运维D' and
pi.oid in (10000450011,10000460011); // This is working. One argument to "in" operator is not working.
So I took the explain on SQLDeveloper,but unable to understand it. Can some one help me figure out the issue.
An ORA-01722 error occurs when an attempt is made to convert a character string into a number, and the string cannot be converted into a valid number. Valid numbers contain the digits '0' thru '9', with possibly one decimal point, a sign (+ or -) at the beginning or end of the string, or an 'E' or 'e' (if it is a floating point number in scientific notation). All other characters are forbidden.
What is the type of pi.oid????????? is it string or a number. Probably if it is a string it can not be converted into a number (it means it have some forbidden characters).
again if pi.oid is a number your are doing something like this
pi.oid = olix.xml_value
which is ambigous becuase xml_value is suppose to be something like string. look through your query carefully and try to remove the ambiguities related to string and numbers. your problem will be solved :)
精彩评论