Can't make CTE work in a function
in [DB2]
|
From: Peter Headland on 11 May 2010 18:54 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
From: Serge Rielau on 11 May 2010 20:27 On 5/11/2010 6:54 PM, Peter Headland wrote: > 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
From: Peter Headland on 12 May 2010 11:58 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
From: Serge Rielau on 16 May 2010 06:46 On 5/12/2010 11:58 AM, Peter Headland wrote: > 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/infocenter/db2luw/v9r7/topic/com.ibm.db2.luw.sql.ref.doc/doc/r0000879.html vs here you don't. and no way to get from <expression> to the above... http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/topic/com.ibm.db2.luw.sql.ref.doc/doc/r0001018.html However here it's there: http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/topic/com.ibm.db2.luw.sql.ref.doc/doc/r0004237.html -- Serge Rielau SQL Architect DB2 for LUW IBM Toronto Lab
From: Peter Headland on 17 May 2010 15:06
On May 16, 3:46 am, Serge Rielau <srie...(a)ca.ibm.com> wrote: > 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 |