DB2 Stored Proc help -- Urgent please

[sri]

My environment is DB2v9.1, Windows/AIX and I am trying to compile the
stored proc below and I am getting an error, "ERROR [42601] [IBM][DB2/
NT] SQL0104N An unexpected token "WITH" was found following "User_ID
= UsrID; ". Expected tokens may include: "SELECT". LINE
NUMBER=11. SQLSTATE=42601"
CREATE PROCEDURE TestProc(IN UsrID INT)
LANGUAGE SQL
SPECIFIC TestProc
DYNAMIC RESULT SETS 1

BEGIN
DECLARE UserID INT DEFAULT -1;
SELECT * FROM tab_user where User_ID = UsrID;
WITH TempView (clnt_id) AS
(SELECT clnt_id FROM clients)
SELECT CLNT_ID FROM TempView;
END

Thanks for your help

[PJackson]

You have an extraneous semi colon here:
SELECT * FROM tab_user where User_ID = UsrID; <----
before you finished your SQL statement.

Careful with those semi-colons! They are needed, but only in the
right places.

Phil Jackson
On Apr 16, 10:02 pm, sri <[e-mail]:

My environment is DB2v9.1, Windows/AIX and I am trying to compile the
stored proc below and I am getting an error, "ERROR [42601] [IBM][DB2/
NT] SQL0104N An unexpected token "WITH" was found following "User_ID
= UsrID; ". Expected tokens may include: "SELECT". LINE
NUMBER=11. SQLSTATE=42601"

CREATE PROCEDURE TestProc(IN UsrID INT)
LANGUAGE SQL
SPECIFIC TestProc
DYNAMIC RESULT SETS 1

BEGIN
DECLARE UserID INT DEFAULT -1;
SELECT * FROM tab_user where User_ID = UsrID;
WITH TempView (clnt_id) AS
(SELECT clnt_id FROM clients)
SELECT CLNT_ID FROM TempView;
END

Thanks for your help

[Serge Rielau]

sri wrote:

My environment is DB2v9.1, Windows/AIX and I am trying to compile the
stored proc below and I am getting an error, "ERROR [42601] [IBM][DB2/
NT] SQL0104N An unexpected token "WITH" was found following "User_ID
= UsrID; ". Expected tokens may include: "SELECT". LINE
NUMBER=11. SQLSTATE=42601"
CREATE PROCEDURE TestProc(IN UsrID INT)
LANGUAGE SQL
SPECIFIC TestProc
DYNAMIC RESULT SETS 1

BEGIN
DECLARE UserID INT DEFAULT -1;
SELECT * FROM tab_user where User_ID = UsrID;
WITH TempView (clnt_id) AS
(SELECT clnt_id FROM clients)
SELECT CLNT_ID FROM TempView;
END

Do you have a Sybase/SQL Server background?
In the SQL Standard you cannot just write a query and it turns into a
result set. You need to DECLARE a cursor WITH RETURN and OPEN that cursor.
E.g.
BEGIN
DECLARE cur WITH RETURN TO CLIENT FOR
SELECT * FROM tab_user WHERE User_ID = Usr_ID;
OPEN cur;
END

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[sri]

On Apr 17, 7:34 am, Serge Rielau <[e-mail]:

sri wrote:

My environment is DB2v9.1, Windows/AIX and I am trying to compile the
storedprocbelow and I am getting an error, "ERROR [42601] [IBM][DB2/
NT] SQL0104N An unexpected token "WITH" was found following "User_ID
= UsrID; ". Expected tokens may include: "SELECT". LINE
NUMBER=11. SQLSTATE=42601"

CREATE PROCEDURE TestProc(IN UsrID INT)
LANGUAGE SQL
SPECIFIC TestProc
DYNAMIC RESULT SETS 1

BEGIN
DECLARE UserID INT DEFAULT -1;
SELECT * FROM tab_user where User_ID = UsrID;
WITH TempView (clnt_id) AS
(SELECT clnt_id FROM clients)
SELECT CLNT_ID FROM TempView;
END

