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 |
What is a sorter transformation?
Write the program through which the records can be updated?
write sql query following table pname description lux soap sunsilk shampoo lux shampoo sunsilk soap clouseup soap closeup paste i want the output like pname description lux soap sunsilk shampoo lux soap sunsilk shampoo closeup paste closeup paste
What are the different versions of informatica?
Differentiate between a database and data warehouse?
What are the challenges of Dataware housing in the future?
If you done any modifications for a table in back end does it reflect in informatca warehouse or maping?
error reading data in flatfiles. Instead rading Ravi Teja its reading Ravi?Teja.How to handle this issue.
Why do you use Mapping Parameter and mapping variable?
What is the function of union transformation?
What the difference is between a database, a data warehouse and a data mart?
In a scenario I want to change the dimensions of a table and normalize the denomralized table which transformation can I use?