>One question again,

>
>it works fine by executing in the sql editor, now I would like insert into my stored procedure:
>
>  with mkat as (
>  SELECT node.id
>  FROM tmatkat AS node,
>  tmatkat AS parent
>  WHERE node.lft BETWEEN parent.lft AND parent.rgt
>  AND parent.id = :smatkat
>  ORDER BY parent.lft
>  )
>  for select m.id, m.bezeichnung, m.lagermenge, m.bestellnr, r.raumname, m.lagerort, h.hersteller, m.bestellnr
>  from tmaterial m left join thersteller h on (m.hersteller = h.id)
>  left join traeume r on (m.raum = r.id)
>  join tmaterial_kat e on m.id = e.matid
>  join mkat f on e.katid = f.id
>  group by m.id, m.bezeichnung, m.lagermenge, m.bestellnr, r.raumname, m.lagerort, h.hersteller,  m.bestellnr
>  into  :a_lid, :bezeichnung, :lagermenge, :bestellnr, :raum, :lagerort, :hersteller, :sbestellnr do suspend;
>
>Where can be the error? "for" should be the problem.

Simple, Olaf, WITH is part of your select, so use FOR WITH... rather than WITH ... FOR.

By the way, I think your query can be simplified to:

for select distinct m.id, m.bezeichnung, m.lagermenge, m.bestellnr, r.raumname, m.lagerort, h.hersteller, m.bestellnr
from tmaterial m
join tmaterial_kat e on m.id = e.matid
join tmatkat node on e.katid = node.id
join tmatkat parent ON node.lft BETWEEN parent.lft AND parent.rgt
left join thersteller h on m.hersteller = h.id
left join traeume r on m.raum = r.id
where parent.id = :smatkat
into :a_lid, :bezeichnung, :lagermenge, :bestellnr, :raum, :lagerort, :hersteller, :sbestellnr do suspend;

(left joins normally ought to be towards the bottom of a query, it gives the optimizer more choice)

HTH,
Set