这是我的工作代码：
select conitem.name as LANG,
(select rating_short_descr
from hrt_rating_levels_tl
where rating_level_id = items.rating_level_id1 and language = 'US') AS READING,
(select rating_short_descr
from hrt_rating_levels_tl
where rating_level_id = items.rating_level_id2 and language = 'US') AS SPEAKING,
(select rating_short_descr
from hrt_rating_levels_tl
where rating_level_id = items.rating_level_id3 and language = 'US') AS WRITING,
(select rating_short_descr
from hrt_rating_levels_tl
where rating_level_id = items.item_text30_7 and language = 'US') AS SIGN_LANGUAGE
from hrt_profile_items items
inner join hrt_content_types_tl types
on items.content_type_id = types.content_type_id
inner join hrt_profiles_b prof
on items.profile_id = prof.profile_id
inner join per_all_people_f pers
on pers.person_id = prof.person_id
and trunc(sysdate) between pers.effective_start_date and pers.effective_end_date
left outer join hrt_content_items_tl conitem
on items.content_item_id = conitem.content_item_id
and types.language = conitem.language
where
types.content_type_name ='Languages'
and items.date_to IS NULL
and pers.person_number = :person_number -- FINE HERE
order by types.content_type_name, pers.person_number, items.profile_item_id但是，如果我删除注解行( --FINE HERE )
oracle.xdo.servlet.data.DataException: oracle.xdo.servlet.data.DataException: oracle.xdo.servlet.data.DataException: oracle.xdo.XDOException: java.sql.SQLSyntaxErrorException: ORA-01722: invalid number我不明白-这是怎么回事？真是难以置信
更新的代码仍然不起作用
select conitem.name as LANG,
(select rating_short_descr
from hrt_rating_levels_tl
where rating_level_id = items.rating_level_id1 and language = 'US') AS READING,
(select rating_short_descr
from hrt_rating_levels_tl
where rating_level_id = items.rating_level_id2 and language = 'US') AS SPEAKING,
(select rating_short_descr
from hrt_rating_levels_tl
where rating_level_id = items.rating_level_id3 and language = 'US') AS WRITING,
(select rating_short_descr
from hrt_rating_levels_tl
where rating_level_id = items.item_text30_7 and language = 'US') AS SIGN_LANGUAGE
from hrt_profile_items items
inner join hrt_content_types_tl types
on items.content_type_id = types.content_type_id
inner join hrt_profiles_b prof
on to_char(items.profile_id) = to_char(prof.profile_id )
inner join per_all_people_f pers
on to_char(pers.person_id) = to_char(prof.person_id )
--and trunc(sysdate) between pers.effective_start_date and pers.effective_end_date
left outer join hrt_content_items_tl conitem
on items.content_item_id = conitem.content_item_id
and types.language = conitem.language
where
types.content_type_name ='Languages'
and items.date_to IS NULL
order by types.content_type_name, pers.person_number, items.profile_item_id
3条答案
按热度按时间vmdwslir1#
检查查询中所有比较的表中的所有列类型。字符列和数字列之间必须不匹配。这也可能发生在子查询中。
在不了解数据模型的情况下，此比较：
rating_level_id = items.item_text30_7在我看来，这听起来有点可疑，因为它看起来像是将一个id（通常是数字）与一个文本字段（通常是varchar2）进行比较。如果列中的任何值item_text30_7不能隐式转换为一个数字，则会导致此错误。8mmmxcuj2#
当oracle无法自动将字符数据转换为数字数据类型时，比较字符数据类型和数字时，可能会得到ora-1722。
检查联接条件：是否存在字符数据和数字数据的混合？
n1bvdmb63#
正如您所提到的，使用下面的过滤器 predicate ，您的sql可以正常工作：
然而，当你删除这个过滤器时，你会得到：ora-01722:无效的数字
因此，看看您的sql，这就是数据类型不匹配可能发生的地方：
比较以下数据：
pers.person_id以及prof.person_id.以及
trunc(sysdate)介于pers.effective_start_date以及pers.effective_end_date错误也可能来自日期计算。