> from orgchart p, orgchart c
> inner join organisation pn on pn.orgid = p.parentid
Don't mix implicit and explicit JOIN in the same statement, change to
something like:
for select p.parentid
from orgchart p
inner join orgchart c on p.childid = c.parentid
and p.depth+c.depth between 1 and 2
inner join organisation pn on pn.orgid = p.parentid
inner join organisation cn on cn.orgid = c.childid
where p.childid=10
into :Vorgid do
(if there's no relation between the tables, then CROSS JOIN or FULL
OUTER JOIN can sometimes (although rarely) be handy.
Also, when you don't need to know how many matches there are, you would
generally want to replace code like
> Select count(assid)
> from Assignment
> where gdlid = :in_gdlid
> and orgid = :IN_ORGID into PorgExist;
> if (PorgExist =0) then
with
if (not exists(Select *
from Assignment
where gdlid = :in_gdlid
and orgid = :IN_ORGID)) then
SELECT COUNT can be time consuming and should only be used when the
number of items returned matters, not when just checking if rows exist
or not.
HTH,
Set
