Sample SQL Generated

The following SQL is generated for the previous example:

SELECT DISTINCT v_rpt_case.case_id
FROM v_rpt_case, rpt_product
WHERE v_rpt_case.case_id = rpt_product.case_id
AND ( (v_rpt_case.country_id = 13)
AND ( UPPER (rpt_product.co_drug_code) = 'LAS+TAB'
OR (pat_exposure IN (
SELECT product_id
FROM lm_product
WHERE UPPER (lm_product.drug_code) ='LAS+TAB')))
OR (UPPER (rpt_product.co_drug_code) = 'LAS+'
OR (pat_exposure IN (SELECT product_id FROM lm_product
WHERE UPPER (lm_product.drug_code) ='LAS+')))
AND (rpt_product.is_overdose = 1)
AND (rpt_product.interaction = 1))
INTERSECT
SELECT DISTINCT v_rpt_case.case_id
FROM v_rpt_case
WHERE v_rpt_case.case_id IN (
SELECT DISTINCT v_rpt_case.case_id
FROM v_rpt_case
WHERE (v_rpt_case.country_id = 13)
OR (v_rpt_case.country_id = 223))

Note:

While creating a new Advanced Condition using the saved Advanced Condition, always enclose the saved Advanced Condition within the parenthesis as using Union, Minus, or Intersect will create different select statements and if you use parenthesis with the saved Advanced Condition with new fields, wrong SQL will be generated.

This is the desired and correct SQL.

The following SQL gets generated in this case.

(SELECT DISTINCT v_rpt_case.case_id                
FROM v_rpt_case                
WHERE v_rpt_case.case_id IN (                                              
SELECT DISTINCT v_rpt_case.case_id                                         
FROM v_rpt_case                                         
WHERE (v_rpt_case.case_num IS NOT NULL)))
UNION
(SELECT DISTINCT v_rpt_case.case_id                
FROM v_rpt_case, rpt_event, rpt_product                
WHERE v_rpt_case.case_id = rpt_event.case_id                
AND v_rpt_case.case_id = rpt_product.case_id                
AND ((rpt_event.agent_seq_num = 1)))

Below Query will execute fine but parenthesis will not be at right place. Following is the SQL generated.

SELECT DISTINCT v_rpt_case.case_id              
FROM v_rpt_case, rpt_product
               WHERE v_rpt_case.case_id = rpt_product.case_id
                 AND ( (UPPER (rpt_product.co_drug_code) = 'RELVAC1'
                              OR (pat_exposure IN (
                                   SELECT product_id
                                   FROM lm_product
                                   WHERE UPPER (lm_product.drug_code) = 'RELVAC1')
))
OR ( UPPER (rpt_product.co_drug_code) = 'C_TEST DRUG 1'
OR (pat_exposure IN (
        SELECT product_id
            FROM lm_product
            WHERE UPPER (lm_product.drug_code) = 'C_TEST DRUG 1')
)
)
)
INTERSECT
(SELECT DISTINCT v_rpt_case.case_id
                 FROM v_rpt_case
                WHERE (v_rpt_case.country_id = 81)
UNION
SELECT DISTINCT v_rpt_case.case_id
              FROM v_rpt_case, rpt_followup
              WHERE v_rpt_case.case_id = rpt_followup.case_id
                 AND (( (TRUNC (rpt_followup.receipt_date) =
TO_DATE ('17-MAR-2009', 'DD-MON-YYYY')
)
                 AND rpt_followup.seq_num > 0
)
)
UNION
SELECT DISTINCT v_rpt_case.case_id
               FROM v_rpt_case, rpt_reporters
               WHERE v_rpt_case.case_id = rpt_reporters.case_id
                   AND ((v_rpt_case.state_id = 3))
                   OR (rpt_reporters.primary_contact = 1))

Note:

Some parenthesis are not at correct place as compared to AC created. Last parenthesis will come only after complete query creation.