Problem with synta of SQL statement in DB2 SP
Hi, i have the following SP in DB2
CREATE PROCEDURE VNDRLEB.P_GETALERTS ( IN HSUBSCRIBERID VARCHAR(6),
IN HALERTTYPEID INTEGER,
IN HTRIGGERID INTEGER )
RESULT SETS 1
LANGUAGE SQL
COLLID T1COLSP
WLM ENVIRONMENT DBT1WEU1
RUN OPTIONS 'HEAP(,,ANY)STACK(,,ANY)ST
--------------------------
-- SQL Stored Procedure
--------------------------
P1: BEGIN
-- Declare cursor
DECLARE cursor1 CURSOR WITH RETURN FOR
IF HALERTTYPEID > 0 AND HTRIGGERID > 0 THEN
SELECT PROFILE.SUBSCRIBERID, ALERTS.ALERTID, ALERTS.ALERTTYPE, ALERTS.ALERTTRIGGERID, ALERTS.DELIVERYTIME, ALERTS.DELIVERYCHANNELID, ALERTS.OBJECT, ALERTS.AMOUNT, ALERTS.MARKET
FROM UIUALERT ALERTS, UITPROF PROFILE
WHERE PROFILE.SUBSCRIBERID = ALERTS.SUBSCRIBERID AND ALERTS.SUBSCRIBERID = HSUBSCRIBERID AND ALERTS.ALERTTYPE = HALERTTYPEID AND ALERTS.ALERTTRIGGERID = HTRIGGERID
ORDER BY ALERTS.ALERTTYPE;
ELSEIF HALERTTYPEID > 0 AND HTRIGGERID = 0 THEN
SELECT PROFILE.SUBSCRIBERID, ALERTS.ALERTID, ALERTS.ALERTTYPE, ALERTS.ALERTTRIGGERID, ALERTS.DELIVERYTIME, ALERTS.DELIVERYCHANNELID, ALERTS.OBJECT, ALERTS.AMOUNT, ALERTS.MARKET
FROM UIUALERT ALERTS, UITPROF PROFILE
WHERE PROFILE.SUBSCRIBERID = ALERTS.SUBSCRIBERID AND ALERTS.SUBSCRIBERID = HSUBSCRIBERID AND ALERTS.ALERTTYPE = HALERTTYPEID
ORDER BY ALERTS.ALERTTYPE;
ELSEIF HALERTTYPEID = 0 AND HTRIGGERID = 0 THEN
SELECT PROFILE.SUBSCRIBERID, ALERTS.ALERTID, ALERTS.ALERTTYPE, ALERTS.ALERTTRIGGERID, ALERTS.DELIVERYTIME, ALERTS.DELIVERYCHANNELID, ALERTS.OBJECT, ALERTS.AMOUNT, ALERTS.MARKET
FROM UIUALERT ALERTS, UITPROF PROFILE
WHERE PROFILE.SUBSCRIBERID = ALERTS.SUBSCRIBERID AND ALERTS.SUBSCRIBERID = HSUBSCRIBERID
ORDER BY ALERTS.ALERTTYPE;
END IF;
-- Cursor left open for client application
OPEN cursor1;
END P1
When i try to compile i get the following error:
VNDRLEB.P_GETALERTS - Build started.
Calling the build utility SYSPROC.DSNTPSMP...
STEP FILE SEQN LINE
-------------- -------------- ----------- --------------------------
CONVERT SQLPRINT 1 1DB2 SQL PRECOMPILER VERSION 7 REL. 1.0 PAGE 1
CONVERT SQLPRINT 2 0OPTIONS SPECIFIED: STDSQL(NO) MAR(1,80) HOST(SQL) ONEPASS MAR(1,80)
CONVERT SQLPRINT 3 0OPTIONS USED - SPECIFIED OR DEFAULTED
CONVERT SQLPRINT 4 APOST
CONVERT SQLPRINT 5 APOSTSQL
CONVERT SQLPRINT 6 ATTACH(TSO)
CONVERT SQLPRINT 7 CONNECT(2)
CONVERT SQLPRINT 8 DEC(15)
CONVERT SQLPRINT 9 FLAG(I)
CONVERT SQLPRINT 10 HOST(SQL)
CONVERT SQLPRINT 11 NOT KATAKANA
CONVERT SQLPRINT 12 LINECOUNT(60)
CONVERT SQLPRINT 13 MARGINS(1,80)
CONVERT SQLPRINT 14 ONEPASS
CONVERT SQLPRINT 15 OPTIONS
CONVERT SQLPRINT 16 PERIOD
CONVERT SQLPRINT 17 NOSOURCE
CONVERT SQLPRINT 18 STDSQL(NO)
CONVERT SQLPRINT 19 SQL(DB2)
CONVERT SQLPRINT 20 NOXREF
CONVERT SQLPRINT 21 1DB2 SQL PRECOMPILER MESSAGES PAGE 2
CONVERT SQLPRINT 22 0DSNH016I E DSNHPARS LINE 17 COL 12 ";" REQUIRED
CONVERT SQLPRINT 23 DSNH104I E DSNHPARS LINE 17 COL 12 ILLEGAL SYMBOL "HALERTTYPEID". SOME SYMBOLS THAT MIGHT BE LEGAL ARE: ;
CONVERT SQLPRINT 24 1DB2 SQL PRECOMPILER STATISTICS PAGE 3
CONVERT SQLPRINT 25 0SOURCE STATISTICS
CONVERT SQLPRINT 26 SOURCE LINES READ: 45
CONVERT SQLPRINT 27 NUMBER OF SYMBOLS: 6
CONVERT SQLPRINT 28 SYMBOL TABLE BYTES EXCLUDING ATTRIBUTES: 1432
CONVERT SQLPRINT 29 -THERE WERE 2 MESSAGES FOR THIS PROGRAM.
CONVERT SQLPRINT 30 THERE WERE 0 MESSAGES SUPPRESSED BY THE FLAG OPTION.
CONVERT SQLPRINT 31 230792 BYTES OF STORAGE WERE USED BY THE PRECOMPILER.
CONVERT SQLPRINT 32 RETURN CODE IS 8
CONVERT SQLTERM 1 DSNH016I E DSNHPARS LINE 17 COL 12 ";" REQUIRED
CONVERT SQLTERM 2 DSNH104I E DSNHPARS LINE 17 COL 12 ILLEGAL SYMBOL "HALERTTYPEID". SOME SYMBOLS THAT MIGHT BE LEGAL ARE: ;
CONVERT SQLTERM 3 CREATE PROCEDURE VNDRLEB.P_GETALERTS(IN HSUBSCRIBERID VARCHAR(6),IN HALERTTYPEID INTEGER,IN HTRIGGERID INTEGER)RESULT SETS 1 LANGU
CONVERT SQLTERM 4 AGE SQL COLLID T1COLSP WLM ENVIRONMENT DBT1WEU1 RUN OPTIONS'HEAP(,,ANY)STACK(,
CONVERT SQLTERM 5 1(ON)MSGFILE(SYSPRINT,,,,E
CONVERT SQLTERM 6 ID
CONVERT SQLTERM 7
CONVERT SQLTERM 8 SOURCE STATISTICS
CONVERT SQLTERM 9 SOURCE LINES READ: 45
CONVERT SQLTERM 10 NUMBER OF SYMBOLS: 6
CONVERT SQLTERM 11 SYMBOL TABLE BYTES EXCLUDING ATTRIBUTES: 1432
CONVERT SQLTERM 12 THERE WERE 2 MESSAGES FOR THIS PROGRAM.
CONVERT SQLTERM 13 THERE WERE 0 MESSAGES SUPPRESSED BY THE FLAG OPTION.
CONVERT SQLTERM 14 230792 BYTES OF STORAGE WERE USED BY THE PRECOMPILER.
CONVERT SQLTERM 15 RETURN CODE IS 8
DSNTPSMP CONSOLE 1 *** DB2 V7R1 SQL Procedure Processor DSNTPSMP 1.15 (UQ72855 2003-01-10)
DSNTPSMP CONSOLE 2 Language conversion warnings and/or errors, rc=8
DSNTPSMP CONSOLE 3 PSM precompile is not successful, cannot continue
DSNTPSMP Summary 1 Request: ALTER_REBUILD routine VNDRLEB.P_GETALERTS did not complete.
Build utility function requested: ALTER_REBUILD
SYSPROC.DSNTPSMP - Returned +8
VNDRLEB.P_GETALERTS - Build failed.
VNDRLEB.P_GETALERTS - Roll back completed successfully.
When i compile only one of the statements it compiles correctly.
Can you please help?
CREATE PROCEDURE VNDRLEB.P_GETALERTS ( IN HSUBSCRIBERID VARCHAR(6),
IN HALERTTYPEID INTEGER,
IN HTRIGGERID INTEGER )
RESULT SETS 1
LANGUAGE SQL
COLLID T1COLSP
WLM ENVIRONMENT DBT1WEU1
RUN OPTIONS 'HEAP(,,ANY)STACK(,,ANY)ST
--------------------------
-- SQL Stored Procedure
--------------------------
P1: BEGIN
-- Declare cursor
DECLARE cursor1 CURSOR WITH RETURN FOR
IF HALERTTYPEID > 0 AND HTRIGGERID > 0 THEN
SELECT PROFILE.SUBSCRIBERID, ALERTS.ALERTID, ALERTS.ALERTTYPE, ALERTS.ALERTTRIGGERID, ALERTS.DELIVERYTIME, ALERTS.DELIVERYCHANNELID, ALERTS.OBJECT, ALERTS.AMOUNT, ALERTS.MARKET
FROM UIUALERT ALERTS, UITPROF PROFILE
WHERE PROFILE.SUBSCRIBERID = ALERTS.SUBSCRIBERID AND ALERTS.SUBSCRIBERID = HSUBSCRIBERID AND ALERTS.ALERTTYPE = HALERTTYPEID AND ALERTS.ALERTTRIGGERID = HTRIGGERID
ORDER BY ALERTS.ALERTTYPE;
ELSEIF HALERTTYPEID > 0 AND HTRIGGERID = 0 THEN
SELECT PROFILE.SUBSCRIBERID, ALERTS.ALERTID, ALERTS.ALERTTYPE, ALERTS.ALERTTRIGGERID, ALERTS.DELIVERYTIME, ALERTS.DELIVERYCHANNELID, ALERTS.OBJECT, ALERTS.AMOUNT, ALERTS.MARKET
FROM UIUALERT ALERTS, UITPROF PROFILE
WHERE PROFILE.SUBSCRIBERID = ALERTS.SUBSCRIBERID AND ALERTS.SUBSCRIBERID = HSUBSCRIBERID AND ALERTS.ALERTTYPE = HALERTTYPEID
ORDER BY ALERTS.ALERTTYPE;
ELSEIF HALERTTYPEID = 0 AND HTRIGGERID = 0 THEN
SELECT PROFILE.SUBSCRIBERID, ALERTS.ALERTID, ALERTS.ALERTTYPE, ALERTS.ALERTTRIGGERID, ALERTS.DELIVERYTIME, ALERTS.DELIVERYCHANNELID, ALERTS.OBJECT, ALERTS.AMOUNT, ALERTS.MARKET
FROM UIUALERT ALERTS, UITPROF PROFILE
WHERE PROFILE.SUBSCRIBERID = ALERTS.SUBSCRIBERID AND ALERTS.SUBSCRIBERID = HSUBSCRIBERID
ORDER BY ALERTS.ALERTTYPE;
END IF;
-- Cursor left open for client application
OPEN cursor1;
END P1
When i try to compile i get the following error:
VNDRLEB.P_GETALERTS - Build started.
Calling the build utility SYSPROC.DSNTPSMP...
STEP FILE SEQN LINE
-------------- -------------- ----------- --------------------------
CONVERT SQLPRINT 1 1DB2 SQL PRECOMPILER VERSION 7 REL. 1.0 PAGE 1
CONVERT SQLPRINT 2 0OPTIONS SPECIFIED: STDSQL(NO) MAR(1,80) HOST(SQL) ONEPASS MAR(1,80)
CONVERT SQLPRINT 3 0OPTIONS USED - SPECIFIED OR DEFAULTED
CONVERT SQLPRINT 4 APOST
CONVERT SQLPRINT 5 APOSTSQL
CONVERT SQLPRINT 6 ATTACH(TSO)
CONVERT SQLPRINT 7 CONNECT(2)
CONVERT SQLPRINT 8 DEC(15)
CONVERT SQLPRINT 9 FLAG(I)
CONVERT SQLPRINT 10 HOST(SQL)
CONVERT SQLPRINT 11 NOT KATAKANA
CONVERT SQLPRINT 12 LINECOUNT(60)
CONVERT SQLPRINT 13 MARGINS(1,80)
CONVERT SQLPRINT 14 ONEPASS
CONVERT SQLPRINT 15 OPTIONS
CONVERT SQLPRINT 16 PERIOD
CONVERT SQLPRINT 17 NOSOURCE
CONVERT SQLPRINT 18 STDSQL(NO)
CONVERT SQLPRINT 19 SQL(DB2)
CONVERT SQLPRINT 20 NOXREF
CONVERT SQLPRINT 21 1DB2 SQL PRECOMPILER MESSAGES PAGE 2
CONVERT SQLPRINT 22 0DSNH016I E DSNHPARS LINE 17 COL 12 ";" REQUIRED
CONVERT SQLPRINT 23 DSNH104I E DSNHPARS LINE 17 COL 12 ILLEGAL SYMBOL "HALERTTYPEID". SOME SYMBOLS THAT MIGHT BE LEGAL ARE: ;
CONVERT SQLPRINT 24 1DB2 SQL PRECOMPILER STATISTICS PAGE 3
CONVERT SQLPRINT 25 0SOURCE STATISTICS
CONVERT SQLPRINT 26 SOURCE LINES READ: 45
CONVERT SQLPRINT 27 NUMBER OF SYMBOLS: 6
CONVERT SQLPRINT 28 SYMBOL TABLE BYTES EXCLUDING ATTRIBUTES: 1432
CONVERT SQLPRINT 29 -THERE WERE 2 MESSAGES FOR THIS PROGRAM.
CONVERT SQLPRINT 30 THERE WERE 0 MESSAGES SUPPRESSED BY THE FLAG OPTION.
CONVERT SQLPRINT 31 230792 BYTES OF STORAGE WERE USED BY THE PRECOMPILER.
CONVERT SQLPRINT 32 RETURN CODE IS 8
CONVERT SQLTERM 1 DSNH016I E DSNHPARS LINE 17 COL 12 ";" REQUIRED
CONVERT SQLTERM 2 DSNH104I E DSNHPARS LINE 17 COL 12 ILLEGAL SYMBOL "HALERTTYPEID". SOME SYMBOLS THAT MIGHT BE LEGAL ARE: ;
CONVERT SQLTERM 3 CREATE PROCEDURE VNDRLEB.P_GETALERTS(IN HSUBSCRIBERID VARCHAR(6),IN HALERTTYPEID INTEGER,IN HTRIGGERID INTEGER)RESULT SETS 1 LANGU
CONVERT SQLTERM 4 AGE SQL COLLID T1COLSP WLM ENVIRONMENT DBT1WEU1 RUN OPTIONS'HEAP(,,ANY)STACK(,
CONVERT SQLTERM 5 1(ON)MSGFILE(SYSPRINT,,,,E
CONVERT SQLTERM 6 ID
CONVERT SQLTERM 7
CONVERT SQLTERM 8 SOURCE STATISTICS
CONVERT SQLTERM 9 SOURCE LINES READ: 45
CONVERT SQLTERM 10 NUMBER OF SYMBOLS: 6
CONVERT SQLTERM 11 SYMBOL TABLE BYTES EXCLUDING ATTRIBUTES: 1432
CONVERT SQLTERM 12 THERE WERE 2 MESSAGES FOR THIS PROGRAM.
CONVERT SQLTERM 13 THERE WERE 0 MESSAGES SUPPRESSED BY THE FLAG OPTION.
CONVERT SQLTERM 14 230792 BYTES OF STORAGE WERE USED BY THE PRECOMPILER.
CONVERT SQLTERM 15 RETURN CODE IS 8
DSNTPSMP CONSOLE 1 *** DB2 V7R1 SQL Procedure Processor DSNTPSMP 1.15 (UQ72855 2003-01-10)
DSNTPSMP CONSOLE 2 Language conversion warnings and/or errors, rc=8
DSNTPSMP CONSOLE 3 PSM precompile is not successful, cannot continue
DSNTPSMP Summary 1 Request: ALTER_REBUILD routine VNDRLEB.P_GETALERTS did not complete.
Build utility function requested: ALTER_REBUILD
SYSPROC.DSNTPSMP - Returned +8
VNDRLEB.P_GETALERTS - Build failed.
VNDRLEB.P_GETALERTS - Roll back completed successfully.
When i compile only one of the statements it compiles correctly.
Can you please help?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.