1. How to generate sequence numbers using expression transformation?
Solution:
In the expression transformation, create a variable port and increment it by 1. Then assign the variable port to an output port. In the expression transformation, the ports are:
V_count=V_count+1
O_count=V_count
This comment has been removed by the author.
ReplyDeleteHi Kareem,
ReplyDeleteI Tried the above scenario. Count comes as 0 in Target on all rows. Do I need to Assign any value on V_Count if Yes then where I have to assign. please provide me Expression mapping screen shot if you have. Kindly help me.
Shiva
Hi Shiva,
DeleteDefault value for variable port is '0'
In Expression of variable port we add count+1, so it will add and incremented by each and every record.
Please make sure when connecting ports from one to another transformation.
Hi Shiva,
DeleteSource--->SQ--->Expression---->Target
Generate SEQ_NO Column in Target table.
in Expression transformation add new port rename like v_count----Assign value in expression variable port like V_COUNT+1.
Add another port rename as O_COUNT(o/p)--->Expression-->V_COUNT
Connect O_COUNT to SEQ_NO in Target Table.
Hi Kareem,
ReplyDeleteI tried the same steps what you have mentioned above .I am too getting '0'as output.
I created two port one is variable port where in exp ,I gave 'var=var +1';
and 2nd one is vout which is output port where in exp i gave 'var'
yes got it .In variable port<exp<'here i was giving "var=var+1",now changed it to "var+1".its working foine for me
ReplyDeleteHi kareem
ReplyDeleteI tried the same ,but i am getting the output value "0".
Please give some more information.i created the variable and output port also.
ensure that the o_cnt is added in the target table and cnt field in target should be specified as primary key
DeleteHi kareem hru
ReplyDeleteHi Anybody plz answer this
ReplyDeleteIf i have below source
dept table
dept_id
10
20
20
30
10
20
30
30
40
Target table should be
Dept_id seq_val
10 1
10 2
20 1
20 2
20 3
30 1
30 2
30 3
40 1
Without using rank.
This article is very nice and informative to learners
ReplyDeleteInformatica training, informatica training in bangalore, informatica online training
http://www.tekclasses.com/courses/etl/informatica-training-in-bangalore/
Hi
ReplyDeleteI tried, it is working only when I say in Variable port "v_count+1". IN case, if I say "v_count = v_count+1" is not working. Please provide me the reason for this difference. Thanks.