开发者

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 :)

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