SQL ricorsivo Programmi
- Dettagli
- Categoria: Faq AS400
- Pubblicato Giovedì, 14 Maggio 2015 13:19
- Scritto da Super User
- Visite: 1193
DSPPGMREF PGM(T2OBJ/*ALL) OUTPUT(*OUTFILE) OBJTYPE(*PGM *SRVPGM) OUTFILE(QTEMP/PREF)
create table qtemp/refpgm0f as (
SELECT distinct WHPNAM , WHFNAM , WHOTYP
FROM refpgm0f WHERE WHOTYP in ('*PGM' , '*SRVPGM') and
substr( WHPNAM , 1 , 1) not in ('*' , ' ' , 'Q' ) and
substr( WHFNAM , 1 , 1) not in ('*' , ' ' , 'Q' ) ) with data;
with PGM_list (
level ,WHPNAM , WHFNAM , Sentinel ) as (
select 1 level , WHPNAM , WHFNAM , CAST(WHPNAM concat '-> '
concat WHFNAM concat '-> '
AS VARCHAR(32000)) as Sentinel from qtemp/refpgm0f
where WHPNAM = 'primo pgm'
union all select o.level + 1 , next_layer.WHPNAM ,
next_layer.WHFNAM , Sentinel concat
next_layer.WHFNAM concat '-> ' from qtemp/refpgm0f
as next_layer
, PGM_list o where o.WHFNAM = next_layer.WHPNAM and o.level < 30
and locate( next_layer.WHFNAM , Sentinel ) = 0
) select distinct
* from PGM_list
order by 4 , 1 , 2 , 3 ;
Documentazione:
http://www.ibm.com/developerworks/ibmi/library/i-db2connectby/index.html
Video fantastico su utilizzo Common Table Expression
https://www.youtube.com/watch?feature=player_embedded&v=VeMAmMYCHI0
