CTEs in UDFs

[jefftyzzer]

DB2 LUW 8.1

Colleagues:

It's my understanding that SELECT...INTO is not supported in UDFs. I'm
having problems, however, with (compound) variable assignments
involving CTEs. My highly-contrived example, below, mimics what I want
to. It's important to note that I need to a) assign more than one
column to a variable, and b) use a CTE (as opposed to an in-line
view), because I need to refer to the first CTE in the second CTE.

CREATE FUNCTION TEST() RETURNS DECIMAL
SPECIFIC TEST
DETERMINISTIC
NO EXTERNAL ACTION
INHERIT SPECIAL REGISTERS

BEGIN ATOMIC

DECLARE V_TEST1 INTEGER DEFAULT 0;--
DECLARE V_TEST2 INTEGER DEFAULT 0;--

SET (V_TEST1, V_TEST2) =
(
WITH
T(C1)
AS
(
VALUES
(1)
),
T2(C1)
AS
(
SELECT
*
FROM
T
)
SELECT
COUNT(*),
COUNT(*)
FROM
T2
);--

RETURN CAST(V_TEST1 AS DECIMAL) / V_TEST2;--

END;

The error I'm getting when trying to create the UDF is

DB21034E The command was processed as an SQL statement because it was
not a
valid Command Line Processor command. During SQL processing it
returned:
SQL0104N An unexpected token "AS" was found following "H
T(C1) ".
Expected tokens may include: "JOIN". LINE NUMBER=16. SQLSTATE=42601

Is it not possible to use a CTE in a scalar subselect (in a UDF)?

Regards,

--Jeff

[Tonkuma]

You can't use CTE in SET statement even in SQL Stored Procedure.

You wouldn't be neccesary to use SET variables, if you used CTE and
the SET statement was not in a loop.
For example:
CREATE FUNCTION TEST() RETURNS DECIMAL
SPECIFIC TEST
DETERMINISTIC
NO EXTERNAL ACTION
INHERIT SPECIAL REGISTERS
RETURN
WITH
T(C1)
AS
(
VALUES
(1)
),
T2(C1)
AS
(
SELECT
*
FROM
T
),
SET1(V_TEST1, V_TEST2) AS (
SELECT
COUNT(*),
COUNT(*)
FROM
T2
)
SELECT
CAST(V_TEST1 AS DECIMAL) / V_TEST2
FROM SET1
;

[jefftyzzer]

On Jun 17, 2:40 am, Tonkuma <[e-mail]:

You can't use CTE in SET statement even in SQL Stored Procedure.

You wouldn't be neccesary to use SET variables, if you used CTE and
the SET statement was not in a loop.
For example:
CREATE FUNCTION TEST() RETURNS DECIMAL
SPECIFIC TEST
DETERMINISTIC
NO EXTERNAL ACTION
INHERIT SPECIAL REGISTERS
RETURN
WITH
T(C1)
AS
(
VALUES
(1)
),
T2(C1)
AS
(
SELECT
*
FROM
T
),
SET1(V_TEST1, V_TEST2) AS (
SELECT
COUNT(*),
COUNT(*)
FROM
T2
)
SELECT
CAST(V_TEST1 AS DECIMAL) / V_TEST2
FROM SET1
;

Thanks, Tonkuma. I ended up going with a "RETURNS TABLE" UDF, which
your reply confirmed was the way I had to go....

Regards,

--Jeff

[Serge Rielau]

jefftyzzer wrote:

Thanks, Tonkuma. I ended up going with a "RETURNS TABLE" UDF, which
your reply confirmed was the way I had to go....

There is no need to go to a table function as Tonkuma also showed.

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[jefftyzzer]

On Jun 19, 7:25 am, Serge Rielau <[e-mail]:

jefftyzzer wrote:

Thanks, Tonkuma. I ended up going with a "RETURNS TABLE" UDF, which
your reply confirmed was the way I had to go....

There is no need to go to a table function as Tonkuma also showed.

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

I see. I thought that even a 1-column, 1-row result was still a result
*set* (as it came from a SELECT) and the UDF would thus need to be
declared as returning a table.

--Jeff