Thank you again for replying well, i don't have access for this document.
I did simulations for the same situations I am facing in the customer site.
Task Name 1: Insert new rows Task Name 2: IKM Oracle Insert Task Name 3: Load EMP_TEMP
INSERT /*+ APPEND PARALLEL */ INTO SCOTT.EMP_TEMP ( EMPNO , ENAME , JOB , MGR , HIREDATE , SAL , COMM , DEPTNO ) SELECT EMP.EMPNO , EMP.ENAME , EMP.JOB , EMP.MGR , EMP.HIREDATE , EMP.SAL , EMP.COMM , EMP.DEPTNO FROM SCOTT.DEPT_TEMP DEPT_TEMP1 INNER JOIN ( select EMPNO,ENAME ,JOB,MGR,HIREDATE,SAL ,COMM,DEPTNO from scott.emp where DEPTNO = ( select max(deptno) from dept ) ) EMP ON DEPT_TEMP1.DEPTNO = EMP.EMPNO
Task Name 1: Insert new rows Task Name 2: IKM Oracle Insert Task Name 3: Load EMP_TEMP_TEST1
INSERT /*+ APPEND PARALLEL */ INTO SCOTT.EMP_TEMP_TEST1 ( EMPNO , ENAME , JOB , SAL , DEPTNO ) SELECT EMP_1.EMPNO , EMP_1.ENAME , EMP_1.JOB , EMP_1.SAL , EMP_1.DEPTNO FROM ( select EMPNO,ENAME ,JOB,SAL ,DEPTNO from scott.emp where DEPTNO = ( select max(deptno) from dept ) ) EMP INNER JOIN SCOTT.DEPT_TEMP DEPT_TEMP ON EMP_1.DEPTNO = DEPT_TEMP.DEPTNO
As you can see that alias here is generated wrongly and casuing this error:
ODI-1228: Task Insert new rows-IKM Oracle Insert-Load EMP_TEMP_TEST1 fails on the target connection DB-Scott.
Caused By: java.sql.SQLSyntaxErrorException: ORA-00904: "EMP_1"."DEPTNO": invalid identifier
How ODI 12c resolve alias Error in such cases? Is it a bug also in 12c where I was reading that it is only in ODI 11
If your script in Custom Template is:
select MY_COL1, MY_COL2
where MY_COL1 = 1234
then make it:
( select MY_COL1, MY_COL2
where MY_COL1 = 1234 ) MY_TABLE
I tried but it did not work...
I am sorry I did not get back to update the status for this Post.
well, on the customer side. The first time it was not working and I just check the next week and it is now working without this error. I don't know what happened on their side. Maybe they install batch.
In my personal Laptop, I have the same problem.
well, it works by the customer side which is important for me.
If you consider that your question was replied please mark this post as Answered, in order to help also other customers.