-
sql statement
Hi,
I have this code which in SQL*Plus runs ok but in PL/SQL it doesn't.
How can I come around with it?
SQL*PLUS
--------
delete from tblworkingcontent
where idworkingcontent in
(select idworkingcontent
from tblworkingcontent wc1
where wc1.metatags='a'
and (select count(*)
from tblworkingcontent wc2
where wc2.idworkingcontent
and wc2.metatags='a') <
(select count(*)
from tblworkingcontent wc3
where wc3.metatags='a'-1))
In PL/SQL instead it doesn't execute
declare
v_metatags tblworkingcontent.metatags%type;
v_numerorighe int;
begin
delete from tblworkingcontent
where idworkingcontent in
(select idworkingcontent
from tblworkingcontent wc1
where wc1.metatags=v_metatags
and (select count(*)
from tblworkingcontent wc2
where wc2.idworkingcontent
and wc2.metatags=v_metatags) <
(select count(*)
from tblworkingcontent wc3
where wc3.metatags=v_metatags-v_numerorighe))
end;
/
ERROR at line 10:
ORA-06550: line 10, column 14:
PLS-00103: Encountered the symbol "SELECT" when expecting one of the following:
( - + mod not null others
avg
count current exists max min prior sql stddev sum variance
execute forall time timestamp interval date
ORA-06550: line 13, column 46:
PLS-00103: Encountered the symbol "<" when expecting one of the following:
) with and or group having intersect minus start union where
connect
ORA-06550: line 16, column 62:
PLS-00103: Encountered the symbol ")" when expecting one of the following:
; return returning and or
Thank you in advance
-
From metalink:
A scalar subquery is a subquery that returns a single row, this is a
new feature in Oracle8i which can be used in the SET clause of an UPDATE
statement and the VALUES clause of an INSERT statement - but such a statement
gives an error if placed in a PLSQL block
In versions below Oracle9i 9.0.1 the SQL and PL/SQL use different parsers, so
everything that work OK and is supported in SQL will not automatically work OK
and be supported in PL/SQL
Tomaž
"A common mistake that people make when trying to design something completely
foolproof is to underestimate the ingenuity of complete fools" - Douglas Adams
-
yes, but there is no way I can come around that?
-
I think you'd best start by cleaningup the logic. The section
"and (select count(*)
from tblworkingcontent wc2
where wc2.idworkingcontent and wc2.metatags=v_metatags) <
(select count(*)
from tblworkingcontent wc3
where wc3.metatags=v_metatags-v_numerorighe)"
depends on the table as a whole and not on any row selected by the
"in (select idworkingcontent
from tblworkingcontent wc1
where wc1.metatags=v_metatags . . . ."
so, as far as I can see, it just switches the whole delete on and off, so it should not be in the sub-query.
. . and I don't see why you need the IN clause . . . and there is a ; missing after the final )), isn't there?
How about:
Code:
delete from tblworkingcontent
where metatags=v_metatags
and
(select count(*)
from tblworkingcontent wc2
where wc2.idworkingcontent and wc2.metatags=v_metatags) <
(select count(*)
from tblworkingcontent wc3
where wc3.metatags=v_metatags-v_numerorighe)
;
-
And, if you want pl/sql, since your scalar subquery expressions
Originally posted by DaPi
depends on the table as a whole and not on any row selected by the
outer query, you can execute them separately:
Code:
declare
v_metatags tblworkingcontent.metatags%type;
v_numerorighe int;
v_cnt1 number;
v_cnt2 number;
begin
select count(*) into v_cnt1
from tblworkingcontent wc2
where wc2.idworkingcontent and wc2.metatags=v_metatags;
select count(*) into v_cnt2
from tblworkingcontent wc3
where wc3.metatags=v_metatags-v_numerorighe;
delete from tblworkingcontent
where metatags=v_metatags
and v_cnt1 < v_cnt2;
end;
/
Tomaž
"A common mistake that people make when trying to design something completely
foolproof is to underestimate the ingenuity of complete fools" - Douglas Adams
-
delete from tblworkingcontent
where idworkingcontent in
(select idworkingcontent
from tblworkingcontent wc1
where wc1.metatags='a'
and (select count(*)
from tblworkingcontent wc2
where wc2.idworkingcontent
(select count(*)
from tblworkingcontent wc3
where wc3.metatags='a'-1))
well
wc2.idworkingcontent
makes difference and I hadn't written it.Sorry.
-
EVERYBODY STOP ! ! ! ! !
we lost this in the formating of the originalpost:
Code:
where wc2.idworkingcontent < wc1.idworkingcontent
back to the drawing board . . . .
-
"delete from tblworkingcontent
where idworkingcontent in
(select idworkingcontent
from tblworkingcontent wc1
where wc1.metatags=v_metatags
and (select count(*)
from tblworkingcontent wc2
where wc2.idworkingcontent
and wc2.metatags=v_metatags) <
(select count(*)
from tblworkingcontent wc3
where wc3.metatags=v_metatags-v_numerorighe))
end;"
/
"wc2.idworkingcontent
It eliminates it it seems.
but this makes a difference.
excuse the mess.
-
-
Originally posted by edli
wc2.idworkingcontent < wc1.idworkingcontent
makes difference and I hadn't written it.Sorry.
The < to end of line gets lost unless you put spaces round the < . . . . Bizzare!
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|