UDF RETURNS DIFFERENT TABLE

[Massimiliano Campagnoli]

I would like to write an UDF function that returns table A or table B
based on a value passed as argument.

Something like:

CREATE FUNCTION my_func(my_value INTEGER)
....
RETURNS TABLE (C1 INTEGER, C2 INTEGER)
....
BEGIN ATOMIC
RETURN
if my_value = 1 then
SELECT C1,C2 FROM TABLEA
else
SELECT C1,C2 FROM TABLEB
Is it possible to include an IF inside a RETURN ?

[Lennart]

On Jul 6, 4:50 pm, Massimiliano Campagnoli <[e-mail]>
wrote:

I would like to write an UDF function that returns table A or table B
based on a value passed as argument.

Something like:

CREATE FUNCTION my_func(my_value INTEGER)
...
RETURNS TABLE (C1 INTEGER, C2 INTEGER)
...
BEGIN ATOMIC
RETURN
if my_value = 1 then
SELECT C1,C2 FROM TABLEA
else
SELECT C1,C2 FROM TABLEB

Is it possible to include an IF inside a RETURN ?

You can achieve the same thing without if then else:

create function X (my_value INTEGER)
returns table (c1 int, c2 int)
return
select * from A where my_value = 1
union
select * from B where my_value <1

/Lennart

[Serge Rielau]

Lennart wrote:

On Jul 6, 4:50 pm, Massimiliano Campagnoli <[e-mail]>
wrote:

>I would like to write an UDF function that returns table A or table B
based on a value passed as argument.

Something like:

CREATE FUNCTION my_func(my_value INTEGER)
...
RETURNS TABLE (C1 INTEGER, C2 INTEGER)
...
BEGIN ATOMIC
RETURN
if my_value = 1 then
SELECT C1,C2 FROM TABLEA
else
SELECT C1,C2 FROM TABLEB

Is it possible to include an IF inside a RETURN ?

You can achieve the same thing without if then else:

create function X (my_value INTEGER)
returns table (c1 int, c2 int)
return
select * from A where my_value = 1
union
select * from B where my_value <1

Make that a UNION ALL.

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab