组合条件查询,以下两种方式都可以:
select b.job_id,
b.source,
b.create_via,
b.who_create,
b.location_code,
b.ref_no
from (select rownum r, a.*
from (select j.*, cj.location_code, cj.ref_no
from nfs_gbl.combine_job j, nfs_gbl.combine_job_criteria cj
where j.status = 0
and j.job_id = cj.job_id
and ((trim(cj.location_code) || trim(cj.ref_no) != 'A0ADWHBATCH091501')
or (trim(cj.location_code) || trim(cj.ref_no) != 'A0ADWHBATCH091502'))
order by j.job_id) a) b
where r < 2;
select b.job_id,
b.source,
b.create_via,
b.who_create,
b.location_code,
b.ref_no
from (select rownum r, a.*
from (select j.*, cj.location_code, cj.ref_no
from nfs_gbl.combine_job j, nfs_gbl.combine_job_criteria cj
where j.status = 0
and j.job_id = cj.job_id
and (cj.location_code, cj.ref_no) not in
(select cjc.location_code, cjc.ref_no
from nfs_gbl.combine_job_criteria cjc
where ((cjc.location_code = 'A0AD' and
cjc.ref_no = 'WHBATCH091501') or
(cjc.ref_no = 'WHBATCH091502' and
cjc.location_code = 'A0AD')))
order by j.job_id) a) b
where r < 2;
select b.job_id,
b.source,
b.create_via,
b.who_create,
b.location_code,
b.ref_no
from (select rownum r, a.*
from (select j.*, cj.location_code, cj.ref_no
from nfs_gbl.combine_job j, nfs_gbl.combine_job_criteria cj
where j.status = 0
and j.job_id = cj.job_id
and ((trim(cj.location_code) || trim(cj.ref_no) != 'A0ADWHBATCH091501')
or (trim(cj.location_code) || trim(cj.ref_no) != 'A0ADWHBATCH091502'))
order by j.job_id) a) b
where r < 2;
select b.job_id,
b.source,
b.create_via,
b.who_create,
b.location_code,
b.ref_no
from (select rownum r, a.*
from (select j.*, cj.location_code, cj.ref_no
from nfs_gbl.combine_job j, nfs_gbl.combine_job_criteria cj
where j.status = 0
and j.job_id = cj.job_id
and (cj.location_code, cj.ref_no) not in
(select cjc.location_code, cjc.ref_no
from nfs_gbl.combine_job_criteria cjc
where ((cjc.location_code = 'A0AD' and
cjc.ref_no = 'WHBATCH091501') or
(cjc.ref_no = 'WHBATCH091502' and
cjc.location_code = 'A0AD')))
order by j.job_id) a) b
where r < 2;