>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
