September 6th, 2012, 04:54 AM
Join Date: Jan 2003
Location: Paris Uppland
There are multiple problems with your query.
Aggregate functions can not be used in a where clause, you should use a having clause.
The between clause is misformed. The subquery is seen as one expression only.
The expression in the select list of the subquery
term_begin_date AND term_end_date
is interpreted as a boolean expression, not as two different values.
One possible reformulation of your query is
and h.emplid in
where PS_TERM_TBL.strm = '1128'
and PS_TERM_TBL.institution = 'NT752'
and PS_TERM_TBL.acad_career = 'UGRD')
having max(effdt) between PS_TERM_TBL.term_begin_date and PS_TERM_TBL.term_end_date)