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??
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 |
What is tracing level?
what is meant by drill through?(Mascot)
what is the use of structures?how many nodes can u create in a structure?
what is pmrep command?
Difference between flat files and relational sources?
can u please tell me about ........ how can explain my project before interviewer my projects are insurence domain & sales domain?
what is the difference between shortcut of an object and copy of an object? compare them?
explain the methodology of Data Warehousing?(Polaries)
What is Relational Integrity?
what are Lookup caches options? Including persistent caches.
IN Informatica, I have 10 records in my source system. I need to load 2 records to target for each run. how to perform.please let me know. I know by using Mapping variable we can perform but how to load please any one explain.
please tell me any easy ways of testing the Data warehouse project. In my project we are not using any tools for ETL. we are writing scripts in SCRIPTELLA. And we using Pentaho tool for Reporting How can i test all these. please tell me ASAP. thanks in adavance