OUTER JOIN & NVL
Hi,
I have the following query and need to do an outer join but its complaining that i cant use an outerjoin with an NVL. I have heard about using views, but i prefer to get arround it by adapting my sql code. Is there any way that i could get this SQL to outer join as it works fine without the NVL.....thanks
SELECT
c.ln_list_number,
c.case_id ,
c.cl_id,
c.claim_sequence_number,
spe.dentist_ios_fee,
format_postcode (NVL (c.claim_postcode, p.postcode)) "Post_Code",
p.pat_id,
p.surname,
p.forename,
p.date_of_birth,
ps.scsimd5,
spe.orig_pay_date,
c.registration_acceptance_date
FROM patients p,
postcode_simd ps,
claims c
,schedule_payment_entries spe
WHERE spe.orig_pay_date BETWEEN TO_DATE ('01/04/2007', 'DD/MM/YYYY')
AND TO_DATE ('31/12/2007', 'DD/MM/YYYY')
AND spe.cl_cl_id = c.cl_id
AND c.registration_acceptance_date >= TO_DATE ('01/04/2007', 'DD/MM/YYYY')
AND p.pat_id = c.pat_pat_id
AND ps.pc7 (+) = format_postcode (NVL (c.claim_postcode, p.postcode))
;