Get the audit numbers that are not closed (i.e.closed flag = O and ACCESSORY-APPL-FLAG = 'AP' in quality_ audit table) and the respective latest revision_number that is saved (i.e .status flag = Y in audit_revision table)

quality_ audit table colomns are

audit_number
.closed flag
ACCESSORY-APPL-FLAG


audit_revision table colomns are
audit_number
revision_number
status flag

i have done this as following but getting error inner querry returning more than one row in single fetch:
select audit_number, revision_number
from qh.qh_audit_revision
where audit_number =
(select audit_number
from qh.qh_quality_audit
where ACCESSORY_APPL_FLAG = 'AP' and closed_flag = 'O') and
status_flag = 'Y'

Oracle LazyDBA home page