Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2002
    Posts
    1

    Unanswered: Rank Analytical Function

    Hi:

    I cannot figure out why I can run the below query in an sql window but not in a procedure (although I can build a string and execute it from a procedure). The error I get when compiling the procedure is

    " PLS-00103: Encountered the symbol "(" when expecting one of the following: , from "

    Any help is much appreciated

    Thanks in advance
    ************************************************** *********
    This works from SQL window (in TOAD)
    ************************************************** *********
    Update eClock_Working set SEQ_No = 6
    Where rowid in (
    SELECT rowid row_id
    FROM
    (SELECT SERVER,PROCESS,REC_TYPE,EMPL_TYPE,EMPL_ID,WEEKEND, WORKDATE,HOURS,CONTRACT,FY,WBS,PAYCODE1,PAYCODE2,P AYCODE3,PAYCODE4,SPECCODE,SPECAMT,LABORCAT,WORKORD ER,TC_CREATEBY,SEQ_No,RANK()
    OVER(PARTITION BY EMPL_ID,CONTRACT,FY,WBS,PAYCODE1,PAYCODE2,PAYCODE3 ,PAYCODE4,SPECCODE,LABORCAT,WORKORDER
    ORDER BY SERVER,PROCESS,REC_TYPE,EMPL_TYPE,EMPL_ID,WEEKEND, WORKDATE,HOURS,CONTRACT,FY,WBS, PAYCODE1,PAYCODE2,PAYCODE3,PAYCODE4,SPECCODE,SPECA MT,LABORCAT,WORKORDER,TC_CREATEBY
    DESC NULLS LAST) AS Emp_Rank
    FROM eclock_working
    ORDER BY SERVER,PROCESS,REC_TYPE,EMPL_TYPE,EMPL_ID,WEEKEND, WORKDATE,HOURS,CONTRACT,FY,WBS, PAYCODE1,PAYCODE2,PAYCODE3,PAYCODE4,SPECCODE,SPECA MT,LABORCAT,WORKORDER,TC_CREATEBY DESC NULLS LAST)
    WHERE Emp_Rank = 6
    AND SEQ_NO is null);

    ************************************************** *********
    This DOES NOT work from a proc window (again in TOAD)
    ************************************************** *********

    CREATE OR REPLACE PROCEDURE SP_UPD_SEQ2
    AS
    BEGIN

    Update eClock_Working set SEQ_No = 6
    Where rowid in (
    SELECT rowid row_id
    FROM
    (SELECT SERVER,PROCESS,REC_TYPE,EMPL_TYPE,EMPL_ID,WEEKEND, WORKDATE,HOURS,CONTRACT,FY,
    WBS,PAYCODE1,PAYCODE2,PAYCODE3,PAYCODE4,SPECCODE,S PECAMT,LABORCAT,WORKORDER,TC_CREATEBY,SEQ_No,RANK( )
    OVER(PARTITION BY EMPL_ID,CONTRACT,FY,WBS,PAYCODE1,PAYCODE2,PAYCODE3 ,PAYCODE4,SPECCODE,LABORCAT,WORKORDER

    ORDER BY SERVER,PROCESS,REC_TYPE,EMPL_TYPE,EMPL_ID,WEEKEND, WORKDATE,HOURS,CONTRACT,FY,WBS,
    PAYCODE1,PAYCODE2,PAYCODE3,PAYCODE4,SPECCODE,SPECA MT,LABORCAT,WORKORDER,TC_CREATEBY
    DESC NULLS LAST) AS Emp_Rank
    FROM eclock_working
    ORDER BY SERVER,PROCESS,REC_TYPE,EMPL_TYPE,EMPL_ID,WEEKEND, WORKDATE,HOURS,CONTRACT,FY,WBS,
    PAYCODE1,PAYCODE2,PAYCODE3,PAYCODE4,SPECCODE,SPECA MT,LABORCAT,WORKORDER,TC_CREATEBY DESC NULLS LAST)
    WHERE Emp_Rank = 6
    AND SEQ_NO is null);
    END;

  2. #2
    Join Date
    Sep 2003
    Location
    The Netherlands
    Posts
    311
    Hi,

    if you are running an oracle 8.1.7 database, you encountered bugnr 1231326. This bug is fixed in oracle 9.0.1 and not backportable to previous versions. No workaround except for using it in SQL*PLUS.

    Good luck
    Edwin van Hattem
    OCP DBA / System analyst

Posting Permissions

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