Example 2: Oracle Format with Substitution Parameters

DEFINE   S_Study_ID = 100
DEFINE          DCM_1 = 003
DEFINE   DCM_2 =          004
DEFINE          DCMQ_1 = 123
DEFINE   DCMQ_2 = 456
DEFINE          DCMQ_3 = 789      

select /* + ORDERED USE_MERGE (cpe)
            INDEX (r RESPONSE_UK_IDX)
            INDEX (cpe CLIN_PLAN_EVE_UK2_IDX) */
          substr(rdcm.document_number, 1 0) ||'WK14338' study,
          rdcm.patient pt,
          rdcm.clin_plan_eve_name cpevent,
          r.repeat_sn repeatsn,
          max(decode(r.dcm_question_id, &DCMQ_1, substr(r.value_text,1,8),

          &DCMQ_2, substr(r.value_text,1,8), &DCMQ_3, substr(r.value_text,1,8)))                                                                                         
          var1
from clinical_planned_events cpe,
     rdcms_view rdcm,
     responses_view r
where rdcm.clinical_study_id = &S_study_ID and
      rdcm.dcm_id in (&DCM_1, &DCM_2) and 
      r.dcm_question_id in (&DCMQ_1, &DCMQ_2, &DCMQ_3) and
      r.received_dcm_id = rdcm.received_dcm_id
group by substr(rdcm.document_number, 1 0) ||'WK14338' study,
         rdcm.patient,
         rdcm.clin_plan_eve_name,
         r.repeat_sn