Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

OUTER JOIN & NVL

Oracle_81Apr 22 2008 — edited Apr 22 2008
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))
;
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on May 20 2008
Added on Apr 22 2008
7 comments
2,021 views