| 
		
			|  pl/sql block to determine the top employees [message #320583] | Thu, 15 May 2008 10:12  |  
			| 
				
				|  | 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 #320590 is a reply to message #320587] | Thu, 15 May 2008 10:28  |  
			| 
				
				|  | 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 |  
	|  |  |