Analytic functions in a select within PL/SQL
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: Analytic functions in a select within PL/SQL

  1. #1
    Join Date
    Apr 2004
    Location
    Boston MA
    Posts
    90

    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.

  2. #2
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    analytic functions only works as dynamic sql in versions < 9i

  3. #3
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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?

  4. #4
    Join Date
    Apr 2004
    Location
    Boston MA
    Posts
    90
    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
  •  



Click Here to Expand Forum to Full Width