Interested to Buy Any Domain ? << Click Here >> for more details...
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??
- 4 Answers
- 8677 Views
- I also Faced
- E-Mail Answers
Answers were Sorted based on User's Feedback
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 |
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 |
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 |
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 |
Dear All, Can anyone tell me that from where i can get Power Center 8.5 S/W for Linux and how can i install Power Center 8.5 or any 7.x series on Linux Platform. Thanks in advance
What are materialized views & where do we use them?
Which transformation we use to convert 'NA' to 'Not Available'
what is the difference between cardinality and Nullability?
What are snapshots? What are materialized views & where do we use them? What is a materialized view log?
what is the difference between cardinality and Nullability?
Explain do we need an etl tool?
what are the cubes developed in a sap bw project and what is the requirement for developing it?
Hi please try to reply with answer. my mail id is raghu.1028@gmail.com. if the source is having millions of records, but having theree target tables. As per user the first record need to insert into first target and second record in to a second target and third into third target, similarly the process needs to repeat?
Which type or t/s is used to convert rows into column & column into rows
I have a Flat file with more no. of Records also including duplicate values. But i need distinct values to one target and remaining records to another target in Informatica way
WHY CANT WE SEE THE DATA IN DATASET
Teradata 
