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
- 11809 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
My source is delimited flat file Flat file data is H|Date D1|ravi|bangalore D2|raju|pune T|4 The data will be send to target if the fallowing two conditions satisfied 1.The first row Date column is equal to SYSDATE 2.Last record second port equal to number of records. How to achieve?
Write the unconnected lookup syntax and how to return more than one column.
my source is junk data how will u remove that junk data by using unix please any one replay me
What is the sequence generator transformation in informatica?
Can we use the mapping parameter or variables developed in one mapping into any other reusable transformation?
design of staging area?
Some flat files are there, out of these having some duplicate. How do you eliminate duplicate files while loading into targets?
what is degenerated dimension?
Can you use flat files in Mapplets.
generate date / time dimension in informatica
Target increases every day even though no new record was inserted
TELL ME ONE COMPLEX MAPPING IN UR PROJECT? chandumba2005@gmai.com
what is the size of u r source(like file or table)?
What is event and what are the tasks related to it?
Which transformation should we use to normalise the COBOL and relational sources?
