Home » Other » Training & Certification » pl/sql block to determine the top employees
icon9.gif  pl/sql block to determine the top employees [message #320583] Thu, 15 May 2008 10:12 Go to next message
amritaseema
Messages: 47
Registered: January 2008
Member
i want to create pl/sql block to determine the top employees with respect to salaries.
a) Accept a number n from the user where n represents the number of top n earners from the employees table.
b) In a loop,gather the salaries of top n people for the number selected above

i tried this prob...
bt unable to answer correctly...


DECLARE
n NUMBER;
v_empid NUMBER;
v_name VARCHAR2 (20);
v_dept NUMBER;
v_sal NUMBER;
BEGIN
n := '&num';

SELECT ROWNUM AS RANK, employee_id, first_name, last_name, department_id, salary
INTO v_empid, v_name, v_dept, v_sal
FROM (SELECT employee_id, first_name, last_name, department_id, salary
FROM employees
WHERE salary != '0'
ORDER BY salary DESC)
WHERE ROWNUM <= n;

FOR i IN 1 .. n
LOOP
DBMS_OUTPUT.put_line (v_sal || '');
END LOOP;
END;

can any one plzzz help me out...
i am not getting where i did anything wrong...
thanks in advance.
Thanks
Seema

[Updated on: Thu, 15 May 2008 10:13]

Report message to a moderator

Re: pl/sql block to determine the top employees [message #320584 is a reply to message #320583] Thu, 15 May 2008 10:17 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & follow posting guidelines as stated in URL above
Re: pl/sql block to determine the top employees [message #320587 is a reply to message #320583] Thu, 15 May 2008 10:25 Go to previous messageGo to next message
Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Have a look at ROW_NUMBER, RANK and DENSE_RANK functions.

Regards
Michel
icon9.gif  Re: pl/sql block to determine the top employees [message #320590 is a reply to message #320587] Thu, 15 May 2008 10:28 Go to previous message
amritaseema
Messages: 47
Registered: January 2008
Member
sorry i am again writing my code

DECLARE
n NUMBER;
v_empid NUMBER;
v_name VARCHAR2 (20);
v_dept NUMBER;
v_sal NUMBER;
BEGIN
n := '&num';

SELECT ROWNUM AS RANK, employee_id, first_name, last_name, department_id, salary
INTO v_empid, v_name, v_dept, v_sal
FROM (SELECT employee_id, first_name, last_name, department_id, salary
FROM employees
WHERE salary != '0'
ORDER BY salary DESC)
WHERE ROWNUM <= n;

FOR i IN 1 .. n
LOOP
DBMS_OUTPUT.put_line (v_sal || '');
END LOOP;
END;


still i am not getting

[Updated on: Thu, 15 May 2008 10:30]

Report message to a moderator

Previous Topic: code to display string of numbers in words(Ex: 321 as Three Two One); (Please don't use to_d
Next Topic: Multithreading (merged 3)
Goto Forum:
  


Current Time: Thu Nov 21 08:54:02 CST 2024