How can you find the 2nd Highest salary in a file department wise in abinitio?

Categories >> Software >> Data Warehouse >> Ab Initio
Suggest New Category

Question Posted / nsrikanta57

9 Answers
43988 Views
IBM, TCS, I also Faced
E-Mail Answers

Answers were Sorted based on User's Feedback

How can you find the 2nd Highest salary in a file department wise in abinitio?..

Answer / vital_parsley

rollup would reduce the number of records how could you get
2nd highest after that ?

I think Scan can work with 2 temp vars.

Is This Answer Correct ?

13 Yes

2 No

How can you find the 2nd Highest salary in a file department wise in abinitio?..

Answer / roshni

you can use roll up to calculate dept wise salary first and
then sort it with key as sal. create indexes using
next_in_sequence() and then filter out the 2nd record.

hope this helped!!

Is This Answer Correct ?

19 Yes

11 No

How can you find the 2nd Highest salary in a file department wise in abinitio?..

Answer / raje0303

Hi Murali,

This logic is correct only will fail when highest salary entries are more than one (e.g. two people has highest and equal salary )
we need to updated scan function as below -

type temporary_type=record
integer(4) temp1;
integer(4) last_sal;
end;

temp :: initialize(in) =
begin
temp.temp1::0;
temp.last_sal::0;
end;

out :: scan(temp, in) =
begin
if(last_sal != in.salary)
begin
out.temp1 :: temp.temp1 + 1;
end;
last_sal = in.salay;
end

out :: finalize(temp, in) =
begin
out.sal:: in.sal;
out.dno :: in.dno;
out.temp1 :: temp.temp1;
end;
out :: output_select(out) =
begin
out::out.temp1 == 2;
end;
###############################################
4.Output_file

Is This Answer Correct ?

6 Yes

2 No

How can you find the 2nd Highest salary in a file department wise in abinitio?..

Answer / bhabani

This is working fine if we have only one person who is getting second highest salary..what if more than 1 person's are getting the same amount of salary ?

Is This Answer Correct ?

3 Yes

1 No

How can you find the 2nd Highest salary in a file department wise in abinitio?..

Answer / sridhar

when each group is having two 2nd higest values this logic
will not work.

Is This Answer Correct ?

1 Yes

0 No

How can you find the 2nd Highest salary in a file department wise in abinitio?..

Answer / vital_parsley

1)use a reformat.
2)Global variable which increments with every records only
if previous record is not equal to current record
3) output_select where sal == 2

Is This Answer Correct ?

3 Yes

2 No

How can you find the 2nd Highest salary in a file department wise in abinitio?..

Answer / ravi

we can do this with scan comp
exp::i/p folloed by sort folloed by scan and o/p
ip record
id,dept_name,sal
in sort keep key fields as (dept_name,sal descending)
now in scan write this T/f in parametres

type temporary_type
record
decimal("")secno;
end;
temp::initialize(in)=
begin
temt.secno::0;
end;
temp::scan(temp,in)=
out::finalize(temp,in)=
begin
out.in::in.id;
out.ddept_name::in.dept_name;
out.secno::temp.secno;
out.sal::in.sal;
end;
out::out_select(out)=
begin
out::out.secno==2
end;

Is This Answer Correct ?

2 Yes

1 No

How can you find the 2nd Highest salary in a file department wise in abinitio?..

Answer / nikhil sali

We can use Scan.
1) First sort your data with 2 keys dept,salary description
2) In scan create one parameter say seq nbr and initialize it with 0.
3) in scan method increment it with 1
4) this will create rank in each group of dept
5) in output select method - select record where seq_nbr = 2

Is This Answer Correct ?

2 Yes

1 No

How can you find the 2nd Highest salary in a file department wise in abinitio?..

Answer / murali krishna udayagiri

Yes Vital is correct. This can be acheived using SCAN
component with only one variable.

1.Input_file
2.Sort with in group (dno asc,sal desc)
3.scan
###############################################
type temporary_type=record
integer(4) temp1;end;
temp :: initialize(in) =
begin
temp.temp1::0;
end;

out :: scan(temp, in) =
begin
out.temp1 :: temp.temp1 + 1;
end;

out :: finalize(temp, in) =
begin
out.sal:: in.sal;
out.dno :: in.dno;
out.temp1 :: temp.temp1;
end;
out :: output_select(out) =
begin
out::out.temp1 == 2;
end;
###############################################
4.Output_file

Thanks,
Murali Udayagiri
Mail id: udayagiri.murali@gmail.com

Is This Answer Correct ?

3 Yes

8 No

Post New Answer