How can the following be achieved in 1 single Informatica
Mapping.
* If the Header table record has error value(NULL) then
those records and the corresponding child records in the
SUBHEAD and DETAIL tables should also not be loaded into
the target(TARGET1,TARGET 2 or TARGET3).
* If the HEADER table record is valid, but the SUBHEAD or
DETAIL table record has an error value (NULL) then the no
data should be loaded into the target TARGET1,TARGET 2 or
TARGET3.
* If the HEADER table record is valid and the SUBHEAD or
DETAIL table record also has valid records only then the
data should be loaded into the target TARGET1,TARGET 2 and
TARGET3.
===================================================
HEADER
COL1 COL2 COL3 COL5 COL6
1 ABC NULL NULL CITY1
2 XYZ 456 TUBE CITY2
3 GTD 564 PIN CITY3
SUBHEAD
COL1 COL2 COL3 COL5 COL6
1 1001 VAL3 748 543
1 1002 VAL4 33 22
1 1003 VAL6 23 11
2 2001 AAP1 334 443
2 2002 AAP2 44 22
3 3001 RAD2 NULL 33
3 3002 RAD3 NULL 234
3 3003 RAD4 83 31
DETAIL
COL1 COL2 COL3 COL5 COL6
1 D001 TXX2 748 543
1 D002 TXX3 33 22
1 D003 TXX4 23 11
2 D001 PXX2 56 224
2 D002 PXX3 666 332
========================================================
TARGET1
2 XYZ 456 TUBE CITY2
TARGET2
2 2001 AAP1 334 443
2 2002 AAP2 44 22
TARGET3
2 D001 PXX2 56 224
2 D002 PXX3 666 332
- 3 Answers
- 10263 Views
- IBM, Mphasis, I also Faced
- E-Mail Answers
Answer Posted / vinithra
Hi,
This could be implemented in many ways.One such way is :
Assumption : All the 3 tables namely HEADER,SUBHEAD and
DETAIL belong to the same Database
Solution : In the Source Qualifier for HEADER , wrie a
query which will select COL1 from all HEADER table which
has valid values in all 3 tables.The query would look like :
SELECT H.COL1
FROM
HEADER H
INNER JOIN
SUBHEAD S
ON
H.COL1=S.COL1
AND
H.COL2 IS NOT NULL AND H.COL3 IS NOT NULL AND H.COL5 IS NOT
NULL AND H.COL6 IS NOT NULL AND
S.COL2 IS NOT NULL AND S.COL3 IS NOT NULL AND S.COL5 IS NOT
NULL AND S.COL6 IS NOT NULL
INNER JOIN
DETAIL D
ON
H.COL1=D.COL1
AND
D.COL2 IS NOT NULL AND D.COL3 IS NOT NULL AND D.COL5 IS NOT
NULL AND D.COL6 IS NOT NULL
i.e the above query will fetch the value 2 .Insert this to
a flat file.
Then in the same mapping , DO the following:
HEADER Source Qualifier -> Lookup on the flat File such
that HEADER.COL1=FlatFile.COL1->Insert the valid records in
Target (records which do not return NULL from the lookup)
Here only record with COL1=2 will be inserted into the
Target
Repeat the same for SUBHEAD->Target2 and DETAIL->Target3 in
the same mapping.
Please let me know if i am wrong.
Thanks Vinithra.
| Is This Answer Correct ? | 5 Yes | 0 No |
Post New Answer View All Answers
What are the measure objects?
What will be the approach?
Explain how to import oracle sequence into informatica?
How we can use union transformation?
Can anyone tell me the new features in Informatica 9 Version?
What are the main features of Oracle 8i with context to datawarehouse?
What are the prerequisite tasks to achieve the session partition?
What is rank transformation?
differences between service based and product based?
What are the transformations that cannot be placed between the sort origin and the joiner transformation so that we do not lose the input sort order?
I want my deployment group to refer an external configuration file, while i deploy in the production environment. How can i achieve it.
what is Active lock explain
waht type of interface is used for testing the data in informatica
If the source has duplicate records as id and name columns, values: 1 a, 1 b, 1 c, 2 a, 2 b, the target should be loaded as 1 a+b+c or 1 a||b||c, what transformations should be used for this?
What happen when you enable grid option avilable at session level ? (Ans found: Scalabily . A single session Parallelization) But how can one session is sharable among different nodes at the same time while running ?
