INPUT file 'A' contains:
1
2
3
4
5
6
7
8
9
10
input file 'B' contains:
6
7
8
9
10
11
12
13
14
15
Output file 'X' contains:
1
2
3
4
5
Output file 'Y' contains:
6
7
8
9
10
Output file 'Z' contains:
11
12
13
14
15
How can we implement this in a single ds job?
Answer Posted / subbuchamala
1)
We can solve this by using Change capture stage. First,we use source as 'A' and refrerence as 'B' both of them are connected to Change capture stage. From, change capture stage it connected to filter stage and then targets X,Y and Z.
(Change Code '0'--->exist in both, copy record
Change Code '1'--->Exists in SRC 'A', INSERT record
Change Code '2'--->Exists in SRC 'B', DELETE record
Change Code '3'--->Exists in 'A' & 'B' with changes, UPDATE record)
In the filter stage:
Change_code column=1 it goes to 'X' [1,2,3,4,5]
Change_code column=0 it goes to 'Y' [6,7,8,9,10]
Change_code column=2 it goes to 'Z' [11,12,13,14,15]
2)
Add an extra column 'colA' and 'colB' to the files 'A' and 'B' respectively. Let the value for colsA be 'a' for all the rows in file 'A' and the value for colB be 'b' in file 'B'(using the column generator stage).Now join both the files using join stage using 'ID' column. Perform full outer join. Map the ID col, colA and colB to output. Next pass it through a transformer.
Transformer constraint:
1) file X - colA=a and colB<>b ----->[1,2,3,4,5]
2) file Y - colA=a and colB=b------->[6,7,8,9,10]
3) file Z - colA<>a and colB=b------>[11,12,13,14,15]
Is This Answer Correct ? | 5 Yes | 3 No |
Post New Answer View All Answers
if we using two sources having same meta data and how to check the data in two sources is same or not? and if the data is not same i want to abort the job ?how we can do this?
Field,NVL,INDEX,REPLACE,TRANSLATE,COLESC
Where do you see different stages in the designer?
Explain entity, attribute and relationship in datastage?
Differentiate between validated and Compiled in the Datastage?
I/p : F1 table have A,B,C,D,E and F2 table having C,E,V i need output 0utput1: ABD output2: V any one suggest me this
How you Implemented SCD Type 1 & Type 2 in your project?
Highlight the main features of datastage?
how do u catch bad rows from OCI stage? And what CLI stands for?
What is datastage engine?
what is the use of surogate key in datastage
What are some prerequisites for datastage?
Difference between ‘validated ok’ and ‘compiled’ in data stage?
How will you move hashed file from one location to another location?
What is a ds designer?