在ODI的流检查中,有一个步骤是用于检查FK的。
原来KM的写法如下:
(
<%=odiRef.getFKColList("",odiRef.getTargetTable("TABLE_ALIAS")+".[COL_NAME] ", ",\n\t\t", "")%>
) not in (
select <%=odiRef.getFKColList("", "[PK_COL_NAME]", ",\n\t\t\t", "")%>
from <%=odiRef.getTable("L", "FK_PK_TABLE_NAME", "A")%>
)
and (
<%=odiRef.getFKColList("",odiRef.getTargetTable("TABLE_ALIAS")+".[COL_NAME] is not null ", "\n\tand\t", "")%>
)
从代码上看,FK的检查前提是外键的值不为空。
但在本次项目中,却在特定情况下,外键的值是取不到,为空的。因此上述KM的写法不能满足实际情况,特做了以下调整:
from <%=odiRef.getTable("L", "CT_NAME", "A")%> <%=odiRef.getTargetTable("TABLE_ALIAS")%>
where NOT EXISTS (
select 'X'
from <%=odiRef.getTable("L", "FK_PK_TABLE_NAME", "A")%>
where <%=odiRef.getFKColList("", "[PK_COL_NAME]="+odiRef.getTargetTable("TABLE_ALIAS")+".[COL_NAME] ", ",\n\t\t\t", "")%>
)
<%=odiRef.getFilter()%>
把not in 变换成not exists,当外表为空时,也是能检查出来放到E$表中的。
看来每次做项目,都需要完整的看项目需求,仔细核对KM是否能满足实际情况,不要轻信标准化哟~~