0

Here is a sample source table

REGION_CODE TIER
093            4
091            5
090            1
096          null

Target Table

REGION_CODE D_REGION_CODE_ID    TIER

Right now the target table will be empty, I want to find the MAX value of D_REGION_CODE_ID column and insert the value into it like MAX( D_REGION_CODE_ID ) + 1 for new each record.(like if max is null then it will be 1 and then it will be 1+1, 2+1 and so on)

I have a completely parameterized mapping and I tried using Aggregator trasnformation with connected lookup on target table but it seems that its reading the specific row value rather than the max from that column(i.e max of row value itself) I have to built it as completely parameterized so i cannot use a straight forward sql query in lookup transformation.Also I can't use sequence generator to insert new surrogate keys as i already have a sequence generator being used to populate surrogate keys for the table,so if i add a new sequence it would just replicate the values from the earlier sequence.

Is there any way to populate rows based on the fetched max value? So that it achieves like this

REGION_CODE  D_REGION_CODE_ID       TIER
093              1                    4
091              2                    5
090              3                    1
096              4                   null
rohanK0602
  • 3
  • 2

1 Answers1

0

Let me know if this works.

  1. create a unconnected lkp. Use a generic SQL like this select max(id ) as maxid, -99 as dummy from mytable. Mark maxid port as return port and dummy as join condition. If you dont want to lookup, you can read from the table, add an aggregator to calculate max(id) and then join it to the below expression using a joiner.

  2. In an expression - Call this lkp like this :lkp.lkp_max_id(-99) and store data in lkp_maxid

  3. Add another sequence generator which will reset from 1 in every run. Add next val to above expression.

  4. Add NEXTVAL to lkp_maxid and use it as new_D_REGION_CODE_ID.

In this solution, everything is generic and you dont have to parameterize anything.

Koushik Roy
  • 6,868
  • 2
  • 12
  • 33
  • I have to create a mapping task from the base mapping, instead of using a aggregator i used a unconnected lkp which is parameterized and in the mapping task i used the query which you suggested and then for the incoming fields i created (IN_D_REGION_CODE_ID) and in the join condition DUMMY = IN_D_REGION_CODE_ID but it seems to throw the error as [=]: function cannot resolve operands of ambiguously mismatching types.... DUMMY = >>>>IN_D_REGION_CODE_ID<<<< what am i missing here? – rohanK0602 Jul 31 '23 at 12:40
  • i can see you are using `DUMMY = IN_D_REGION_CODE_ID`. I would rename input field and use `DUMMY = IN_DUMMY` field and make sure data type of both fields are `NUMBER`. – Koushik Roy Jul 31 '23 at 14:19
  • but when creating the input field in input parameters it doesn't show what kind of data type it will be there is just a textbox to add the incoming field name and thats it. – rohanK0602 Jul 31 '23 at 15:31
  • can you modify return field data types ? Could you pls refer to https://www.google.com/search?q=iics+unconnected+lookup&rlz=1C1GCEA_enIN1032IN1032&oq=iics+unconnected+lookup&gs_lcrp=EgZjaHJvbWUyBggAEEUYOTINCAEQABiDARixAxiABDIHCAIQABiABDIHCAMQABiABDIHCAQQABiABDIHCAUQABiABDIHCAYQABiABDIJCAcQABgKGIAEMgcICBAAGIAEMgcICRAAGIAE0gEINTE2MmowajeoAgCwAgA&sourceid=chrome&ie=UTF-8#fpstate=ive&vld=cid:273e3db9,vid:arna2GXLhJQ – Koushik Roy Aug 01 '23 at 02:40
  • cannot modify if defining it at mapping task. I made a workaround tk change the dummy data type in select query to varchar and the join condition satisfied as lkp_dummy = In_dummy , and was able to fetch the max of column! thanks for the unconnected lkp logic – rohanK0602 Aug 01 '23 at 04:15