which transformation should we use to get the 5th rank member from a table in

Categories >> Software >> Data Warehouse >> Informatica
Suggest New Category

which transformation should we use to get the 5th rank
member from a table in informatica?can we achieve this in
sql?

Question Posted / bharathi

6 Answers
10942 Views
IBM, I also Faced
E-Mail Answers

Answers were Sorted based on User's Feedback

which transformation should we use to get the 5th rank member from a table in informatica?can we a..

Answer / venkateshmalneni

FOR THIS WE HAVE 2 USE TWO T/R ie FIRST WE HAVE 2 USE RANK T/R
AND THEN USE A FILTER T/R IN FILTER GIVE THE CONDITION AS
RANK=5 CONNECT TO TARGET
THE FLOW IS LIKE THIS
SRC --->SQ--->RANK--->FILTER--->TRG

WE CAN ALSO DO THIS IN SQL USE THE FOLLOWING QUERY

SELECT * FROM <TABLE_NAME> WHERE ROWNUM <= 5 MINUS SELECT *
FROM <TABLE_NAME> WHERE ROWNUM <=4;

Is This Answer Correct ?

8 Yes

0 No

which transformation should we use to get the 5th rank member from a table in informatica?can we a..

Answer / bhaskar

select * from(select * from emp order by sal desc)
where rownum<=5
MINUS
select * from(select * from emp order by sal desc)
where rownum<=4

Is This Answer Correct ?

3 Yes

0 No

which transformation should we use to get the 5th rank member from a table in informatica?can we a..

Answer / satyanarayana

first one is correct

Is This Answer Correct ?

1 Yes

1 No

which transformation should we use to get the 5th rank member from a table in informatica?can we a..

Answer / yaseen

As my knowledge we can write as
select distinctsal from emp A where 5 =
(select count(distinctsal) from emp B where A.sal <= B.sal)

Plz correct me if I am wrong

Is This Answer Correct ?

0 Yes

0 No

which transformation should we use to get the 5th rank member from a table in informatica?can we a..

Answer / jvdwhinfo

Hi There,

The DFD given in answer is absolutely correct in
informatica.

But coming to sql override stuff i agree with answer 4.
And i want to do little modification keeping performance in
view.
The modified query looks like this.

With salorder
As
(select * from emp)
select * from salorder where rownum <= 5
minus
select * from salorder where rownum <= 4;

Thanks,
James

Is This Answer Correct ?

0 Yes

1 No

which transformation should we use to get the 5th rank member from a table in informatica?can we a..

Answer / chandrasekar

Hi Venkatesh,

The above SQL query is not working correctly..

I have one answer for the above problem. The query is, try
it in the SQL override.

select * from emp where sal=(select max(sal) from emp a
where (select count(sal) from emp b where b.sal >=a.sal)=5)

If it wrong means send the correct answers to me
chandranmca2007@gmail.com

Is This Answer Correct ?

0 Yes

3 No

Post New Answer