August 28, 2008 at 3:50 pm
I need to pull rows from view e if it has data if not pull data from view f.this is my query:
SELECT
c.copay_id,
d.drug_label_assoc_detail_id,
case
when ((e.copay_flat_dollar_amount is NOT NULL) or (e.copay_percent is NOT NULL) or (e.dict_copay_display_id is NOT NULL)) then
e.copay_pharmacy_type
else
f.copay_pharmacy_type
end copay_pharmacy_type,
case
when ((e.copay_flat_dollar_amount is NOT NULL) or (e.copay_percent is NOT NULL) or (e.dict_copay_display_id is NOT NULL)) then
e.copay_first_term
else
f.copay_first_term
end copay_first_term
FROM
copay c with (nolock)
cross join drug_label_assoc_detail d with (nolock)
left outer join v_copay_drug_label_assoc_detail_2 e with (nolock)
on c.copay_id =e.copay_id
and e.drug_label_assoc_detail_id = d.drug_label_assoc_detail_id
inner join dict_copay_display with (nolock)
on e.dict_copay_display_id=dict_copay_display.dict_copay_display_id
inner join health_care_plan with (nolock)
on e.copay_id=health_care_plan.copay_id
and e.dict_pbm_participant_id=health_care_plan.dict_pbm_participant_id
and e.dict_formulary_dataload_source_code=health_care_plan.health_care_plan_dataload_source_code
left outer join v_copay_summary_level f with (nolock)
on c.copay_id=f.copay_id
and f.drug_label_assoc_detail_id=d.drug_label_assoc_detail_id
inner join dict_copay_display d1 with (nolock)
on f.dict_copay_display_id=d1.dict_copay_display_id
inner join health_care_plan h1 with (nolock)
on f.copay_id=h1.copay_id
and f.dict_pbm_participant_id=h1.dict_pbm_participant_id
and f.dict_formulary_dataload_source_code=h1.health_care_plan_dataload_source_code
this just returning rows common to e and f.What am I doing wrong?
Thanks,
SSM
August 29, 2008 at 3:44 am
Your outer joins are effectively being converted to inner joins by the subsequent inner joins.
The joins should be nested so that the FROM clause looks something like:
FROM
    copay C
    CROSS JOIN drug_label_assoc_detail D
    LEFT JOIN
    (
        v_copay_drug_label_assoc_detail_2 E
        JOIN dict_copay_display DCD
            ON DCD.dict_copay_display_id = E.dict_copay_display_id
        JOIN health_care_plan HCP
            ON E.copay_id = HCP.copay_id
                AND E.dict_pbm_participant_id = HCP.dict_pbm_participant_id
                AND E.dict_formulary_dataload_source_code = HCP.health_care_plan_dataload_source_code
    )
        ON C.copay_id = E.copay_id
            AND E.drug_label_assoc_detail_id = D.drug_label_assoc_detail_id
    LEFT JOIN
    (
        v_copay_summary_level F
        JOIN dict_copay_display D1
            ON F.dict_copay_display_id = D1.dict_copay_display_id
        JOIN health_care_plan H1
            ON F.copay_id = H1.copay_id
                AND F.dict_pbm_participant_id = H1.dict_pbm_participant_id
                AND F.dict_formulary_dataload_source_code = H1.health_care_plan_dataload_source_code
    )
        ON C.copay_id = F.copay_id
            AND F.drug_label_assoc_detail_id = D.drug_label_assoc_detail_id
ps The brackets are not required but are recommended for readability.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply