Subquery help - one works, two doesnt - Informix

Queries having "in" are slow, and so are queries having "or".

But I suppose that the problem is elsewhere.
Perhaps the database engine is confused by your alias "s".

You use it in three different circunstances.
I would rather use s1, s2, and s3.

select distinct r.studentid, r.studentname, r.building || '*' || trim(r.grade) || '*' || trim(r.currcode) combo
from reg r, seats s1
where
r.status = 'A' and
r.building = s1.bldg and
trim(r.currcode) <> ''
and

(r.building || '*' || trim(r.grade) || '*' || trim(r.currcode) ) not in (
select distinct s2.bldg || '*' || trim(s2.grade) || '*' || trim(s2.prog) combo from seats s2)
or
(r.building || '*' || trim(r.grade) || '*' || trim(r.currcode) ) in (
select distinct s.bldg || '*' || trim(s3.grade) || '*' || trim(s3.prog) combo from seats s3
where s3.seats = 0)