How to delete first 3 rows & last 3 rows in target table in
informatica
Answers were Sorted based on User's Feedback
FIRS PIPELINE
BY using sequence generator t/r generate sequence
numbers(sno)
in rank t/r,from sq connect all the ports to the rank
t/r,rank by sno,select top 3 ranks
SECOND PIPELINE
connect the nextval port from sequence generator to sorter
t/r
from sq connect all the ports to the sorter t/r,connect all
the ports to the filter t/r.in filter t/r write a condition
sno>3.
connect the o/p of the two pipelines by using UNION T/R
connect the o/p of the Union t/r to aggrigator t/r group by
sno.create an o/p port with name count_sno to
count(sno),connect the o/p from aggrigator t/r to the filter
t/r write the condition count_sno=1.
connect the o/p from filter to trg
THE DATA FLOW DIAGRAM IS LIKE THIS
sequence generator
| |
| \|/
src--->sq--->rank------>union--->aggrigator---->filter->trg
| | /
\|/ \|/ /
sortet----->filter/
If am wrong please let me know
Is This Answer Correct ? | 2 Yes | 0 No |
FIRS PIPELINE
BY using sequence generator t/r generate sequence
numbers(sno)
in rank t/r,from sq connect all the ports to the rank
t/r,rank by sno,select top 3 ranks
SECOND PIPELINE
connect the nextval port from sequence generator to sorter
t/r
from sq connect all the ports to the sorter t/r,connect all
the ports to the filter t/r.in filter t/r write a condition
sno>3.
connect the o/p of the two pipelines by using UNION T/R
connect the o/p of the Union t/r to aggrigator t/r group by
sno.create an o/p port with name count_sno to
count(sno),connect the o/p from aggrigator t/r to the filter
t/r write the condition count_sno=1.
connect the o/p from filter to trg
THE DATA FLOW DIAGRAM IS LIKE THIS
sequence generator
| |
| \|/
src--->sq--->rank------>union--->aggrigator---->filter->trg
| | /
\|/ \|/ /
sortet----->filter/
If am wrong please let me know
Is This Answer Correct ? | 1 Yes | 1 No |
Answer / civa
take seq trns and drag nextval port into exp trns and also
drag sq ports to exp
in exp delete from emp where nextval= 1 or 2 or 3 and
nextval=count(empno)-1 or count(empno)-2 or count(empno)-3
Is This Answer Correct ? | 3 Yes | 3 No |
Answer / kiran
Question state Target table!! All above answers are for
Source table!
Is This Answer Correct ? | 1 Yes | 1 No |
Answer / santosh kumar sarangi
create a sequence t/r and link the next value with sq t/r to
expression t/r and. Create two link.
First Link:
Connect ex t/r to update strategy t/r and keep condition as
IIF(NEXTVAL<4,DD_DELETE,DD_REJECT) and connect to target
except the nextval column.
Second Link:
Connect to sorter t/r and sort with descending order of
nextval and rename as in_nextval. create new sequence
generator and link to the same ex t/r. Link the ports to
again one more update strategy t/r and keep the same condition
as IIF(NEXTVAL<4,DD_DELETE,DD_REJECT) and link to the one
more instance of target table.
Let me know if any things wrong.
Thanks & regards,
Santosh Kumar Sarangi
Is This Answer Correct ? | 0 Yes | 1 No |
Answer / suresh
cant we write a subquery?
delete from table emp where exists (select * from emp where rownum=1 or 2 or 3);
count(empno) --> has to be stored in a variable port and delete the last three rows of the target table.
Is This Answer Correct ? | 0 Yes | 2 No |
Which one is better performance wise connected/un-connected lkp?why?
What type of sorting algorithm does the Sorter Transformation use, to do its sorting Operation? 1. Bubble sort 2. Insertion sort 3. Shell sort 4. Merge sort 5. Heapsort 6. Quicksort 7. Bucket sort
What is enterprise data warehousing?
Why we use ENABLE HIGH PRECISION in session properties?
What is the functionality of update strategy?
How do you join more than 3 flat files in informatica?
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?
explain about unit testing? in real time?
Whats the difference between informatica powercenter server, repositoryserver and repository?
What is an expression transformation?
How to load only the first and last record of a flat file into the target?
In a sequential batch can you run the session if previous session fails?