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 a.nr = b.nr

  join trechnungen c on b.jahr = c.jahr and b.nr = c.nr 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

  begin

    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;

  End

How can I can I build a CTE outside?

Thank you

Best regards

Olaf