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
- 12005 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 go to the older version for a mapping?
Explain the code page compatibility?
How do you convert single row from source into three rows into target?
What is the format of informatica objects in a repository?
What is the different lookup cache(s)?
Explain why we use partitioning the session in informatica?
What are pre and post-session shell commands?
Performance tuning in UNIX for informatica mappings?
What is the format of informatica objects in a repository? What are the databases that informatica can connect to windows?
I have 10 columns in a flat file and 10 rows corresponding to that columns. I want column number 5 and 6 for last five records. In unix as well as informtica.
Explain the types of transformations?
What is the difference between SOURCE and TARGET BASED COMMITS? What are the deliverables?in your project?
What does reusable transformation mean?
Mention a few design and development best practices for informatica?
What are the limitations of joiner transformation?
