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.

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??

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.

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!!