Sql function - ALL behaviour

[Megha Vishwanath]

Hello,

I'm trying to improve a sql function:

Currently I am building up a query by appending to the query each assessment id.

Can a function like ALL be used here to simplify this you think?

FOR i in array_lower(pgmids,1)..array_upper(pgmids,1)
LOOP
asmnts := ARRAY(select distinct id from assmt where pid=pgmids[i]);
query:='SELECT s.id as id,ass.pid as pid,cl.name as clname,c.sex as sex, c.mt as mt, count(distinct stu.id) AS count FROM ans se, ques q,assmt ass,student stu, classes cl, stu_class_relation sc, child c, school s WHERE se.objid=stu.id and se.qid=q.id and q.assid=ass.id and sc.stuid=stu.id and sc.clid=cl.id AND cl.sid = s.id AND stu.cid = c.id and (se.grade is not null or se.mark is not null)';
FOR i in array_lower(asmnts,1)..array_upper(asmnts,1)
LOOP
query:= query||' and se.objid in (select se.objid from ans se,ques q where se.qid=q.id and (se.grade is not null or se.mark is not null) and q.assid = '||asmnts[i]||')';
END LOOP;
query=query||'GROUP BY s.id, ass.id,cl.id,c.sex,c.mt';
FOR sch in EXECUTE query
LOOP
insert into agg_pgm_cohorts values (sch.id,sch.pid,sch.clname,sch.sex,sch.mt,sch.count);
END LOOP;