Archives of the TeradataForum

Message Posted: Tue, 10 May 2011 @ 16:19:49 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

I am trying to use recursive sql to grab an output and all inputs that went into making the output. I have the following SQL that gives me a spool error. Is there maybe another way to do this. The table is rather large, so I would like to do it the quickest way.

     WITH RECURSIVE ALL_IO
     (Inp_Actg_Matl_Nbr
     ,Out_Actg_Matl_Nbr
     ,mmm_fclt_code
     ,level) AS
     (
     SELECT DPSSAV01.VPSSA_Prdn_Summ_Fact_L1.Inp_Actg_Matl_Nbr
     ,DPSSAV01.VPSSA_Prdn_Summ_Fact_L1.Out_Actg_Matl_Nbr
     ,DPSSAV01.VPSSA_Prdn_Summ_Fact_L1.mmm_fclt_code
     , 0
     FROM DPSSAV01.VPSSA_Prdn_Summ_Fact_L1
     WHERE Out_Actg_Matl_Nbr = '98011113093'
     UNION ALL
     SELECT  ALL_IO.Inp_Actg_Matl_Nbr
     ,ALL_IO.Out_Actg_Matl_Nbr
     ,ALL_IO.mmm_fclt_code
     ,ALL_IO.Level + 1
     FROM ALL_IO INNER JOIN DPSSAV01.VPSSA_Prdn_Summ_Fact_L1 ON ALL_IO.Inp_Actg_Matl_Nbr =
     DPSSAV01.VPSSA_Prdn_Summ_Fact_L1.Out_Actg_Matl_Nbr
     AND ALL_IO.Level < 2
     )
     SELECT * FROM ALL_IO;

Thanks

Mark M. Flynn | Supply Chain Analyst

	<Prev	Next>		<<First	<Prev	Next>	Last>>