we hava more than 1000 records. i have to select first 5
record, last 5 record and 5 records from middle of the source.
how can i do in oracle as well as in informetica??
Answer Posted / babu
Hi All,
ORACLE:
========
Below SQL query will give the exact result of what you
people are expecting.
SELECT rownum num,e.* From Emp e Where Rownum<=5
Union
SELECT * From
(SELECT Rownum Num,E.* From Emp E)
Where Num BETWEEN (SELECT round(Count(*)/2) From Emp)AND
(SELECT round(Count(*)/2) +1 From Emp)
Union
SELECT * From
(SELECT rownum num,e.* From emp e)
Where Num <=(SELECT count(*) From Emp)
And
Num>(SELECT count(*)-5 From Emp)
INFORMATICA:
============
please find the below mapping for your scenario.
SRC==>SQ==>(1)EXP==>AGG=====JNR===>RTR==T1,T2,T3
|| ||
(2)EXP==========
1> In first expression having the all columns coming from
the source and two extra columns(output ports)
I.one for sequence numbers.
II.second one is for constant value
2> In aggregatation transformation count the number of
records based(GROUP BY)on the dummy port.
3> In joiner transformation join the two flows(AGG and Exp
the join column is dummy port)
4> create 3 groups in Router transformation for 1St 5
records,Latst 5 records and Middle 5 records
GRP1::sno<=5
GRP2::sno<=cno+2 and sno>=cno-2 /*cno=count(*)*/
GRP3::sno<=cno and sno>cno-5
Cheers,
Babu rao
Is This Answer Correct ? | 3 Yes | 1 No |
Post New Answer View All Answers
where we cannot use sorter Transformation. if we use then we will not get correct data?
Explain what is etl process? How many steps etl contains explain with example?
What are the ETL Testing Operations?
what are the session parameters? How do u set them?
how do u set partition points in the mapping?
what is data modeling?
What is etl process. How many steps etl contains explain with example?
how would u estimate the size of Aggregator transform data and index cache?
What is the difference between project backlog and product backlog?
Explain about round-robi?
what is the difference between cardinality and Nullability?
What are various etl tools in the market?
how do u implement configuration management in Informatica?
Explain me ETL project architecture in Detail?
What is etl process? How many steps etl contains?