× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



Thanks much Elvis. RH


"Elvis Budimlic"
<ebudimlic@xxxxxxxxxxxxxxxxxxxxxxxxx>
wrote in message news:00f601c76ab2$7cf12590$6e01a8c0@xxxxxxxx
There's no need for dynamic SQL.  Here is a modified example without it:

CREATE PROCEDURE ctetest
RESULT SETS 1
LANGUAGE SQL
BEGIN

DECLARE ctecursor CURSOR FOR
  WITH T1 AS (SELECT 'Testing' FROM SYSIBM/SYSDUMMY1) SELECT * FROM T1;

OPEN ctecursor;

END;

As for differences between MS & DB2, MS tends to do things that deviate
from
standards if they believe it makes sense syntactically etc.
IBM tends to stick with standards, for portability sake mostly.

Good book to read for DB2 stored procedures is "Stored Procedures,
Triggers,
and User-Defined Functions on DB2 Universal Database for iSeries" IBM
redbook.  Download the PDF at:

http://www.redbooks.ibm.com/redbooks/pdfs/sg246503.pdf

Also, check out "Related publications" section in that redbook.

Elvis

Celebrating 10-Years of SQL Performance Excellence

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On
Behalf Of Ryan Hunt
Sent: Monday, March 19, 2007 7:00 PM
To: midrange-l@xxxxxxxxxxxx
Subject: Re: CTE in PROC?

Thanks Elvis.
I am pretty much an MS SQL Server developer so for every 500 MS SQL procs
I
write, I write 1 for DB2/400.  It always amazes me how differently each
DBMS

implements certain things.  In the MS world, using the SQL_TEXT variable
below is considered dynamic SQL and is typically only used if a parameter
or

variable determined at runtime needs to alter the SQL statement in some
fashion.  Otherwise, static SQL can be used (for the most part).

So, I completely wasn't expecting the SQL_TEXT variable requirement.
Maybe
I need to finally read the DB2/400 SQL redbook from cover to cover.

Mind shedding some light on the dynamic SQL requirement for me?

Thanks.

Ryan

"Elvis Budimlic"
<ebudimlic@xxxxxxxxxxxxxxxxxxxxxxxxx>
wrote in message news:00e901c76a76$285f5510$6e01a8c0@xxxxxxxx
I don't see why it wouldn't work.  I just tried this in Navigator's
RunSqlScript window:

CREATE PROCEDURE ctetest
RESULT SETS 1
LANGUAGE SQL
BEGIN
 DECLARE SQL_TEXT VARCHAR(100);
 DECLARE ctecursor CURSOR FOR SQL_STATEMENT;

 SET SQL_TEXT = 'WITH T1 AS (SELECT ''Testing'' FROM SYSIBM/SYSDUMMY1)
SELECT * FROM T1';
PREPARE SQL_STATEMENT FROM SQL_TEXT;
OPEN ctecursor;

END;

CALL ctetest;


It worked as I expected it to, returning 'Testing' string.

Elvis

Celebrating 10-Years of SQL Performance Excellence

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On
Behalf Of Ryan Hunt
Sent: Monday, March 19, 2007 4:47 PM
To: midrange-l@xxxxxxxxxxxx
Subject: CTE in PROC?

I have been trying to use a CTE in a stored proc but can't seem to get
my
syntax right.  I've checked the SQL redbook and the stored proc redbook
(and

of course google) but I haven't seen an example of where to put it.

I've tried:
CREATE PROCEDRE...
blah
blah

BEGIN

DECLARE CURSOR C1 WITH RETURN FOR

WITH Q1 (col1, col2)
AS
(SELECT....)

SELECT blah, blah
FROM Q1

OPEN C1
CLOSE C1
END

Anyway, I can't seem to put the CTE in there (anyware) without causing
an
error.

Can CTE's be used on DB2/400 V5R4?



-- 
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list
To post a message email:
MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.





-- 
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list
To post a message email:
MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.





-- 
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list
To post a message email:
MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.





-- 
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list
To post a message email:
MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.






As an Amazon Associate we earn from qualifying purchases.

This thread ...

Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.