Hey all,
I'm building a mapping to fill a bridge map for a raged hierarchy. I think it should be good to go, but when I run it, the process fails with the error "ORA-00918 column ambiguously defined". I took a look at the code and can pinpoint exactly what is going awry, but I'm not sure how to fix it. The mapping is using the SCD type 2 IKM and fails on the "Insert flow into I$ table" step. The Code is doing the following sub-query:
(
SELECT PROGRAM_D_CHILD.DIMENSION_KEY FTVPROG_COAS_CODE ,
PROGRAM_D_CHILD.COAS_CODE FTVPROG_COAS_CODE ,
PROGRAM_D_CHILD.PROGRAM_CODE FTVPROG_PROG_CODE ,
FTVPROG_CHILD_1.FTVPROG_COAS_CODE FTVPROG_COAS_CODE_3 ,
FTVPROG_CHILD_1.FTVPROG_PROG_CODE FTVPROG_PROG_CODE_3 ,
FTVPROG_CHILD_1.FTVPROG_EFF_DATE FTVPROG_EFF_DATE_2 ,
FTVPROG_CHILD_1.FTVPROG_PROG_CODE_PRED FTVPROG_PROG_CODE_PRED_3 ,
FTVPROG_GRANDCHILD_1.FTVPROG_COAS_CODE FTVPROG_COAS_CODE_4 ,
FTVPROG_GRANDCHILD_1.FTVPROG_PROG_CODE FTVPROG_PROG_CODE_4 ,
FTVPROG_GRANDCHILD_1.FTVPROG_PROG_CODE_PRED FTVPROG_PROG_CODE_PRED_4 ,
PROGRAM_D_PARENT.DIMENSION_KEY FTVPROG_COAS_CODE ,
PROGRAM_D_PARENT.COAS_CODE COAS_CODE ,
PROGRAM_D_PARENT.PROGRAM_CODE PROGRAM_CODE ,
FTVPROG_PARENT.FTVPROG_COAS_CODE FTVPROG_COAS_CODE_2 ,
FTVPROG_PARENT.FTVPROG_PROG_CODE FTVPROG_PROG_CODE_2 ,
FTVPROG_PARENT.FTVPROG_PROG_CODE_PRED FTVPROG_PROG_CODE_PRED_2
FROM ODI_DEV.PROGRAM_D PROGRAM_D_CHILD ,
(
SELECT FTVPROG_GRANDCHILD.FTVPROG_COAS_CODE FTVPROG_COAS_CODE ,
FTVPROG_GRANDCHILD.FTVPROG_PROG_CODE FTVPROG_PROG_CODE ,
FTVPROG_GRANDCHILD.FTVPROG_PROG_CODE_PRED FTVPROG_PROG_CODE_PRED
FROM FIMSMGR.FTVPROG@pub_wouprd FTVPROG_GRANDCHILD
WHERE FTVPROG_GRANDCHILD.FTVPROG_NCHG_DATE = to_date(20991231, 'YYYYMMDD')
) FTVPROG_GRANDCHILD_1 ,
(
SELECT FTVPROG_CHILD.FTVPROG_COAS_CODE FTVPROG_COAS_CODE ,
FTVPROG_CHILD.FTVPROG_PROG_CODE FTVPROG_PROG_CODE ,
FTVPROG_CHILD.FTVPROG_EFF_DATE FTVPROG_EFF_DATE ,
FTVPROG_CHILD.FTVPROG_PROG_CODE_PRED FTVPROG_PROG_CODE_PRED
FROM FIMSMGR.FTVPROG@pub_wouprd FTVPROG_CHILD
WHERE FTVPROG_CHILD.FTVPROG_NCHG_DATE = to_date(20991231, 'YYYYMMDD')
) FTVPROG_CHILD_1 ,
ODI_DEV.PROGRAM_D PROGRAM_D_PARENT ,
FIMSMGR.FTVPROG@pub_wouprd FTVPROG_PARENT
WHERE FTVPROG_CHILD_1.FTVPROG_COAS_CODE = FTVPROG_PARENT.FTVPROG_COAS_CODE AND
FTVPROG_CHILD_1.FTVPROG_PROG_CODE_PRED = FTVPROG_PARENT.FTVPROG_PROG_CODE AND
PROGRAM_D_CHILD.COAS_CODE = FTVPROG_CHILD_1.FTVPROG_COAS_CODE AND
PROGRAM_D_CHILD.PROGRAM_CODE = FTVPROG_CHILD_1.FTVPROG_PROG_CODE AND
FTVPROG_GRANDCHILD_1.FTVPROG_COAS_CODE = FTVPROG_CHILD_1.FTVPROG_COAS_CODE AND
FTVPROG_GRANDCHILD_1.FTVPROG_PROG_CODE_PRED = FTVPROG_CHILD_1.FTVPROG_PROG_CODE AND
PROGRAM_D_PARENT.COAS_CODE = FTVPROG_PARENT.FTVPROG_COAS_CODE AND
PROGRAM_D_PARENT.PROGRAM_CODE = FTVPROG_PARENT.FTVPROG_PROG_CODE AND
FTVPROG_PARENT.FTVPROG_NCHG_DATE = to_date(20991231, 'YYYYMMDD')
) INLINE_VIEW
I've emboldened the problem lines. As you can see, ODI is aliasing three different columns to the same name, "FTVPROG_COAS_CODE". This means that if elsewhere in the code something does a SELECT INLINE_VIEW.FTVPROG_COAS_CODE, they will not know which column to grab, thus the error. Does anyone know why ODI would be naming these columns with the same name, or how to fix the issue?
Thanks,
Richard