ODI Insert PK Errors using substitution methods

Insert PK Errors

This task inserts into the error (E$) table the errors detected while checking a primary key. This command always runs, has the Primary Key checkbox active and has Log Counter set to Error to count these records as errors.

Note:

When using a CKM to perform flow control from an interface, you can define the maximum number of errors allowed. This number is compared to the total number of records returned by every command in the CKM of which the Log Counter is set to Error.

Note the use of the getCollist method to insert into the error table the whole record being checked and the use of the getPK and getInfo method to retrieve contextual information.

Command on Target (Oracle)

insert into <%=odiRef.getTable("L","ERR_NAME", "W")%>
(
        ODI_PK,
        ODI_SESS_NO,
        ODI_ROW_ID,
        ODI_ERR_TYPE,
        ODI_ERR_MESS,
        ODI_ORIGIN,
        ODI_CHECK_DATE,
        ODI_CONS_NAME,
        ODI_CONS_TYPE,
        <%=odiRef.getColList("", "[COL_NAME]", ",\n\t", "", "MAP")%>
)
select  SYS_GUID(),
        <%=odiRef.getSession("SESS_NO")%>, 
        rowid,
        '<%=odiRef.getInfo("CT_ERR_TYPE")%>', 
        '<%=odiRef.getPK("MESS")%>',
        '<%=odiRef.getInfo("CT_ORIGIN")%>',
        <%=odiRef.getInfo("DEST_DATE_FCT")%>,
        '<%=odiRef.getPK("KEY_NAME")%>',
        'PK',   
        <%=odiRef.getColList("", odiRef.getTargetTable("TABLE_ALIAS")+".[COL_NAME]", ",\n\t", "", "MAP")%>
from     <%=odiRef.getTable("L", "CT_NAME", "A")%> <%=odiRef.getTargetTable("TABLE_ALIAS")%>
where   exists  (
                select  <%=odiRef.getColList("", "SUB.[COL_NAME]", ",\n\t\t\t", "", "PK")%>
                from    <%=odiRef.getTable("L","CT_NAME","A")%> SUB
                where   <%=odiRef.getColList("", "SUB.[COL_NAME]="+odiRef.getTargetTable("TABLE_ALIAS")+".[COL_NAME]", "\n\t\t\tand ", "", "PK")%>
                group by        <%=odiRef.getColList("", "SUB.[COL_NAME]", ",\n\t\t\t", "", "PK")%>            having  count(1) > 1
                )
<%=odiRef.getFilter()%>