DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2001
    New Brunswick, NJ

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


    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
           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  

  2. #2
    Join Date
    Nov 2002
    Geneva Switzerland
    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!
    "The power of instruction is seldom of much efficacy except in those happy dispositions where it is almost superfluous" - Gibbon, quoted by R.P.Feynman

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Click Here to Expand Forum to Full Width