我收到一个对我来说没有意义的Oracle Invalid Number错误。我理解what this error means但在这种情况下不应该发生。很抱歉这个问题很长,但请耐心等待,以便彻底解释。
我有一个表存储不同来源的ID,有些ID可以包含字母。因此,该列是VARCHAR。
其中一个源有数字ID,我想加入该源:
SELECT *
FROM (
SELECT AGGPROJ_ID -- this column is a VARCHAR
FROM AGG_MATCHES -- this is the table storing the matches
WHERE AGGSRC = 'source_a'
) m
JOIN SOURCE_A a ON a.ID = TO_NUMBER(m.AGGPROJ_ID);
在大多数情况下,这是有效的,但取决于随机事物,例如select子句中的列,如果它使用左连接或内连接等,我将开始看到Invalid Number错误。
我已多次验证AGG_MATCHES中所有条目,其中AGGSRC = 'source_a'在AGGPROJ_ID列中不包含非数字字符:
-- this returns no results
SELECT AGGPROJ_ID
FROM AGG_MATCHES
WHERE AGGSRC = 'source_a' AND REGEXP_LIKE(AGGPROJ_ID, '[^0-9]');
我知道Oracle基本上会在内部重写查询以进行优化。回到第一个SQL示例,我最好的猜测是,根据整个查询的编写方式,在某些情况下,Oracle会尝试在子查询之前执行JOIN。换句话说,它试图将整个AGG_MATCHES表连接到SOURCE_A而不仅仅是子查询返回的子集。如果是这样,那么AGGPROJ_ID列中将包含非数值的行。
有谁知道这是否是导致错误的原因?如果是这个原因,那么我是否要强制Oracle首先执行子查询部分,以便它只尝试加入AGG_MATCHES表的子集?
更多背景:
这显然是一个简化的例子来说明问题。 AGG_MATCHES表用于存储不同来源(即项目)之间的“匹配”。换句话说,它曾经说过sourceA中的项目与sourceB中的项目匹配。
我没有反复编写相同的SQL,而是为我们常用的源创建了视图。我们的想法是拥有一个包含两列的视图,一列用于SourceA,另一列用于SourceB。出于这个原因,我不想在源表的TO_CHAR列上使用ID,因为devs必须记住每次进行连接时都这样做,并且我正在尝试删除代码重复。此外,由于SOURCE_A中的ID是一个数字,我觉得任何存储SOURCE_A.ID的视图都应该继续并将其转换为数字。