Results 1 to 8 of 8
  1. #1
    Join Date
    May 2004
    Location
    Redwood Shores, CA
    Posts
    68

    Unanswered: 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;
    Last edited by Nocopy; 05-05-04 at 17:39.
    My way or the highway. Yeah

  2. #2
    Join Date
    May 2004
    Location
    Redwood Shores, CA
    Posts
    68
    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)
    HTML 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 Nocopy; 05-05-04 at 17:55.
    My way or the highway. Yeah

  3. #3
    Join Date
    Jul 2003
    Posts
    2,296
    does it work in the form when you hardcode the values like you did when you ran it in SQL?
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  4. #4
    Join Date
    May 2004
    Location
    Redwood Shores, CA
    Posts
    68
    The form does not do any extrapolation. It saves as is.

    This is a piece of data validation procedure that checks if dates and times are consequtive.

    I dump all into several arrays with bulk collect and loop through the arrays to find out of chron order pairs. The discrepancy is saved into an errortable.

    If the verdict is that Oracle 8.1.7 's PLSQL is too junior for analytic functions then I shall resort to the procedural solution.
    My way or the highway. Yeah

  5. #5
    Join Date
    Jul 2003
    Posts
    2,296
    I believe I read somewhere of a similar problem.
    I think the situation was that 817 PL/SQL does not support the analytics.

    Too bad.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  6. #6
    Join Date
    May 2004
    Location
    Redwood Shores, CA
    Posts
    68

    That explains it.
    Thanks Duck.

    over and out
    My way or the highway. Yeah

  7. #7
    Join Date
    Jan 2004
    Location
    Singapore
    Posts
    89
    Quote Originally Posted by Nocopy
    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)
    HTML 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
    Make use of execute immediate.
    I think this will solve ur problem
    Thanks and Regards,

    Praveen Pulikunnu

  8. #8
    Join Date
    May 2004
    Location
    Redwood Shores, CA
    Posts
    68
    Yes Praveen, I got the same suggestion on the other forum. Before Oracle 9i and you can only run analytic sql dynamically.
    For my application the idea of running analytics was speed. I don't want to resort to runtime compilation with NDS. I have solved this problem from within PL/SQL (no analytics in SQL). That just got me to think a little harder.

    Thank you for reply.
    My way or the highway. Yeah

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •