1

I am working on ODI mapping where I am calculating" Min(ID) over parition by(device_num, sys_id) as min_id" in expression component, I used another expression component to filter duplicates using row_number() over partition by (ID) order by(min_id) followed by a filter component "rownum=1" this results in window function error are not allowed here.

I understand that I need to run the analytical function on top the aggregate results. I am not sure how to achieve this in odi mapping (odi 12c). can anyone of you please guide me?

merge into (
        select /*+ */ *
        from target_base.tgt_table
        where (1=1)
    ) TGT
using (
    select   /*+ */
        RESULT2.ID_1 AS ID,
        RESULT2.COL AS MIN_ID
    from    (
    SELECT 
      RESULT1.ID AS ID ,
      RESULT1.DEVICE__NUM AS DEVICE__NUM ,
      RESULT1.SYS_ID AS SYS_ID ,
      MIN(RESULT1.ID) OVER (PARTITION BY RESULT1.DEVICE__NUM ,RESULT1.SYS_ID) AS COL ,
      ROW_NUMBER() OVER (PARTITION BY RESULT1.ID ORDER BY (MIN(RESULT1.ID) OVER (PARTITION BY RESULT1.DEVICE__NUM ,RESULT1.SYS_ID) AS COL) DESC ) AS COL_1   
      -- WINDOW FUNCTION ERROR,
    FROM 
      (
    select * from union_table
      ) RESULT1
    )RESULT2
    where (1=1)
     and (RESULT2.COL_1 = 1)
    ) SRC
on (
    and     TGT.ID=SRC.ID  )
when matched then update set
    TGT.COMMON_ID   = SRC.MIN_ID
    , TGT.REC_UPDATE    = SYSDATE
WHERE (
    DECODE(TGT.COMMON_ID, SRC.COMMON_ID, 0, 1) > 0
    )

UNION_TABLE has data as per below table

ID device_num sys_id
1 A 5
2 B 15
3 C 25
4 D 35
5 A 10
5 A 5
6 B 15
6 B 20
7 C 25
7 C 30
8 D 35
8 D 40

output expected: the ID where the rown_num=1 will be updated in target

output

ODI MAPPING

ODI Mapping

pawan rakesh
  • 45
  • 11

2 Answers2

1

This is very complex use case to model in ODI and the parser might not understand what you are trying to achieve.

My advice would be to write the difficult part of the query manually in SQL and use it as a source in ODI. Here is how to do it : In the physical design of your mapping click on your source table. In the property pane, go to the Extract Options. You can then paste your SQL as a value for option CUSTOMER_TEMPLATE.

Custom SQL

Of course it hides a bit the logic of the mapping so it shouldn't be used everywhere but for complex use cases as this one, this is an easy way to get the job done. I personally always add a memo on mapping with custom SQL so other developers can quickly see it.

JeromeFr
  • 1,869
  • 2
  • 17
  • 20
  • Thank you for your response, I will surely try this approach, Also I was thinking to make use of Reusable Mappings upto my first aggregate function and then use a expression component (Analytical function) followed by the target. what are your thoughts on this approach ? – pawan rakesh Feb 01 '22 at 23:32
  • 1
    @pawanrakesh It's definitely worth trying, good idea. Make sure you tick the `Subselect Enabled` checkbox when clicking on the reusable mapping in our mapping https://i.imgur.com/PRbAwJk.png – JeromeFr Feb 02 '22 at 08:44
0

Let try use IKM :Oracle Incremental Update on target table replace for IKM Oracle Merge. Physical -> click target table -> Intergration Knowlege Module -> Oracle Incremental Update

VietnamDataEngineer
  • 161
  • 3