Analytic functions in a select within PL/SQL

[ddrozdov]

guys, when i try to compile the (fragment of) code below I get:
PLS-00103: Encountered the symbol "(" when expecting one of the following:
, from


(select ct_recid, seqnum, smstm, smsaasdt, smsdt,
lag(smsdt,1) over(order by seqnum) lag1,
----------------^
And it points right here.

Did anyone else have an experience with PL/SQL getting confused with analytic functions?
I have Oracle 8.1.7

select
 ct_recid, seqnum, smstm, smsdt,
 nvl(smsdt,decode(smsaasdt,1,nvl(lag1,nvl(lag2,nvl(lag3,nvl(lag4,lag5)))))) keep_dt
 bulk collect into recid_tab, num_tab, char_tab, date_tab, keep_date_tab
from
 (select ct_recid, seqnum, smstm, smsaasdt, smsdt,
   lag(smsdt,1) over(order by seqnum) lag1,
   lag(smsdt,2) over(order by seqnum) lag2,
   lag(smsdt,3) over(order by seqnum) lag3,
   lag(smsdt,4) over(order by seqnum) lag4,
   lag(smsdt,5) over(order by seqnum) lag5
 from
   AVE0005A0202.SAMPTM_DATA
 where
   subject_id = this.subject_id and cblock = this.cblock and 
   substr(ct_recid,1,instr(ct_recid,'.',-1))  = substr(this.ct_recid,1,instr(this.ct_recid,'.',-1))
 order by seqnum) x;

Oh, by the way
it runs fine from SQL

select
 seqnum, smstm, smsaasdt, smsdt,
 nvl(smsdt,decode(smsaasdt,1,nvl(lag1,nvl(lag2,nvl(lag3,nvl(lag4,lag5)))))) keep_dt
from
 (select ct_recid, seqnum, smstm, smsaasdt, smsdt,
   lag(smsdt,1) over(order by seqnum) lag1,
   lag(smsdt,2) over(order by seqnum) lag2,
   lag(smsdt,3) over(order by seqnum) lag3,
   lag(smsdt,4) over(order by seqnum) lag4,
   lag(smsdt,5) over(order by seqnum) lag5
 from
   AVE0005A0202.SAMPTM_DATA
 where
   csubjid = '0077/0003' and cblock = 1 and pagenum = '62'
 order by seqnum)

SEQNUM    SMSTM    SMSAASDT  SMSDT          KEEP_DT
1         12:12              01/12/2004    01/12/2004
2         12:13    1                       01/12/2004
3         12:14    1                       01/12/2004
4         12:14              01/13/2004    01/13/2004
5         12:14              01/13/2004    01/13/2004
6         12:14    1                       01/13/2004 

Do you guys get a gist of what I am doing?
the smaasdt means date same as above on the form.
The form has 6 rows
I need to grab the next available date from above if smaasdt = 1

[pando]

analytic functions only works as dynamic sql in versions < 9i

[jmodic]

Sure, analytic functions are not supported in PL/SQL until 9i. It's the same with CASE expressins and some other stuff - you can run it in SQL, but not in SQL from inside the PL/SQL. Until 9i, Oracle have two different SQL parsers - one for SQL engine and another one for PL/SQL engine. And typicaly, any newly introduced SQL functionality is not available in PL/SQL untill at least next major release.

In your case, you should be able to use analytic functions from within PL/SQL if you use some kind of dynamic SQL - DBMS_SQL, execute immediate or ref cursor.

[ddrozdov]

Thank you guys.
I shall use subqueries instead.