we hava more than 1000 records. i have to select first 5 record, last 5 record

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??

Question Posted / indraint09

4 Answers
7917 Views
I also Faced
E-Mail Answers

Answers were Sorted based on User's Feedback

we hava more than 1000 records. i have to select first 5 record, last 5 record and 5 records from m..

Answer / 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

we hava more than 1000 records. i have to select first 5 record, last 5 record and 5 records from m..

Answer / babu

Hi Guys,
small change is there in above solution

i.e:: GRP2:sno<=(cno/2)+2 AND sno>=(cno/2)-2

Cheers,
Babu Rao

Is This Answer Correct ?

0 Yes

0 No

we hava more than 1000 records. i have to select first 5 record, last 5 record and 5 records from m..

Answer / chanukya

have the sequence generator transformation in the mapping
whcih will give you the row numbers.

From there you can take watever rows you want. From 1 to
last or anything.

This wil help you.

Is This Answer Correct ?

2 Yes

5 No

we hava more than 1000 records. i have to select first 5 record, last 5 record and 5 records from m..

Answer / akash

For implementation through Oracle:
Sort ASC/DESC on rowid in a sub-query and take first 5 rows
of this query in main query.

For implementation through informatica:
1. Use Rank Transformation.
2. Use Sorter Transformation.

The above are for first or last 5 records. Middle 5 records
are not included here.

Is This Answer Correct ?

0 Yes

3 No

Post New Answer