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

Reply via email to