× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



Thanks Mike.  I tried what you suggested but it's not working out of the box.  I'll tinker with it tonight and see if I can't get it to work.

Thanks,

Rob


On 1/31/2018 1:02 PM, Mike Jones wrote:
Hi Rob,

There's a good chance you should be able to do what you want, although I've
never tried using two UNION ALL statements in recursive SQL.

One risk is whether or not you get any locking deadlocks (hangs) recreating
table QTEMP/PGMREFPGM over and over within the scope of a single SQL
statement. Since it is a QTEMP object you're probably OK, but objects
modified inside functions can be subject to locking side effects (deadlocks
in certain cases).

The error is because "x.whfnam" is not in scope when accessed in the second
function call reference (specifically the "x" correlation name is not in
scope).

I think this should be closer to what you want:

WITH pgms (level, whfnam) AS (

CTE_PARMS ( level, main_program ) as (
VALUES(0,'SB0010')
)

-- Initialization

SELECT p.level, p.main_program
FROM CTE_PARMS p

UNION ALL

SELECT p.level + 1, i.whfnam
FROM CTE_PARMS p
JOIN TABLE(RROGERSONW.Get_Called_Program_Ref( p.main_program )) i

UNION ALL

-- Recursion
SELECT a.level + 1, r.whfnam
FROM pgms a
CROSS JOIN TABLE(RROGERSONW.Get_Called_Program_Ref( a.whfnam )) r
fetch first 100 rows only

)

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

Mike


date: Wed, 31 Jan 2018 08:45:58 -0500
from: Robert Rogerson <rogersonra@xxxxxxxxx>
subject: 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




As an Amazon Associate we earn from qualifying purchases.

This thread ...

Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.