Can't make CTE work in a function

#1

I am using DB2 Express 9.7 on Windows, trying to write SQL UDFs that
use CTEs.

This works in a UDF:

RETURN WITH r ( c ) AS (SELECT c FROM t);

This works in a UDF:

SET v = (SELECT c FROM t);

This does not work in a UDF:

SET v = (WITH r ( c ) AS (SELECT c FROM t) SELECT c FROM r);

The error is:

SQL0104N An unexpected token "AS" was found following "ET v = (WITH
r ( c )".

How can I get the result of a CTE into a variable within a UDF?

--
Peter Headland

#2

On 5/11/2010 6:54 PM, Peter Headland wrote:

Quote:

I am using DB2 Express 9.7 on Windows, trying to write SQL UDFs that
use CTEs.

This works in a UDF:

RETURN WITH r ( c ) AS (SELECT c FROM t);

This works in a UDF:

SET v = (SELECT c FROM t);

This does not work in a UDF:

SET v = (WITH r ( c ) AS (SELECT c FROM t) SELECT c FROM r);

The error is:

SQL0104N An unexpected token "AS" was found following "ET v = (WITH
r ( c )".

How can I get the result of a CTE into a variable within a UDF?
WITH can only be used top-level.

The RETURN example is an exception.
You have two choices. Use a cursor or FOR statement, or create a UDF
with the CTE and then put that on the right hand side of the SET.

WITH was introduced in DB2 V2 and AFAIK precedes the standard which
allows it to be nested. There has been very limited pressure to double
back and lift the initial restrictions.

Cheers
Serge
--
Serge Rielau
SQL Architect DB2 for LUW
IBM Toronto Lab

#3

As always, Serge - you da' man! (Still chuckling over your patient
attempts to explain simple concepts to [tautology deleted] Oracle DBAs
elsewhere).

Is this arcane knowledge documented anywhere/in a book I could buy, or
do I just have to keep praying you stay available?

--
Peter Headland

#4

On 5/12/2010 11:58 AM, Peter Headland wrote:

Quote:

As always, Serge - you da' man! (Still chuckling over your patient
attempts to explain simple concepts to [tautology deleted] Oracle DBAs
elsewhere).

Is this arcane knowledge documented anywhere/in a book I could buy, or
do I just have to keep praying you stay available?
Should all fall out of the syntax diagrams.

Here you have WITH
http://publib.boulder.ibm.com/infoce.../r0000879.html

vs here you don't. and no way to get from <expression> to the above...
http://publib.boulder.ibm.com/infoce.../r0001018.html

However here it's there:
http://publib.boulder.ibm.com/infoce.../r0004237.html

--
Serge Rielau
SQL Architect DB2 for LUW
IBM Toronto Lab

#5

On May 16, 3:46*am, Serge Rielau <srie... (AT) ca (DOT) ibm.com> wrote:

Quote:

On 5/12/2010 11:58 AM, Peter Headland wrote:> As always, Serge - you da' man! (Still chuckling over your patient
Is this arcane knowledge documented anywhere/in a book I could buy, or
do I just have to keep praying you stay available?

Should all fall out of the syntax diagrams.

Serge, even though we have never met, I love you like a brother.
However ... that is a bit of a "classic IBM" answer along the lines of
the old "in the basket of a hot air balloon" joke...

Since your job title suggests you may be in a position to influence
these things: I am currently engaged in writing functions and
procedures that must have identical (JDBC) signatures and behaviour
across several vendors' databases. I consistently find DB2 and SQL PL
the worst of those in terms of lack of orthogonality/excessive special
cases/seemingly arbitrary limitations. The CTE-based function I was
trying to develop "just worked" in both PostgreSQL and SQL Server. Of
course Oracle, as always, has it's own unique way to do these things
(recursive CTEs are only available in Oracle 11R2), but - like the
other two - the functionally equivalent SELECT could be used in any
context.

--
Peter Headland

#6

Quote:

..... The CTE-based function I was
trying to develop "just worked" in both PostgreSQL and SQL Server. Of
course Oracle, as always, has it's own unique way to do these things
.....
Many logics could be written without using CTE, sometimes more simple

