есть запрос, который работает при обычном вызове.

select sysdate paymentdate1,
(select max(pm2.paymentdate) from payment pm2 where pm2.payerid=4024969) paymentdate2 from dual
union
select z.paymentdate as paymentdate1,
( select max(pm1.paymentdate) from payment pm1
where pm1.paymentdate<z.paymentdate
and pm1.payerid=4024969
) as paymentdate2
from (
select pm.paymentdate
from payment pm
where pm.payerid=4024969
order by pm.paymentdate desc) z
order by 1 desc

Если я пытаюсь вставить его в PLSQL - код, таким образом

for FS in(
select sysdate paymentdate1,
(select max(pm2.paymentdate) from payment pm2 where pm2.payerid=4024969) paymentdate2 from dual
union
select z.paymentdate as paymentdate1,
( select max(pm1.paymentdate) from payment pm1
where pm1.paymentdate<z.paymentdate
and pm1.payerid=4024969
) as paymentdate2
from (
select pm.paymentdate
from payment pm
where pm.payerid=4024969
order by pm.paymentdate desc) z
order by 1 desc)
loop
...
end loop;

То получаю сообщение, что мол мой вложеный селект
не ожидался.
Научите, как побороть это.

До чего обидно.
пытался вставить в код функции.
рабочий запрос

select months_between(r1,r2) from(
select sysdate r1,
(select max(nvl(pm2.paymentdate,to_date('01.01.1999','dd.mm.yyyy'))) from payment pm2 where pm2.payerid=4024969) r2 from dual
union all
select z.paymentdate r1,
( select max(pm1.paymentdate) from payment pm1
where pm1.paymentdate<z.paymentdate
and pm1.payerid=4024969
) r2
from (
select distinct pm.paymentdate
from payment pm
where pm.payerid=4024969
order by pm.paymentdate desc) z
)
where months_between(r1,r2)>1

и в результате оракле ругается на (select max(nvl(....
Я уж без for FS in( - пытался. А все зря.

klawsу
я в курсе, что нада писать
select ... into ....

И я также в курсе, что такая конструкция тоже не работает -

declare
fs number(10,3);
begin
select months_between(r1,r2) into fs from(
select sysdate r1,
(select max(nvl(pm2.paymentdate,to_date('01.01.1999','dd.mm.yyyy'))) from payment pm2 where pm2.payerid=4024969) r2 from dual
union all
select z.paymentdate r1,
( select max(pm1.paymentdate) from payment pm1
where pm1.paymentdate<z.paymentdate
and pm1.payerid=4024969
) r2
from (
select distinct pm.paymentdate
from payment pm
where pm.payerid=4024969
order by pm.paymentdate desc) z
)
where months_between(r1,r2)>1;
end;

A ГЛАВНОЕ, хочу спросить у Вас ЧТО МНЕ ДЕЛАТЬ?

версия Oracle 8i

а такое вот работает
declare
-- Local variables here
TYPE cv_typ IS REF CURSOR;
cv cv_typ;
r11 number(10,2);
begin
-- Test statements here
OPEN cv FOR
' select r1 from(
select months_between(sysdate,
(select max(pm2.paymentdate) from payment pm2 where pm2.payerid=4024969)) r1 from dual
union
select months_between(z.paymentdate,
( select max(pm1.paymentdate) from payment pm1
where pm1.paymentdate<z.paymentdate
and pm1.payerid=4024969
)) r1
from (
select pm.paymentdate
from payment pm
where pm.payerid=4024969
order by pm.paymentdate desc) z
)';
LOOP
FETCH cv INTO r11;
EXIT WHEN cv%NOTFOUND;
DBMS_OUTPUT.PUT_LINE (
TO_CHAR (r11));
END LOOP;
CLOSE cv;
end;