-
Ora-01719: outer join operator not allowed in operand of OR or IN
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.
Code:
--***********************************************************************
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'))
--***********************************************************************
Code:
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
...
...
...
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|