ways.
Using Nested-table-expressions and/or SQL control statements would be
usual ways.
Occasionally, eliminating unnecessary SET to variable statements, by
combining multiple statenments into one statement.

For example:
SET v = (WITH r ( c ) AS (SELECT c FROM t) SELECT c FROM r);
could be replaced with(by using NTE)...
SET v = (SELECT c FROM (SELECT c FROM t) r( c ));

I want to know your examples of CTE-based functions which are worked
in both PostgreSQL and SQL Server, but difficult to convert to DB2.

#7

Peter,

Consistently, eh?
May I dare guess that DB2 is the server you use least?
So could it be you natively gravitate to what works in SQL Server and
then of course only run into DB2 limitations because you aren't even
aware of those of the other system?

Does SQL Server now support VALUES everywhere? Does it support multi row
INSERTs? What about subqueries in the SET clause of an UPDATE?
Ever tried to create a BEFORE trigger in SQL Server?
Not even sure if SQL Server supports FOR EACH ROW triggers.

I won't comment on PostgreSQL since I simply do not know it well enough.

DB2 was the first server that introduced WITH and IBM pushed it into the
standard.
We do take a long term view when we design language and with properly
designed language what you get is a leap-frog game where different
vendors color out the language at different speeds.
You can thank IBM for the very fact that there is such a thing as a
compatible WITH syntax to begin with.

Cheers
Serge

PS: While you're at it, care to explain TSQL GROUP BY to me? What a mess!

--
Serge Rielau
SQL Architect DB2 for LUW
IBM Toronto Lab

#8

On May 20, 2:22*am, Serge Rielau <srie... (AT) ca (DOT) ibm.com> wrote:

Quote:

May I dare guess that DB2 is the server you use least?

You'd be wrong. I don't use any of them more than the other, nor do I
have any significant history with any of them. In fact, I haven't done
much serious db work for years before this project which, as I said,
must be able to use any of the four to do exactly the same thing. That
implies I do exactly the same (functionally) in each of them.

For this project, I have had to do a self-taught crash-course on four
databases in parallel; yours (in which I include the documentation)
has given me the most difficulty. I'm sorry you find that statement so
unpalatable that you seek to discredit me. It's precisely the fact
that I was largely ignorant about these four products when I began
which should make my comments on stuff like documentation and ease-of-
use valuable.

I'm certainly not saying the other three dbs are perfect. Each has
made me curse repeatedly. DB2 is simply the one with the highest curse
count so far.

FWIW, it's possible part of my difficulty has been that DB2 seems to
be far more of a closed community than the other dbs - it's easy to
find stacks of discussion of the others on the web; DB2 seems less
heavily covered. When I went to a good local technical bookstore, they
had near-zero on DB2; when I asked why, the owner told me that he used
to stock some titles, but sent them back because he had zero sales.
Sales of titles on the other three were steady. I don't know why this
is; maybe everyone who uses DB2 works for IBM these days? :-)

Quote:

So could it be you natively gravitate to what works in SQL Server

I have no idea where you get the idea I am a SQL Server expert, much
less fan-boy. When coding something new for the four dbs, I actually
tend to code for PostgreSQL first and SQL Server last, for operational
reasons that do not imply a value judgment; DB2 and Oracle slot in in
the middle in random order.

Quote:

I won't comment on PostgreSQL since I simply do not know it well enough.

You should take a look at it as a reference point - it's better than
all the commercial offerings in many ways, though not without its own
little annoyances. Infinitely cleaner than MySQL (but what isn't?) Of
the four, it has the lowest curse-count by a long way so far.

--
Peter Headland

#9

Quote:

FWIW, it's possible part of my difficulty has been that DB2 seems to be far more of a closed community than the other dbs - .....

http://www.dbforums.com/
In the dBforums, number of viewing peoples are usually ...
Oracle > DB2 > Microsoft SQL Server > PostgreSQL

#10

Peter I had no intention of discrediting you and apologize it I came
acroos as such.
I simply did try to understand.
I do get a lot of "DB2 is is bad" because it isn't "the same" as wath
ever people are used to.

Cheers
Serge
--
Serge Rielau
SQL Architect DB2 for LUW
IBM Toronto Lab