Do you have a Sybase/SQL Server background?
In the SQL Standard you cannot just write a query and it turns into a
result set. You need to DECLARE a cursor WITH RETURN and OPEN that cursor.
E.g.
BEGIN
DECLARE cur WITH RETURN TO CLIENT FOR
SELECT * FROM tab_user WHERE User_ID = Usr_ID;
OPEN cur;
END

--
Serge RielauDB2Solutions Development
IBM Toronto Lab- Hide quoted text -

- Show quoted text -

Thanks for your reply -- I just copied some snippets of the procedure
and hence this in complete code, I am sorry for that. This is exactly
what I am trying to acheive. I want to use Common Table Expressions in
stored procedure when I run the following statement

BEGIN
WITH TempView (clnt_id) AS
(SELECT clnt_id FROM clients)
SELECT CLNT_ID FROM TempView;
END

independently then it runs just fine but when I put the same into a
stored procedure then I get the following error message

ERROR [42601] [IBM][DB2/NT] SQL0104N An unexpected token "WITH" was
found following "1 BEGIN ". Expected tokens may include:
"SELECT". LINE NUMBER=9. SQLSTATE=42601
Thanks,

Sri

[Serge Rielau]

sri wrote:

On Apr 17, 7:34 am, Serge Rielau <[e-mail]:

>sri wrote:

>>My environment is DB2v9.1, Windows/AIX and I am trying to compile the
storedprocbelow and I am getting an error, "ERROR [42601] [IBM][DB2/
NT] SQL0104N An unexpected token "WITH" was found following "User_ID
= UsrID; ". Expected tokens may include: "SELECT". LINE
NUMBER=11. SQLSTATE=42601"
CREATE PROCEDURE TestProc(IN UsrID INT)
LANGUAGE SQL
SPECIFIC TestProc
DYNAMIC RESULT SETS 1
BEGIN
DECLARE UserID INT DEFAULT -1;
SELECT * FROM tab_user where User_ID = UsrID;
WITH TempView (clnt_id) AS
(SELECT clnt_id FROM clients)
SELECT CLNT_ID FROM TempView;
END

Do you have a Sybase/SQL Server background?
In the SQL Standard you cannot just write a query and it turns into a
result set. You need to DECLARE a cursor WITH RETURN and OPEN that cursor.
E.g.
BEGIN
DECLARE cur WITH RETURN TO CLIENT FOR
SELECT * FROM tab_user WHERE User_ID = Usr_ID;
OPEN cur;
END

--
Serge RielauDB2Solutions Development
IBM Toronto Lab- Hide quoted text -

- Show quoted text -

Thanks for your reply -- I just copied some snippets of the procedure
and hence this in complete code, I am sorry for that. This is exactly
what I am trying to acheive. I want to use Common Table Expressions in
stored procedure when I run the following statement

BEGIN
WITH TempView (clnt_id) AS
(SELECT clnt_id FROM clients)
SELECT CLNT_ID FROM TempView;
END

independently then it runs just fine but when I put the same into a
stored procedure then I get the following error message

I think you meant to say BEGIN ATOMIC?
Now when you use BEGIN ATOMIC you also get no result back.
DB2 supports queries like as an easy way to achieve side-effects. Such
as sending email or so. In your case chances are DB2 will throw away the
query because it does nothing.

>
ERROR [42601] [IBM][DB2/NT] SQL0104N An unexpected token "WITH" was
found following "1 BEGIN ". Expected tokens may include:
"SELECT". LINE NUMBER=9. SQLSTATE=42601

Yep.. you need a DECLARE cursor statement here.

