Logic required
Posted: Mon Feb 28, 2011 9:35 am
Hi,
below is my input.
CUS_TYPE,CUS_ID,REC_TYPE,DATE,DETAIL
C, 123, 1, 2011/02/28, XXXA
C, 123, 2, 2011/02/28, XXXB
C, 123, D, 2011/02/28, XXXC
C, 123, D, 2011/02/28, XXXD
C, 100, 1, 2011/02/28, AAA
C, 100, 2, 2011/02/28, AAA1
C, 100, D, 2011/02/28, AAA2
based on filed(CUS_ID),i need to do look up on other table
and fetch few fields (Eg. BNK,PORT,DESC).
my output should be:
CUS_TYPE,CUS_ID,REC_TYPE,DATE,DETAIL
C, 123, 1, 2011/02/28, XXXA
C, 123, 2, 2011/02/28, XXXB
C, 123, D1, 2011/02/28, XXXC
C, 123, D2, 2011/02/28, XXXD
C, 123, K1, 2011/02/28, 0001
C, 123, K2, 2011/02/28, N2
C, 123, K3, 2011/02/28, YYYY
C, 100, 1, 2011/02/28, AAA
C, 100, 2, 2011/02/28, AAA1
C, 100, D1, 2011/02/28, AAA2
C, 100, K1, 2011/02/28, 0005
C, 100, K2, 2011/02/28, N4
C, 100, K3, 2011/02/28, ZZZ
Note: K1,K2,K3 are the fields BNK,PORT,DESC respectively retrieved from look up table and its corresponding vales are populated in the filed:DETAIL.
Condition:INPUT
1. There should be alteast 3 rows for single CUS_ID.The first row will be
populated with the value '1' (field:REC_TYPE).The second row will be
populated with the value '2' (field:REC_TYPE) and rest of the rows for
same CUS_ID will be populated as 'D' (field:REC_TYPE).
Condition:OUTPUT
1. For the field REC_TYPE, if we find the value '1' or '2' , we need to
keep as it is. If we find the value 'D',for the first row it should be D1
and for the rest of the rows for the same CUS_ID, it should be
incremented by one
2. For the new fields retrieved from look up table, we need to add the
value K1 (for the look up field:BNK, and it should be incremented for
rest of the fields such as K2,K3) and its value will be populated
in the field:DETAIL
Kindly help me to resolve it
Thanks in advance
below is my input.
CUS_TYPE,CUS_ID,REC_TYPE,DATE,DETAIL
C, 123, 1, 2011/02/28, XXXA
C, 123, 2, 2011/02/28, XXXB
C, 123, D, 2011/02/28, XXXC
C, 123, D, 2011/02/28, XXXD
C, 100, 1, 2011/02/28, AAA
C, 100, 2, 2011/02/28, AAA1
C, 100, D, 2011/02/28, AAA2
based on filed(CUS_ID),i need to do look up on other table
and fetch few fields (Eg. BNK,PORT,DESC).
my output should be:
CUS_TYPE,CUS_ID,REC_TYPE,DATE,DETAIL
C, 123, 1, 2011/02/28, XXXA
C, 123, 2, 2011/02/28, XXXB
C, 123, D1, 2011/02/28, XXXC
C, 123, D2, 2011/02/28, XXXD
C, 123, K1, 2011/02/28, 0001
C, 123, K2, 2011/02/28, N2
C, 123, K3, 2011/02/28, YYYY
C, 100, 1, 2011/02/28, AAA
C, 100, 2, 2011/02/28, AAA1
C, 100, D1, 2011/02/28, AAA2
C, 100, K1, 2011/02/28, 0005
C, 100, K2, 2011/02/28, N4
C, 100, K3, 2011/02/28, ZZZ
Note: K1,K2,K3 are the fields BNK,PORT,DESC respectively retrieved from look up table and its corresponding vales are populated in the filed:DETAIL.
Condition:INPUT
1. There should be alteast 3 rows for single CUS_ID.The first row will be
populated with the value '1' (field:REC_TYPE).The second row will be
populated with the value '2' (field:REC_TYPE) and rest of the rows for
same CUS_ID will be populated as 'D' (field:REC_TYPE).
Condition:OUTPUT
1. For the field REC_TYPE, if we find the value '1' or '2' , we need to
keep as it is. If we find the value 'D',for the first row it should be D1
and for the rest of the rows for the same CUS_ID, it should be
incremented by one
2. For the new fields retrieved from look up table, we need to add the
value K1 (for the look up field:BNK, and it should be incremented for
rest of the fields such as K2,K3) and its value will be populated
in the field:DETAIL
Kindly help me to resolve it
Thanks in advance