| Subquery in SELECT statement. [message #10905] |
Mon, 23 February 2004 11:39  |
Jae Bosou
Messages: 7
Registered: December 2003
|
Junior Member |
|
|
What's wrong with my query? I got the following error:
PLS-00103: Encountered the symbol "SELECT" when expecting one of the following:
( - + mod not null others <an identifier>
<a double-quoted delimited-identifier> <a bind variable> avg
count current exists max min prior sql stddev sum variance
execute forall time timestamp interval date
<a string literal with character set specification>
<a number> <a single-quoted SQL string>
----------------------------------------------
Here's the example of my PLSQL script:
DECLARE
p_year varchar2(4) := '2004';
p_pict varchar2(2) := 'PR';
p_payno number(3) := '1';
p_code varchar2(4) := 'V'
CURSOR vac_accr IS
select
table1_ID,
table1_balance -
(select table2_taken - table2_accr
from table2
where table2_year = p_year
and table2_payno = p_payno
and table2_code = p_pict
) new_bal
from table1
where table1_payno = p_payno
and table1_year = p_year
and table1_code = p_pict;
BEGIN
FOR vacaccr IN vac_accr LOOP
update table3
set table2_begin_balance = vacaccr.new_bal
where table3_CODE = p_vac_code
and table3_id = vacaccr.table1_id);
END LOOP;
commit;
END;
--------------------------------------------------------
|
|
|
|
| Re: Subquery in SELECT statement. [message #10906 is a reply to message #10905] |
Mon, 23 February 2004 12:46  |
Todd Barry
Messages: 4819
Registered: August 2001
|
Senior Member |
|
|
Not all SQL syntax is recognized in PL/SQL prior to 9i. The workaround is to execute using dynamic SQL, but...
Couldn't this whole procedural block be replaced with a single statement? Something like:
update table3 t3
set begin_balance = (select t1.balance - (t2.taken - t2.accr)
from table1 t1,
table2 t2
where t1.id = t3.id
and t2.year = t1.year
and t2.payno = t1.payno
and t2.code = t1.code)
where code = p_vac_code
and id in (select id
from table1
where payno = p_payno
and year = p_year
and code = p_pict); |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
