sql statement

[edli]

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

[TomazZ]

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

[edli]

yes, but there is no way I can come around that?

[DaPi]

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:

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)
;

[TomazZ]

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:

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;
/

[edli]

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.

[DaPi]

EVERYBODY STOP ! ! ! ! !

we lost this in the formating of the originalpost:

 where wc2.idworkingcontent < wc1.idworkingcontent

back to the drawing board . . . .

[edli]

"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.

[edli]

yess.
That's it.

[DaPi]

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!