Beauty! This is what I was after ;)

Guys/Galls pls don't be shy, Pls gimme some more :)

Thank You all & TIA



>>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 
>