Interested to Buy Any Domain ? << Click Here >> for more details...
How to extract original records at one target & Duplicate
records at one target?
- 12 Answers
- 22666 Views
- I also Faced
- E-Mail Answers
Answers were Sorted based on User's Feedback
Answer / sks
Hi,
Yes you can do it by using Expression transformation as
well. Infact, by using 2 SQ (source qualifier) and using
sql queries (as I explained earlier), you can perform the
required task on relational tables only (but not on flat
files etc).
In the case of flat files as source, you will need to use
Expression transformation. But you also need to use sorter
before expression (otherwise record flag will change for
every incoming record & duplicates won't be traced). The
sequence of transformations will be as follows -
Source -> SQ -> Sorter ->Expression ->Router(or 2 filters) -
>Targets
| Is This Answer Correct ? | 6 Yes | 1 No |
yes... add a new column in the sql override by calculating
the count(*) to get how many times the record is repeating.
Using Router we can seperate them into two different
targets.
| Is This Answer Correct ? | 1 Yes | 1 No |
Answer / davan
sq-->sort--exp--riuder--tg1--tg2
In exp V_flag=iif(empno=p_empno,v_flag+1,1)--variable port
p_empno=empno--variable port
0_flag=v_flag--output port
In rouder o_flag>1 one target,default one target
| Is This Answer Correct ? | 0 Yes | 0 No |
Answer / kumar
The scenario's.. i want, duplicate records from a table to a
target.. n remaining records to b on another target...
| Is This Answer Correct ? | 0 Yes | 1 No |
Answer / sunil
Its not very clear by using expression transformation.Can
any one eloberate. I am creating one variable port VAR_EMPNO
in exp and giving value as EMPNO.Created one output port
EMPVAR_OP with cond : IFF(EMPNO = VAR_EMPNO,1,0) Based on
this I am filtering and redirecting to two targets but its
not working, is the expression fine over here?
| Is This Answer Correct ? | 1 Yes | 2 No |
Answer / sks
Hi,
If I understand your question correctly, then it is like-
How to send original (distinct) records to one target
(let's say TA) and duplicates to another target (let's say
TB).
It can simply be done as follows -
In your mapping, take the source table containing whole
records (duplicates as well). Now, take 2 source qualifier
(SQ) transformations. The first SQ should
contain 'Distinct' sql query (hope you know that) and the
output ports should go to TA table.
The second SQ should contain the following sql stmt (which
filters only the duplicate records) -
-------------------------------------------
SELECT empno, ename, job, mgr, hiredate, sal, comm, deptno
FROM (SELECT empno, ename, job, mgr, hiredate, sal, comm,
deptno, ROW_NUMBER() OVER (PARTITION BY empno ORDER BY
EMPNO) rn FROM s_assign_1_emp)
WHERE rn > 1
-------------------------------------------
I am using emp table example (hope you can understand it).
Now, the output ports of the seecond SQ should go to TB
table.
This is the simplest way of doing it. Hope it will help.
If you feel any issue, please let me know at
shrikant_rps@yahoo.com
| Is This Answer Correct ? | 1 Yes | 3 No |
What is update override?
how we can update a target table without update strategy and update override?
Hi, There is a session in my workflow which is running for a long time, atlast we found the cause is the missing index. My session is running via a stored procedure. Can I create an Index on the table which the stored procedure is using while my session is running? Please suggest. My Informatica version is PC 8.0.6 My Oracle APPS is 11.5.3. Thanks,
Let’s say I have more than have record in source table and I have 3 destination table A,B,C. I have to insert first 1 to 10 records in A then 11 to 20 in B and 21 to 30 in C. Then again from 31 to 40 in A, 41 to 50 in B and 51 to 60 in C……So on up to last record.
what are the difference between Informatica 7.1 and 8.1?
What is data caches/ index caches?
Plz can anybody send Informatica and Cognos FAQ Plz To thid ID anil643@gmail.com (or) kumar643@gmail.com
SO many times i saw "$PM parser error " .what is meant by PM?
Can some one explain me about Telecommunications(wireless) project in Informatica? Thanks in advance
TWO FLAT FILES ARE THERE , EACH HAVING NO MATCHING COLUMNS . HOW CAN U JOIN THESE TWO USING JOINER TRANSFORMATION ?
How to identify bottlenecks in sources,targets,mappings,workflow,system and how to increase the performance?
if we hav 10 records in a file, can we get first record from it by using Aggregator with out using Groupby Port in it?If Yes..let me know the answer plz!!!
Teradata 
