Select email_address from
(

select distinct
xec.email_address
from
apps.xx_contact_emails xec
,disc.local_license_details lld
,apps.xx_license_contacts xxlc
,apps.ra_contact_roles rcr
,disc.local_contacts lc
where
lc.country != 'Japan'
AND NVL (lc.bad_email, 'N') ='N'
AND lc.contact_status = 'A'
AND nvl (lc.restricted_email, 'N') = 'N'
AND lc.contact_id = xec.contact_id
and lc.CONTACT_ID = rcr.CONTACT_ID
and rcr.CONTACT_ROLE_ID = xxlc.CONTACT_ROLE_ID
and xxlc.LICENSE_ID = lld.LICENSE_ID
and lld.version >= '6'
AND lld.license_status = 'Latest'
and rcr.usage_code in ('REGISTER', 'SITE', 'MARKET')
and not exists (select 1 from
as_interests_all ai
,as_interest_codes_all aic2
,as_interest_codes_all aic
,as_interest_types_all ait
where
lc.contact_id = ai.contact_id
AND aic.INTEREST_TYPE_ID = ait.INTEREST_TYPE_ID
AND aic.interest_code_id = ai.primary_interest_code_id
AND aic2.interest_code_id(+) = ai.secondary_interest_code_id
AND ait.interest_type = 'Marketing list'
and aic.code in ('Online Seminars', 'Wolfram Education Group')
and ai.status_code = 'REMOVE'
and rownum = 1)

UNION

select distinct
xec.email_address
from
apps.xx_contact_emails xec
, disc.local_contacts lc
where
lc.contact_creation_date > add_months(sysdate, -6)
and lc.country != 'Japan'
and lc.contact_id = xec.contact_id
and not exists (select 1 from
apps.ra_contact_roles rcr
where lc.contact_id = rcr.contact_id
and rcr.usage_code is not null
and rownum = 1)
) as table1


WHERE apps.xx_WEG_Event_biweekly_email_v.email_address not in 
(
SELECT DISTINCT
xec.email_address
FROM
apps.xx_contact_emails xec
,ra_contacts rco
,ra_customers rcu
,ra_addresses_ALL raa
,RA_CONTACTs_DFV RCD
,as_interests_all ai
,as_interest_codes_all aic2
,as_interest_codes_all aic
,as_interest_types_all ait
WHERE
rcu.customer_id = raa.customer_id
and rco.CONTACT_ID = xec.CONTACT_ID
AND raa.address_id = rco.address_id
AND rcu.sales_channel_code NOT IN ('CERTRESELL','RESELLER')
AND rco.STATUS = 'A'
AND RCU.STATUS = 'A'
AND rco.ROWID = rcd.row_id
AND ai.contact_id = rco.contact_id
AND aic.INTEREST_TYPE_ID = ait.INTEREST_TYPE_ID
AND aic.interest_code_id = ai.primary_interest_code_id
AND aic2.interest_code_id(+) = ai.secondary_interest_code_id
AND ait.interest_type = 'Marketing list'
and aic.code = 'Online Seminars'
and ai.status_code = 'REMOVE'
)