Interested to Buy Any Domain ? << Click Here >> for more details...
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
- 11789 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
How to elaborate powercenter integration service?
Suppose we have two source qualifier transformations sq1 and sq2 connected to target tables tgt1 and tgt2 respectively. How do you ensure tgt2 is loaded after tgt1?
How do you load more than 1 max sal in each department through informatica or write sql query in oracle?
I have three same source structure tables. But, I want to load into single target table. How do I do this? Explain in detail through mapping flow.
Why sorter is an active transformation?
Describe expression transformation?
Performance tuning in UNIX for informatica mappings?
What are the components of workflow manager?
what kind of issue you will get in environment? (we ll call help desk to raise ticket rite?
I have a mapping loading 100 records and it failed on 20th record. how to recover it without changing anything.(in prod where we don't have any access).. (the session should should start from 21 record)
What is the use of code page?
How can we use batches?
what is the -ve test case in your project.
How are indexes created after completing the load process?
What are the types of caches in lookup?
