-
Analytic functions in a select within PL/SQL
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
Code:
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
Code:
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)
PHP Code:
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
Last edited by ddrozdov; 05-05-2004 at 05:18 PM.
-
analytic functions only works as dynamic sql in versions < 9i
-
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.
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
-
Thank you guys.
I shall use subqueries instead.
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
|