| SQL code bit works but not when include PL/SQL [message #38019] |
Wed, 13 March 2002 15:03  |
Sid
Messages: 38
Registered: May 1999
|
Member |
|
|
The code below returns King, Ford and Scott from empp table since Ford and Scott salaries ties at second highest salary when the value is 2. When I substitute the value 2 for 4 it returns King, Ford, Scott and Jones as I want it to.
Here is the SQL code:
select ename, sal
from (select ename, sal,
RANK() over (order by sal desc) as rank_value
from emp)
where rank_value <= 2;
Problem is I want to enter the values into top_salary table. When I include PL/SQL code it does not work.
ACCEPT p_no_top_emp PROMPT 'Please enter the number of top money makers: '
DECLARE
v_no_top_emp NUMBER(40) := &p_no_top_emp;
BEGIN
select ename, sal
from (select ename, sal,
RANK() over (order by sal desc) as rank_value
from emp)
where rank_value <= v_no_top_emp;
END;
/
Note code above does not have the insert into code to keep it simple. But still it does not work.
Below is the code with insert into
ACCEPT p_no_top_emp number PROMPT 'Please enter the number of top money makers: '
DECLARE
v_ename empp.ename%TYPE;
v_sal empp.sal%TYPE;
CURSOR cursor_top_salary IS
select ename, sal
from (select ename, sal,
RANK() over (order by sal desc) as rank_value from emp)
where rank_value <= &p_no_top_emp;
BEGIN
for i IN cursor_top_salary LOOP
insert into top_salary
values ( v_ename, v_sal)
END LOOP;
END;
/
Please help!!
|
|
|
|
| Re: SQL code bit works but not when include PL/SQL [message #38023 is a reply to message #38019] |
Wed, 13 March 2002 20:34  |
seng
Messages: 191
Registered: February 2002
|
Senior Member |
|
|
I think that you SELECT statement is returned more than one values. So you can't used this SELECT statement in PL/SQL.
If you want to use SELECT statement than make sure it is only return a single record.
For more than a record then you need to use CURSOR.
Hope this is helping you.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
