>Dear All
>
>What is the longest SQL statement you ever used to
>prepare report cursor(s).
>
>I have seen few 'elephants' here and there, now I need few of those.
>For research purposes / with right to publish.
>
>Mocked up table/field names are ok, it is shear size / complexity I am after.
>
>TIA

AVATAR-Cache' ::: Here's my baby elephant, it replaced Two SQL mammoths -

SELECT CAST(v_e_s_a.PATID AS INT) As PATIENT_ID,RTRIM(v_e_s_a.c_ss_number) AS SSN,
       RTRIM(v_e_s_a.patient_gender_code) AS Sex_c,v_e_s_a.c_date_of_birth AS DOB,
       RTRIM(v_e_s_a.c_ethnicity_code) AS Ethnic_ori,RTRIM(v_e_s_a.c_race_code) AS Race_c,
       v_e_s_a.EPISODE_NUMBER As Episode,v_e_s_a.preadmit_admission_date AS Adm_padm_d,
       RTRIM(v_e_s_a.admit_type_of_code) AS Adm_Type_C,
       CAST(CASE v_e_s_a.FACILITY WHEN 1 THEN 2
                             WHEN 2 THEN 1
                             WHEN 3 THEN 7
                             WHEN 4 THEN 3
                             WHEN 5 THEN 8
                             ELSE 0
       END AS INT) AS Facility_I,
       p_c_d.facility_chart_number As Facility_C,v_e_s_a.preadmit_admission_date As Admit_Date,
       v_e_s_a.date_of_discharge As Discharge_,v_e_s_a.program_code As Program_c,h_l_s.LS_uniqueid As Legal_UID,
       h_l_s.legal_status_code As Legal_Stat,h_l_s.legal_status_effective_date As Effective_,
       v_e_s_a.admit_practitioner_name As Adm_Clinician,v_e_s_a.admit_principal_diag_code As Diag_C,
       v_e_s_a.admit_principal_diag_value As Diag,v_e_s_d.disc_principal_diagnosis_code As Disdiag_c,
       disc_principal_diagnosis_value As Disdiag,disc_unit_code as Disunit_c,disc_legal_status_value As DisLS,
       primary_referral_Value As REF_VAL,GUARANTOR_ID As GUAR_ID,guarantor_order_number As GUAR_ORD,
       guarantor_name As GUAR_NAME,p_c_d.patient_name_last As PAT_NAME_L,p_c_d.patient_name_first As PAT_NAME_F,
       disc_type_of_value As disc_tval,disc_unit_value As disc_uval,disc_practitioner_staff_name As DiscPname,
       disc_legal_status_code As disc_LS_c,disc_practitioner_staff_id As DiscPstaf,disc_type_of_code As disc_tcode 
FROM SYSTEM.view_episode_summary_admit v_e_s_a 
LEFT OUTER JOIN SYSTEM.patient_current_demographics p_c_d ON 
      v_e_s_a.PATID=p_c_d.PATID AND v_e_s_a.FACILITY=p_c_d.facility 
LEFT OUTER JOIN SYSTEM.history_legal_status h_l_s ON 
      v_e_s_a.facility=h_l_s.facility AND v_e_s_a.PATID=h_l_s.PATID AND 
      v_e_s_a.EPISODE_NUMBER=h_l_s.EPISODE_NUMBER 
LEFT OUTER JOIN SYSTEM.view_episode_summary_discharge v_e_s_d ON 
      v_e_s_a.PATID=v_e_s_d.PATID AND v_e_s_a.facility=v_e_s_d.facility AND 
      v_e_s_a.EPISODE_NUMBER=v_e_s_d.EPISODE_NUMBER 
LEFT OUTER JOIN SYSTEM.Discharge_Referral d_r ON 
      v_e_s_a.PATID=d_r.PATID AND v_e_s_a.EPISODE_NUMBER=d_r.EPISODE_NUMBER AND 
      v_e_s_a.facility=d_r.facility 
LEFT OUTER JOIN SYSTEM.billing_guar_order_current b_g_o_c ON 
      v_e_s_a.PATID=b_g_o_c.PATID AND v_e_s_a.EPISODE_NUMBER=b_g_o_c.EPISODE_NUMBER AND 
      v_e_s_a.facility=b_g_o_c.facility 
WHERE admit_type_of_code <> '0' 
ORDER BY v_e_s_a.c_ss_number, v_e_s_a.preadmit_admission_date, legal_status_effective_date