Hello, i have three select statments that work, fine when i link tem together when using oracle reports, but i dont want to use oracle reports, so i want to join them up, using outer joins to make sure i dont overlook records.
Here they are :
select distinct benefit_transactions.btr_cpa_cla_refno
,parties.par_per_surname
,addresses.adr_line_all
,rbx151_schemes_data.description
,rbx151_schemes_data.SCHEME_NO
,btr_pla_refno
,nvl2 (claim_parts.cpa_suspended_date, 'Y', 'N')' AS SUSP
from fsc.address_usages
,fsc.address_elements
,fsc.addresses
,fsc.parties
,fsc.properties
,claim_periods
,benefit_transactions
,rbx151_schemes_cl
,rbx151_schemes_data
,claim_roles
,claim_property_occupancies
,claim_hb_payment_schemes
,claims
,claim_parts
where address_elements.ael_street_index_code = addresses.adr_ael_street_index_code
and addresses.adr_refno = address_usages.aus_adr_refno
and properties.pro_refno = address_usages.aus_pro_refno
and properties.pro_refno = claim_property_occupancies.cpo_pro_refno
and rbx151_schemes_cl.scheme_no = rbx151_schemes_data.scheme_no
and claim_roles.cro_crt_code = 'CL'
and claim_roles.cro_end_date is null
and claim_periods.cpe_cpa_cla_refno = claim_roles.cro_cla_refno
and parties.par_refno = claim_roles.cro_par_refno
and claim_property_occupancies.cpo_cla_refno = claim_periods.cpe_cpa_cla_refno
and claim_property_occupancies.cpo_cla_refno = benefit_transactions.btr_cpa_cla_refno
and claim_periods.cpe_cpa_cla_refno = benefit_transactions.btr_cpa_cla_refno
and benefit_transactions.btr_cpa_cla_refno = rbx151_schemes_cl.claim_no
and claim_roles.cro_cla_refno = claim_property_occupancies.cpo_cla_refno
and claim_periods.cpe_cpo_pro_refno = rbx151_schemes_cl.pro_refno
and claim_periods.cpe_cpa_cpy_code = 'HB'
and claim_periods.cpe_cps_code = 'A'
and claim_periods.cpe_cpa_cpy_code = benefit_transactions.btr_cpa_cpy_code
and rbx151_schemes_cl.claim_no like '406%'
-- and benefit_transactions.btr_cpa_cla_refno = '307801231'
--and parties.par_per_surname = 'HAIRE'
and claim_property_occupancies.cpo_pro_refno = rbx151_schemes_cl.pro_refno
and claim_periods.cpe_cpa_cla_refno = claim_parts.cpa_cla_refno --MORE ADDED CODE!!
and claims.cla_refno = claim_hb_payment_schemes.chp_cla_refno --ADDED CODE!!!
AND claims.cla_refno = claim_roles.cro_cla_refno --ADDED CODE!!!
and (claim_hb_payment_schemes.chp_pty_code ='CL' or claim_hb_payment_schemes.chp_pty_code ='LL') --ADDED CODE
and claim_periods.cpe_created_date =
(select max(c2.cpe_created_date)
from claim_periods c2
where c2.cpe_cpa_cla_refno = claim_periods.cpe_cpa_cla_refno
and claim_periods.cpe_cpa_cpy_code = c2.cpe_cpa_cpy_code )
and claim_property_occupancies.cpo_created_date =
(select max(cp2.cpo_created_date)
from claim_property_occupancies cp2
where cp2.cpo_cla_refno = claim_property_occupancies.cpo_cla_refno)
and benefit_transactions.btr_created_date =
(select max(b2.btr_created_date)
from benefit_transactions b2
where b2.btr_cpa_cla_refno = benefit_transactions.btr_cpa_cla_refno)
and claim_parts.CPA_CREATED_DATE =
(select max(c1.CPA_CREATED_DATE)
from claim_parts c1
where c1.CPA_CREATED_DATE = claim_parts.CPA_CREATED_DATE)
btr_cpa_cla_refno = private_ll_accounts.pla_refno
select private_ll_accounts.pla_refno,
private_ll_accounts.pla_par_refno
from private_ll_accounts
where private_ll_accounts.pla_created_date =
(select max(p2.pla_created_date)
private_ll_accounts.pla_refno = private_ll_pay_schemes.PLP_PLA_REFNO
select distinct private_ll_pay_schemes.PLP_PLA_REFNO, private_ll_pay_schemes.PLP_BAK_ACCOUNT_NUMBER
from private_ll_pay_schemes
where private_ll_pay_schemes.PLP_START_DATE =
(select max(p1.PLP_START_DATE)
from private_ll_pay_schemes p1
where p1.PLP_PLA_REFNO = private_ll_pay_schemes.PLP_PLA_REFNO
and private_ll_pay_schemes.PLP_PLA_REFNO = p1.PLP_PLA_REFNO (+))
Thanks crazypants!!!