Prev: Filesystem configuration on HP UX for DB2
Next: Multiple paths in references
From: Tonkuma on 23 Apr 2010 13:03
DB2 SQL Table Function have a restriction
that once a RETURN statement was executed, no control would be
returned to the function(as you know).

A way to avoid this restriction and to return multiple rows from a
table function
is to use a return statement with recursive common table expression.

Here is a sample...

------------------------- Commands Entered -------------------------
CREATE OR REPLACE FUNCTION dbo.FCOMBitMask
( BitCnt INTEGER
, Mask INTEGER
)
RETURNS TABLE
( Val INTEGER )
LANGUAGE SQL
READS SQL DATA
SPECIFIC dbo.FUNC1
RETURN
WITH while_loop( i , flag ) AS (
VALUES ( -1 , 'N' )
UNION ALL
SELECT i + 1
, CASE BITAND(i + 1 , Mask)
WHEN Mask THEN
'Y'
ELSE 'N'
END
FROM while_loop
WHERE i < 2147483647
AND i < POWER(2, BitCnt) - 1
)
SELECT i
FROM while_loop
WHERE flag = 'Y'
;
--------------------------------------------------------------------
DB20000I The SQL command completed successfully.

------------------------- Commands Entered -------------------------
SELECT *
FROM TABLE( dbo.FCOMBitMask(5 , 13) )
;
--------------------------------------------------------------------

VAL
-----------
13
15
29
31

4 record(s) selected.

 | 
Pages: 1
Prev: Filesystem configuration on HP UX for DB2
Next: Multiple paths in references