SQL Recursion and UDFT...

Hi all,

I'm working on (at this point) an exercise for personal learning. My
final goal is to pass a program name to a program/procedure and this will
return as the files accessed by the job stream.

I created a UDTF

CREATE OR REPLACE FUNCTION
rrogersonw.Get_Called_Program_Ref(main_program VarChar(10))
RETURNS TABLE (whfnam Char(10) )
LANGUAGE SQL
MODIFIES SQL DATA
BEGIN
DECLARE CLCmd VarChar(256) Not NULL Default '';
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
BEGIN
DECLARE ERROR_HIT INTEGER;
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
SET ERROR_HIT = 1;
DROP TABLE QTEMP/pgmrefpgm;
CREATE TABLE QTEMP/pgmrefpgm as (
SELECT * FROM QADSPPGM ) WITH NO DATA;
END;

SET CLCmd = 'DSPPGMREF PGM(' concat main_program concat ') '
Concat ' OUTPUT(*OUTFILE) '
Concat ' OUTFILE(QTEMP/PGMREFPGM) '
Concat ' OUTMBR(*FIRST *REPLACE) ';

CALL QCMDEXC(CLCmd, LENGTH(CLCmd));

RETURN SELECT whfnam
FROM QTEMP/pgmrefpgm
WHERE whotyp = '*PGM'
AND SUBSTRING(whfnam, 1, 1) <> 'Q'
;
END;

So if I call my UDTF. Also note that I am only selecting *PGM types to
keep it easier at the start.

SELECT *
FROM TABLE(RROGERSONW.Get_Called_Program_Ref('SB0010', 1)) x;

It correctly returns a table with the programs called by SB0010. My next
step is to recursively call the UDTF for the called programs.

This is the sql statement I'm trying to use.

-- Use recursion to find all the programs in a jobstrean
WITH pgms (level, whfnam) AS (

-- Initialization
VALUES(0,'SB0010')

UNION ALL
SELECT 1, x.whfnam
FROM TABLE(RROGERSONW.Get_Called_Program_Ref('SB0010')) x

-- Recursion
UNION ALL
SELECT level+1, a.whfnam
FROM TABLE(RROGERSONW.Get_Called_Program_Ref(x.whfnam)) a
JOIN pgms b
ON a.whfnam = b.whfnam
fetch first 100 rows only)

-- Final/Main Select
SELECT level, whfnam
FROM pgms
WHERE level <= 3
ORDER BY 1;

I get the error

SQL State: 42703
Vendor Code: -206
Message: [SQL0206] Column or global variable WHFNAM not found.

When I try to run the statement.

Is it even possible to do what I'm trying to do? If so, how do I qualify
whfnam? Or can someone show me how I should be writing the statement.
I tried to write the query based on a presentation Brigitta have at
Commons in Europe. ([1]https://www.youtube.com/watch?v=VeMAmMYCHI0)

Thanks for any help.

Rob

References

Visible links
1. https://www.youtube.com/watch?v=VeMAmMYCHI0