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 "PRLINECOUN T",
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 "NPRINVCOUN T",
COUNT(DISTINCT( CASE WHEN AP.PRIORITY ='Non Priority' THEN LINE.PZINSKEY ELSE NULL END)) AS "NPRLINECOU NT"
FROM
AP_WORK AP
LEFT OUTER JOIN
AP_INV_DET INV
ON AP.PXINSNAME=IN V.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.P YUSERIDENTIFIER
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_C MD 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_C MD 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.
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 "PRLINECOUN T",
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 "NPRINVCOUN T",
COUNT(DISTINCT( CASE WHEN AP.PRIORITY ='Non Priority' THEN LINE.PZINSKEY ELSE NULL END)) AS "NPRLINECOU NT"
FROM
AP_WORK AP
LEFT OUTER JOIN
AP_INV_DET INV
ON AP.PXINSNAME=IN V.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.P YUSERIDENTIFIER
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_C MD 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_C MD 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.