Example 1: Oracle Format

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, 123, substr(r.value_text,1,8),
        456, substr(r.value_text,1,8), 
        789, substr(r.vaue_text,1,8) )) var1
from clinical_planned_events cpe,
     rdcms_view rdcm,
     responses_view r
where rdcm.clinical_study_id = 100 and
      rdcm.dcm_id in (003,004) and 
      r.dcm_question_id in (123,456,789) 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