Forum Stats

  • 3,837,943 Users
  • 2,262,310 Discussions
  • 7,900,435 Comments

Discussions

Categories

ODI 12c - Get source column of a target table column

kovar
kovar Member Posts: 46 Red Ribbon
edited May 28, 2018 8:40AM in Data Integrator

Hi everyone,

I was wondering if there is a odi_repo table which stores information of each column on target table its column from source?

odi-problem.png

Sometimes when I'm writing documentation for my mappings I just need a easier way to write down my target table columns, and their sources (with schema).

Thanks in advance!

- Kovar

Tagged:
kovarPavan8u
· Share on TwitterShare on Facebook

Best Answer

Answers

  • Adrian_Popescu-Oracle
    Adrian_Popescu-Oracle RomaniaPosts: 1,984 Employee
    edited Mar 7, 2018 2:38AM Answer ✓

    Hi Kovar,

    The table name is SNP_MAP_EXPR.

    See here ODI repo description:

    Doc 1903225.1 Oracle Data Integrator 11g and 12c Repository Description

    Adrian

    kovarPavan8u
    · Share on TwitterShare on Facebook
  • kovar
    kovar Member Posts: 46 Red Ribbon
    edited Mar 7, 2018 4:13AM

    Hi Adrian,

    thanks a lot. This is exactly what I was looking for.

    - Kovar

    · Share on TwitterShare on Facebook
  • kovar
    kovar Member Posts: 46 Red Ribbon
    edited May 16, 2018 9:38AM

    Hi everyone,

    I'm having trouble with getting expression values of my target table columns. I'm missing some ODI tables in order to join them with SNP_MAP_EXPR to acquire this values, but I'm not sure which one I need. Has anyone been in this situation? Any kind of hint, advice or mini code snippet would help.

    Thanks in advance!

    - Kovar

    · Share on TwitterShare on Facebook
  • Adrian_Popescu-Oracle
    Adrian_Popescu-Oracle RomaniaPosts: 1,984 Employee
    edited May 16, 2018 10:10AM

    Hi,

    Looks like Doc 1903225.1 Oracle Data Integrator 11g and 12c Repository Description

    didn't help after all.

    There is no other document describing ODI repository.

    Adrian

    · Share on TwitterShare on Facebook
  • Adrian_Popescu-Oracle
    Adrian_Popescu-Oracle RomaniaPosts: 1,984 Employee
    edited May 17, 2018 5:33AM

    Hi Kovar,

    You could start by checking the foreign keys setup on table SNP_MAP_EXPR.

    Then foreign keys setup on other tables linked to table SNP_MAP_EXPR.

    And so on.

    Adrian

    · Share on TwitterShare on Facebook
  • kovar
    kovar Member Posts: 46 Red Ribbon
    edited May 28, 2018 8:40AM

    Hi Adrian,

    since I don't have access do all ODI REPO tables (I need to contact db admin so he can grant me permissions on odi repo tables that I need) it's hard for me to know which tables I need and are missing in my query. So far I have this query which is missing column names for each expression. I tried to join it with SNP_COL but then I get for each column name every expression listed.

    For example, this query will generate 19 rows:

    select m.name, t.table_name, me.txt

    from odi_repo.SNP_MAP_EXPR me

    inner join odi_repo.SNP_MAP_EXPR_REF mer on mer.I_OWNER_MAP_EXPR = me.I_MAP_EXPR

    inner join odi_repo.SNP_MAP_CP mc on mer.I_SCOPING_MAP_CP = mc.I_MAP_CP

    inner join odi_repo.SNP_MAP_COMP mcomp on mc.I_OWNER_MAP_COMP = mcomp.I_MAP_COMP

    inner join ODI_REPO.snp_mapping m on m.i_mapping = mcomp.i_owner_mapping 

    inner join ODI_REPO.snp_map_ref mr on mcomp.i_map_ref = mr.i_map_ref

    inner join ODI_REPO.snp_table t on mr.i_ref_id = t.i_table

    where m.name = 'M_MAPPING_NAME'

    If I join SNP_COL with SNP_TABLE I will get 19 x 19 = 361 rows.

    My question is: which ODI REPO table I'm missing so I can list column names for each expression?

    Thanks,

    Kovar

    · Share on TwitterShare on Facebook
This discussion has been closed.