Archives of the TeradataForum

Message Posted: Fri, 23 Jul 2010 @ 13:14:45 GMT

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

Hello everyone -

I have a situation where I've got a child parent like table, and I don't know how many possible levels there are, and I'd like to get all the parent's children. Is there any way to do something like this?

Here is an example, and the path I've gone down:

     CREATE VOLATILE TABLE vt
     (processId int
     , inputProd VARCHAR(10)
     , outputProd VARCHAR(10)
     , qtyUsed int
     ) ON COMMIT PRESERVE ROWS;

     bt;
     insert into vt values(101, 'paper', 'JumboR', 1000);
     insert into vt values(101, 'glue', 'JumboR', 500);
     insert into vt values(101, 'core', 'JumboR', 1);
     insert into vt values(201, 'JumboR', 'RedTape', 1);
     insert into vt values(201, 'DyeRed', 'RedTape', 200);
     insert into vt values(202, 'JumboR', 'GreenTape', 1);
     insert into vt values(202, 'DyeGreen', 'GreenTape', 200);

     insert into vt values(203, 'GreenTape', 'DecoTape1', 1);
     insert into vt values(203, 'Overlay1', 'DecoTape1', 1);

     insert into vt values(901, 'JumboR', 'JumboR', 1);
     insert into vt values(902, 'JumboR', 'paper', 1);
     insert into vt values(902, 'chemical', 'paper', 1);
     et;


     /*
     select *
     from vt;

     select vt.inputProd, vt.outputProd, vt2.outputProd
     from vt
     left outer join (
             select inputProd, outputProd
             from vt
             group by 1,2
     ) vt2
     on vt2.inputProd = vt.outputProd
     group by 1,2,3
     */

     /* This method would work, but I'd have to assume a specific number of
     levels, and continue to nest these "or" statements */
     select outputProd
     from vt
     where (inputProd = 'paper'
             or inputProd in (
                     select outputProd
                     from vt
                     where inputProd = 'paper'
                     or inputProd in (
                             select outputProd
                             from vt
                             where inputProd = 'paper'
                     )
             )
     );

     drop table vt;

Thank you in advance for any help you can offer!

Chris

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