If I had it my way I'd raise a -104 for the standalone BEGIN ATOMIC as
well. Before my time. :-(

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[sri]

On Apr 17, 9:13 am, Serge Rielau <[e-mail]:

sri wrote:

On Apr 17, 7:34 am, Serge Rielau <[e-mail]:

sri wrote:
My environment is DB2v9.1, Windows/AIX and I am trying to compile the
storedprocbelow and I am getting an error, "ERROR [42601] [IBM][DB2/
NT] SQL0104N An unexpected token "WITH" was found following "User_ID
= UsrID; ". Expected tokens may include: "SELECT". LINE
NUMBER=11. SQLSTATE=42601"
CREATE PROCEDURE TestProc(IN UsrID INT)
LANGUAGE SQL
SPECIFIC TestProc
DYNAMIC RESULT SETS 1
BEGIN
DECLARE UserID INT DEFAULT -1;
SELECT * FROM tab_user where User_ID = UsrID;
WITH TempView (clnt_id) AS
(SELECT clnt_id FROM clients)
SELECT CLNT_ID FROM TempView;
END
Do you have a Sybase/SQL Server background?
In the SQL Standard you cannot just write a query and it turns into a
result set. You need to DECLARE a cursor WITH RETURN and OPEN that cursor.
E.g.
BEGIN
DECLARE cur WITH RETURN TO CLIENT FOR
SELECT * FROM tab_user WHERE User_ID = Usr_ID;
OPEN cur;
END

--
Serge RielauDB2Solutions Development
IBM Toronto Lab- Hide quoted text -

- Show quoted text -

Thanks for your reply -- I just copied some snippets of the procedure
and hence this in complete code, I am sorry for that. This is exactly
what I am trying to acheive. I want to use Common Table Expressions in
stored procedure when I run the following statement

BEGIN
WITH TempView (clnt_id) AS
(SELECT clnt_id FROM clients)
SELECT CLNT_ID FROM TempView;
END

independently then it runs just fine but when I put the same into a
stored procedure then I get the following error message

I think you meant to say BEGIN ATOMIC?
Now when you use BEGIN ATOMIC you also get no result back.
DB2 supports queries like as an easy way to achieve side-effects. Such
as sending email or so. In your case chances are DB2 will throw away the
query because it does nothing.

ERROR [42601] [IBM][DB2/NT] SQL0104N An unexpected token "WITH" was
found following "1 BEGIN ". Expected tokens may include:
"SELECT". LINE NUMBER=9. SQLSTATE=42601

Yep.. you need a DECLARE cursor statement here.

If I had it my way I'd raise a -104 for the standalone BEGIN ATOMIC as
well. Before my time. :-(

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab- Hide quoted text -

- Show quoted text -

I am a newbie hence please bear with my ignorance. Here is the
complete stored proc

CREATE PROCEDURE TestProc(IN UsrID INT)
LANGUAGE SQL
SPECIFIC TestProc
DYNAMIC RESULT SETS 1
BEGIN
WITH TempView (clnt_id) AS
(SELECT clnt_id FROM clients)
SELECT CLNT_ID FROM TempView;
END
When i compile I get the error mentioned above. What am I doing wrong?

[Serge Rielau]

sri wrote:

When i compile I get the error mentioned above. What am I doing wrong?

CREATE PROCEDURE TestProc(IN UsrID INT)
LANGUAGE SQL
SPECIFIC TestProc
DYNAMIC RESULT SETS 1
BEGIN
DECLARE result CURSOR WITH RETURN FOR
WITH TempView (clnt_id) AS
(SELECT clnt_id FROM clients)
SELECT CLNT_ID FROM TempView;
OPEN result;
END

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[sri]

On Apr 17, 4:29 pm, Serge Rielau <[e-mail]:

sri wrote:

When i compile I get the error mentioned above. What am I doing wrong?

CREATE PROCEDURE TestProc(IN UsrID INT)
LANGUAGE SQL
SPECIFIC TestProc
DYNAMIC RESULT SETS 1
BEGIN
DECLARE result CURSOR WITH RETURN FOR
WITH TempView (clnt_id) AS
(SELECT clnt_id FROM clients)
SELECT CLNT_ID FROM TempView;
OPEN result;
END

Cheers
Serge
--
Serge RielauDB2Solutions Development
IBM Toronto Lab

I used your example as my base and built my stored proc, it works just
fine and I am getting the expected result. Thanks a lot for your help.

--Sri