Subject CTE
Author Check_Mail

Hello everybody,


actually I have a stored procedure where I get the needet material for each product, some of them I get many times. Now I would like to create A CTE and gets the amount of each material.


In my case I call a stored procedure and those calls itself.


Here the example:


  for select a.teilenr, sum(a.menge) from tklpos a join tkl b on a.jahr = b.jahr and =

  join trechnungen c on b.jahr = c.jahr and = join tteile d on a.teilenr = d.teilenr

  where extract(year from c.datum) = coalesce(:jahr,extract(year from c.datum))

  and extract(month from c.datum) = coalesce(:monat,extract(month from c.datum)) and a.teilenr = coalesce(:t, a.teilenr)

  and d.umsatztyp = coalesce(:umsatztyp,d.umsatztyp) and c.fa = coalesce(:fa, c.fa) and b.kundennr = coalesce(:kunde,b.kundennr) and c.gutschrift = 0

  group by a.teilenr

  into :teil, :menge do


    if(menge is null) then menge = 0;

    for select materialnr, pteilenr, urspteilenr, anzahlm, lieferant from P_GETTEILEMATPS_2014(:teil, :menge, -1) into

    :matnr, :pteilenr, :urspteilenr, :anz, :lieferant do suspend;



How can I can I build a CTE outside?


Thank you


Best regards