How to use a CTE in a DB2 user defined function

Does anyone know how I can use a CTE (with statement) in a DB2 user defined function?   I am trying to use the code below but it is not working:

create or replace function f_wds_manager_tree
(
    p_root_manager_id varchar (8)
)
returns table
(
    level integer,
    wkr_ent_id varchar (8),
    wkr_last_nm varchar (150),
    wkr_first_nm varchar (150),
    wkr_offc_country_cd varchar (3),
    wkr_type_cd character (1),
    full_part_time_in character (1),
    wkr_status_cd character (1),
    cbd varchar (42),
    cost_center_cd varchar (30),
    manager_ent_id varchar (8)
)
    return
    (
        with cte_temp
        (
            level,
            wkr_ent_id,
            wkr_last_nm,
            wkr_first_nm,
            wkr_offc_country_cd,
            wkr_type_cd,
            full_part_time_in,
            wkr_status_cd,
            cbd,
            cost_center_cd,
            manager_ent_id
        )
        as
        (
            select
                1,
                wkr_ent_id,
                wkr_last_nm,
                wkr_first_nm,
                wkr_offc_country_cd,
                wkr_type_cd,
                full_part_time_in,
                wkr_status_cd,
                cbd,
                cost_center_cd,
                manager_ent_id
            from wds_current
            where manager_ent_id = p_root_manager_id
            union all
            select
                level+1,
                child.wkr_ent_id,
                child.wkr_last_nm,
                child.wkr_first_nm,
                child.wkr_offc_country_cd,
                child.wkr_type_cd,
                child.full_part_time_in,
                child.wkr_status_cd,
                child.cbd,
                child.cost_center_cd,
                child.manager_ent_id
            from cte_temp parent, wds_current child
            where
                parent.wkr_ent_id = child.manager_ent_id
        )
   
        select * from cte_temp;
    );
LVL 4
DrLechterAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dave FordSoftware Developer / Database AdministratorCommented:
When you say "it is not working", what exactly do you mean? Specifically, what error message are you getting?
0
DrLechterAuthor Commented:
I get an error message when I try to create the function saying:

ERROR [42601] [IBM][DB2/LINUXZ64] SQL0104N An unexpected token "as" was found following "_ent_id ) ".
0
Dave FordSoftware Developer / Database AdministratorCommented:
In situations like this, it seems more "natural" (to me) to use a stored-procedure that simply returns a result-set. But, if you definitively want to use a function, the following code compiles for me on DB2 for i (v6.1):

create function MyScema.test4EE ()
returns table (abvc varchar(100))
language SQL
BEGIN
return
with aCTE as (
select oneBigVarChar
  from MySchema.deleteme
 where oneBigVarChar like '%this%'
)
select *
  from aCTE;
END

Open in new window


Function TEST4EE was created in MYSCHEMA.

HTH,
DaveSlash
0
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

DrLechterAuthor Commented:
For some reason, I cannot get this to run without an error.  Would you please setup an example that selects a column from sysdummy?
0
Dave FordSoftware Developer / Database AdministratorCommented:
Sure. This example is somewhat "contrived", but it does compile:

create function MySchema.test4EE_v2 ()
returns table (aTS  timestamp)
language SQL
set option dbgview = *source
BEGIN
return
with aCTE as (
select current_timestamp
  from sysibm.sysdummy1
)
select *
  from aCTE;
END

Open in new window


HTH,
DaveSlash
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
DrLechterAuthor Commented:
Thanks, it is working now.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
DB2

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.