Ora-01719: outer join operator not allowed in operand of OR or IN

[mrpaulwass]

Hello,

I have the following large query where I need to compare values, but I'm prohibited because of the outer join.

Is there any way to get around this?? I tried using a function, but ran into the same type of problem.

Thanks ahead of time!


Below is a Snippet of the larger code shown further below.

--***********************************************************************   
   AND ((clinic_cov.primary_coverage_b(+) = 'Y' AND clinic_pc.coverage_segment_code='MAIN')
     OR (clinic_cov.primary_coverage_b(+) = 'N' AND clinic_pc.coverage_segment_code='TAIL'))
--***********************************************************************

SELECT DISTINCT c.claim_no || ''                                                                  claim_no,
       c.occurrence_no || ''                                                             occurrence_no,  
       cs_get_role_list(c.claim_pk, 'EXAMINER', null, null, null)                        examiner_name,
       cs_get_client_name(clinic_risk.entity_fk)                                         clinic_name,
       nvl(ci_report.get_type_phone(clinic_risk.entity_fk, 'CLAIM'),
           ci_report.get_phone(clinic_risk.entity_fk))                                   clinic_phone,
       cs_get_client_name_formatted(c.insured_entity_fk, '^F^ ^M^ ^L^ ^S^', '^O^')       insured_name,
       nvl(ci_report.get_type_phone(c.insured_entity_fk, 'CLAIM'),
           ci_report.get_phone(c.insured_entity_fk))                                     insured_phone,

...
...
...

  FROM claim_summary_view    c, 
       claim_insured         ci, 
       risk                  r,
       risk_class            rc,
       coverage_insured_xref prim,
       coverage_insured_xref xs,
       risk_class_profile    rcp, 
       /* primary coverage goes from CLAIM_INSURED to COVERAGE to PRODUCT_COVERAGE */
       coverage              prim_cov,
       product_coverage      prim_pc, 
       /* primary coverage goes from CLAIM_INSURED to COVERAGE to PRODUCT_COVERAGE */
       coverage              xs_cov,
       product_coverage      xs_pc,
       /* clinic primary coverage goes from COVERAGE_INSURED_XREF to claim primary RISK to
          COVERAGE to PRODUCT_COVERAGE and COVERAGE_LIMIT_CODE */
       risk                  clinic_risk, 
       policy                clinic_pol,
       coverage              clinic_cov, 
       product_coverage      clinic_pc, 
       coverage_limit_code   clc,
       license_profile       lp
 WHERE c.insured_role_pk = ci.entity_role_fk
   AND c.insured_entity_fk = rcp.entity_fk(+)
   AND nvl(rcp.risk_class_profile_pk,0) =
       (SELECT nvl(min(risk_class_profile_pk),0)
          FROM risk_class_profile rcp2
         WHERE rcp2.entity_fk = c.insured_entity_fk)
   AND prim.claim_insured_fk = ci.claim_insured_pk
    /* get the primary coverage on the claim */
   AND prim.coverage_fk = prim_cov.coverage_pk(+)
   AND prim_cov.product_coverage_code = prim_pc.code(+)
   AND nvl(prim.incident_low_value, 0) = 0 
   AND prim.risk_fk = r.risk_pk(+)
   AND r.risk_cls_used_to_rate = rc.code(+)
   AND clinic_risk.primary_risk_b(+) = 'Y'
   /* 
   AND clinic_risk.record_mode_code(+) = 'OFFICIAL'
   AND clinic_risk.accounting_to_date(+) = to_date('01013000', 'mmddyyyy')
   AND clinic_risk.base_record_b(+) = 'N'  */
   /* get the clinic risk record */
   AND r.policy_fk = clinic_risk.policy_fk(+)
   AND clinic_risk.policy_fk = clinic_pol.policy_pk(+)
   /* Get the clinic primary coverage */
   AND clinic_risk.risk_base_record_fk = clinic_cov.risk_base_record_fk(+)
   AND clinic_cov.record_mode_code(+) = 'OFFICIAL'
   AND clinic_cov.accounting_to_date(+) = to_date('01013000', 'mmddyyyy')
   AND clinic_cov.base_record_b(+) = 'N'


--***********************************************************************   
   AND ((clinic_cov.primary_coverage_b(+) = 'Y' AND clinic_pc.coverage_segment_code='MAIN')
     OR (clinic_cov.primary_coverage_b(+) = 'N' AND clinic_pc.coverage_segment_code='TAIL'))
--***********************************************************************

   AND clinic_cov.product_coverage_code = clinic_pc.code(+)
   AND clinic_cov.coverage_limit_code = clc.code(+)
   AND nvl(clc.incident_low_value(+), 0) = 0  
...
...
...

[DaPi]

I think you can get round this by turning the OR into a UNION. But rather than have an unmaintainable UNION of two massive queries, I'd first look at making an in-line view involving those two tables and a UNION.

BTW: Do you REALLY need all those outer joins? I'm always suspicious when I see so many. Swiss cheese effect - so much missing data!