Results 1 to 5 of 5

Thread: Sql0104n error

  1. #1
    Join Date
    Jan 2010
    Posts
    6

    Unanswered: Sql0104n error

    Hi all,

    I am trying to write a stored procedure in DB2. I am using contro center version 9.1 for the same.


    Create Procedure TESTPROC(IN param_month integer,IN param_year integer)
    Language SQL

    BEGIN

    INSERT INTO TEST SELECT OPTR.PYUSERNAME||'('||AP.INDOP||')' "AGENT",
    COUNT(DISTINCT(CASE WHEN AP.INDOUT='TAX' THEN AP.PZINSKEY ELSE NULL END)) "TAXCOUNT",
    COUNT(DISTINCT(CASE WHEN AP.INDOUT='CE' THEN AP.PZINSKEY ELSE NULL END)) "CECOUNT",
    COUNT(DISTINCT(CASE WHEN AP.INDOUT IN ('PPV','PP') THEN AP.PZINSKEY ELSE NULL END)) "PPVCOUNT",
    COUNT(DISTINCT AP.PZINSKEY) "TOTAL",
    COUNT(DISTINCT(INV.PZINSKEY)) AS "INVCOUNT",
    COUNT(DISTINCT(LINE.PZINSKEY)) AS "LINECOUNT",

    COUNT(DISTINCT(CASE WHEN AP.PRIORITY ='Priority' THEN AP.PZINSKEY ELSE NULL END)) "PRTOTAL",
    COUNT(DISTINCT(CASE WHEN AP.PRIORITY ='Priority' THEN INV.PZINSKEY ELSE NULL END)) AS "PRINVCOUNT",
    COUNT(DISTINCT(CASE WHEN AP.PRIORITY ='Priority' THEN LINE.PZINSKEY ELSE NULL END)) AS "PRLINECOUNT",

    COUNT(DISTINCT(CASE WHEN AP.PRIORITY ='Non Priority' THEN AP.PZINSKEY ELSE NULL END)) "NPRTOTAL",
    COUNT(DISTINCT(CASE WHEN AP.PRIORITY ='Non Priority' THEN INV.PZINSKEY ELSE NULL END)) AS "NPRINVCOUNT",
    COUNT(DISTINCT(CASE WHEN AP.PRIORITY ='Non Priority' THEN LINE.PZINSKEY ELSE NULL END)) AS "NPRLINECOUNT"


    FROM
    AP_WORK AP
    LEFT OUTER JOIN
    AP_INV_DET INV
    ON AP.PXINSNAME=INV.DOCID
    LEFT OUTER JOIN
    AP_INV_LINE_DET LINE
    ON INV.DOCID=LINE.DOCID,
    PR_OPERATORS OPTR

    WHERE AP.INDOP IS NOT NULL
    AND DATE(AP.INDDATE)
    BETWEEN DATE('01-02-2008') AND DATE('12-31-2009')
    AND AP.INDOP=OPTR.PYUSERIDENTIFIER

    GROUP BY OPTR.PYUSERNAME||'('||AP.INDOP||')'

    FOR READ ONLY WITH UR

    END@


    And this is what I am getting


    DB21034E The command was processed as an SQL statement because it was not a
    valid Command Line Processor command. During SQL processing it returned:
    SQL0104N An unexpected token "Create Procedure TESTPROC(IN param_month i" was
    found following "BEGIN-OF-STATEMENT". Expected tokens may include:
    "<values>". LINE NUMBER=38. SQLSTATE=42601

    SQL0104N An unexpected token "Create Procedure TESTPROC(IN param_month i" was found following "BEGIN-OF-STATEMENT". Expected tokens may include: "<values>".

    Explanation:

    A syntax error in the SQL statement or the input command string
    for the SYSPROC.ADMIN_CMD procedure was detected at the specified
    token following the text "<text>". The "<text>" field indicates
    the 20 characters of the SQL statement or the input command
    string for the SYSPROC.ADMIN_CMD procedure that preceded the
    token that is not valid.

    As an aid, a partial list of valid tokens is provided in the
    SQLERRM field of the SQLCA as "<token-list>". This list assumes
    the statement is correct to that point.

    The statement cannot be processed.

    User Response:

    Examine and correct the statement in the area of the specified
    token.

    sqlcode : -104

    sqlstate : 42601


    I also tried changing delimiter to @ rather than samicolon which is default one.Still it does not work.No matter whatever changes I make. same error code SQL0104N appers with different descriptions evrytime.

    Your inputs will be highly appreciated.

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Add semicolon...
    FOR READ ONLY WITH UR ;

    END@

  3. #3
    Join Date
    Jan 2010
    Posts
    6
    Quote Originally Posted by tonkuma View Post
    Add semicolon...
    FOR READ ONLY WITH UR ;

    END@

    Thanks TONKUMA for your reply.

    I added the semicolo there too...
    now it says
    SQL0104N An unexpected token "FOR READ ONLY" was found following "'('||AP.INDOP||')'

    ". Expected tokens may include: "<space>
    ".

    Is there something wrong with the single quotes and doble quotes??

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    You can't specify "FOR READ ONLY" in an INSERT statement.

    Extracted syntax of INSERT statement...
    INSERT INTO [table-name | view-name | etc. ] [WITH common-table-expression[, ...] ] fullselect [WITH RR | RS | CS | UR ]

    fullselect doesn't contain read-only-clause.

    The optional clause read-only-clause was included in Select-statement.
    Last edited by tonkuma; 01-30-10 at 10:31. Reason: Added last statement "The optional clause read-only-clause was ..."

  5. #5
    Join Date
    Jan 2010
    Posts
    6
    Hey tonkume,

    you were right.

    I removed the for read only clause and it worked.
    At least procedure was compiled.thanks a lot
    i was really frustrated by this error since yesterday and i am completely new to this db2 thing.

    highly appreciate your help buddy!!!.

    Will come up with few queries if i happen to encounter again.
    Thanks a lot!!

Posting Permissions

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