Help on Tuning

General
JGP
Enthusiast

Help on Tuning

I have the below set of tables created.

CREATE SET TABLE EDMSIT01_T3D_FND.DIM_PARTY ,NO FALLBACK ,

NO BEFORE JOURNAL,

NO AFTER JOURNAL,

CHECKSUM = DEFAULT,

DEFAULT MERGEBLOCKRATIO

(

Party_Id INTEGER NOT NULL,

Eff_Start_Dt DATE FORMAT 'YYYY-MM-DD' NOT NULL,

Eff_End_Dt DATE FORMAT 'YYYY-MM-DD' NOT NULL DEFAULT DATE '9999-12-31' COMPRESS (DATE '9999-12-31'),

Party_Desc VARCHAR(400) CHARACTER SET UNICODE NOT CASESPECIFIC COMPRESS ,

Host_Party_Id VARCHAR(200) CHARACTER SET UNICODE NOT CASESPECIFIC COMPRESS ,

Party_Start_Dt DATE FORMAT 'YYYY-MM-DD',

Party_End_Dt DATE FORMAT 'YYYY-MM-DD',

Lifcyc_Type_Cd INTEGER,

Init_Data_Src_Type_Cd INTEGER,

Assc_Hr_Num VARCHAR(50) CHARACTER SET LATIN NOT CASESPECIFIC,

Jnt_Party_Cnt INTEGER,

Indiv_Flg CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS ,

Orgn_Flg CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS ,

Jnt_Party_Flg CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS ,

Assc_Flg CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS ,

Extr_Orgn_Flg CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS ,

Intrnl_Orgn_Flg CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS ,

Io_Fin_Inst_Flg CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS ,

Io_Dept_Flg CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS ,

Io_Div_Flg CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS ,

Io_Brnch_Flg CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS ,

Io_Region_Flg CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS ,

Io_Oth_Orgn_Flg CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS ,

Intrnl_Orgn_Num VARCHAR(32) CHARACTER SET UNICODE NOT CASESPECIFIC COMPRESS ,

Orgn_Ctr_Cd INTEGER,

Orgn_Num VARCHAR(32) CHARACTER SET UNICODE NOT CASESPECIFIC COMPRESS ,

Intrnl_Orgn_Bank_Num VARCHAR(32) CHARACTER SET UNICODE NOT CASESPECIFIC COMPRESS ,

Hr_Intrnl_Orgn_Ind CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS ,

Party_Sts_Cd INTEGER,

Party_Sts_Rsn_Cd INTEGER,

Party_Sts_Chng_Dt DATE FORMAT 'YYYY-MM-DD',

Party_Prev_Sts_Cd INTEGER,

Party_Verif_Type_Cd INTEGER,

Bkrpt_Type_Cd INTEGER,

Cltrl_Hold_Flg CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS ,

Rvlv_Ln_Utlz_Flg CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS ,

Funds_Utlz_Flg CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS ,

Rtl_Liab_Flg CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS ,

OD_Fac_Flg CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS ,

Cr_Line_Flg CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS ,

New_To_Prod_Flg CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS ,

Swp_Ind_Flg CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS ,

Pref_Brnch_Party_Id INTEGER,

Last_Tran_Dt DATE FORMAT 'YYYY-MM-DD',

Home_Brnch_Party_Id INTEGER,

Risk_Grade_Id INTEGER,

Sspt_Frd_Flg CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS ,

Extr_Frd_Flg CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS ,

UCF_Rec_Load_Dt DATE FORMAT 'YYYY-MM-DD' NOT NULL DEFAULT DATE ,

UCF_Rec_End_Dt DATE FORMAT 'YYYY-MM-DD' NOT NULL DEFAULT DATE '9999-12-31' COMPRESS (DATE '9999-12-31'),

UCF_Rec_Deltd_Flg BYTEINT NOT NULL COMPRESS (0 ,1 ),

UCF_Proc_Id INTEGER NOT NULL DEFAULT 0 ,

UCF_File_Id INTEGER NOT NULL DEFAULT 0 ,

UCF_Trfm_Id INTEGER NOT NULL DEFAULT 0 )

PRIMARY INDEX ( Party_Id ,Eff_Start_Dt );

Statistics are defined on the below columns.

Party_Id

Eff_Start_Dt

Eff_End_Dt

Host_Party_Id

Party_Start_Dt

Lifcyc_Type_Cd

Init_Data_Src_Type_Cd

Orgn_Ctr_Cd

Party_Sts_Cd

Party_Sts_Rsn_Cd

Party_Prev_Sts_Cd

Party_Verif_Type_Cd

Bkrpt_Type_Cd

Pref_Brnch_Party_Id

Home_Brnch_Party_Id

Risk_Grade_Id

Party_Id,Eff_Start_Dt

CREATE SET TABLE EDMSIT01_T3D_FND.DIM_LOCTR ,NO FALLBACK ,

NO BEFORE JOURNAL,

NO AFTER JOURNAL,

CHECKSUM = DEFAULT,

DEFAULT MERGEBLOCKRATIO

(

Loctr_Id INTEGER NOT NULL,

Eff_Start_Dt DATE FORMAT 'YYYY-MM-DD' NOT NULL,

Eff_End_Dt DATE FORMAT 'YYYY-MM-DD' NOT NULL DEFAULT DATE '9999-12-31' COMPRESS (DATE '9999-12-31'),

Loctr_Sbtyp_Cd INTEGER,

Addr_Sbtyp_Cd INTEGER,

Elctrnc_Addr_Sbtyp_Cd INTEGER,

Elctrnc_Addr_Txt VARCHAR(200) CHARACTER SET UNICODE NOT CASESPECIFIC COMPRESS ,

Elctrnc_Addr_Dom_Nm VARCHAR(200) CHARACTER SET UNICODE NOT CASESPECIFIC COMPRESS ,

Dom_Root_Cd INTEGER,

Phn_Num VARCHAR(100) CHARACTER SET UNICODE NOT CASESPECIFIC COMPRESS ,

Phn_Country_Cd_Num VARCHAR(16) CHARACTER SET UNICODE NOT CASESPECIFIC COMPRESS ,

Phn_Area_Cd_Num VARCHAR(16) CHARACTER SET UNICODE NOT CASESPECIFIC COMPRESS ,

Phn_Exch_Num VARCHAR(50) CHARACTER SET LATIN NOT CASESPECIFIC,

Phn_Ln_Num VARCHAR(50) CHARACTER SET LATIN NOT CASESPECIFIC,

Phn_Extn_Num VARCHAR(16) CHARACTER SET UNICODE NOT CASESPECIFIC COMPRESS ,

Addr_Ln_1_Txt VARCHAR(500) CHARACTER SET UNICODE NOT CASESPECIFIC COMPRESS ,

Addr_Ln_2_Txt VARCHAR(500) CHARACTER SET UNICODE NOT CASESPECIFIC COMPRESS ,

Addr_Ln_3_Txt VARCHAR(200) CHARACTER SET UNICODE NOT CASESPECIFIC COMPRESS ,

Dwlng_Type_Cd INTEGER,

Cnsus_Blk_Id_Geo INTEGER,

Tambol_Nm VARCHAR(100) CHARACTER SET UNICODE NOT CASESPECIFIC COMPRESS ,

Tambol_Nm_TH VARCHAR(100) CHARACTER SET UNICODE NOT CASESPECIFIC COMPRESS ,

Country_Nm VARCHAR(100) CHARACTER SET UNICODE NOT CASESPECIFIC COMPRESS ,

Country_Nm_TH VARCHAR(100) CHARACTER SET UNICODE NOT CASESPECIFIC COMPRESS ,

Region_Nm VARCHAR(100) CHARACTER SET UNICODE NOT CASESPECIFIC COMPRESS ,

Region_Nm_TH VARCHAR(100) CHARACTER SET UNICODE NOT CASESPECIFIC COMPRESS ,

Province_Nm VARCHAR(100) CHARACTER SET UNICODE NOT CASESPECIFIC COMPRESS ,

Province_Nm_TH VARCHAR(100) CHARACTER SET UNICODE NOT CASESPECIFIC COMPRESS ,

City_Nm VARCHAR(100) CHARACTER SET UNICODE NOT CASESPECIFIC COMPRESS ,

City_Nm_TH VARCHAR(100) CHARACTER SET UNICODE NOT CASESPECIFIC COMPRESS ,

Amphur_Nm VARCHAR(100) CHARACTER SET UNICODE NOT CASESPECIFIC COMPRESS ,

Amphur_Nm_TH VARCHAR(100) CHARACTER SET UNICODE NOT CASESPECIFIC COMPRESS ,

Carr_Rte_Txt VARCHAR(200) CHARACTER SET UNICODE NOT CASESPECIFIC COMPRESS ,

Postal_Cd_Id INTEGER,

Hse_Num VARCHAR(16) CHARACTER SET UNICODE NOT CASESPECIFIC COMPRESS ,

Hse_Num_Mod_Val VARCHAR(16) CHARACTER SET UNICODE NOT CASESPECIFIC COMPRESS ,

Street_Drctn_Type_Cd INTEGER,

Street_Num VARCHAR(16) CHARACTER SET UNICODE NOT CASESPECIFIC COMPRESS ,

Street_Nm VARCHAR(100) CHARACTER SET UNICODE NOT CASESPECIFIC COMPRESS ,

Soi_Num VARCHAR(50) CHARACTER SET UNICODE NOT CASESPECIFIC COMPRESS ,

Moo_Num VARCHAR(16) CHARACTER SET UNICODE NOT CASESPECIFIC COMPRESS ,

Street_Sfx_Cd INTEGER,

Bldg_Num VARCHAR(16) CHARACTER SET UNICODE NOT CASESPECIFIC COMPRESS ,

Bldg_Nm VARCHAR(100) CHARACTER SET UNICODE NOT CASESPECIFIC COMPRESS ,

Unit_Num VARCHAR(16) CHARACTER SET UNICODE NOT CASESPECIFIC COMPRESS ,

Floor_Val VARCHAR(16) CHARACTER SET UNICODE NOT CASESPECIFIC COMPRESS ,

Wrkspc_Num VARCHAR(16) CHARACTER SET UNICODE NOT CASESPECIFIC COMPRESS ,

Village_Nm VARCHAR(100) CHARACTER SET UNICODE NOT CASESPECIFIC COMPRESS ,

Rte_Num VARCHAR(16) CHARACTER SET UNICODE NOT CASESPECIFIC COMPRESS ,

Mail_Pckup_Tm VARCHAR(10) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS ,

Mail_Delv_Tm VARCHAR(10) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS ,

Mail_Stop_Num VARCHAR(100) CHARACTER SET UNICODE NOT CASESPECIFIC COMPRESS ,

Mail_Box_Num VARCHAR(16) CHARACTER SET UNICODE NOT CASESPECIFIC COMPRESS ,

UCF_Rec_Load_Dt DATE FORMAT 'YYYY-MM-DD' NOT NULL DEFAULT DATE ,

UCF_Rec_End_Dt DATE FORMAT 'YYYY-MM-DD' NOT NULL DEFAULT DATE '9999-12-31' COMPRESS (DATE '9999-12-31'),

UCF_Rec_Deltd_Flg BYTEINT NOT NULL COMPRESS (0 ,1 ),

UCF_Proc_Id INTEGER NOT NULL DEFAULT 0 ,

UCF_File_Id INTEGER NOT NULL DEFAULT 0 ,

UCF_Trfm_Id INTEGER NOT NULL DEFAULT 0 )

PRIMARY INDEX ( Loctr_Id ,Eff_Start_Dt );

Statistics are defined on the below columns.

Loctr_Id

Eff_Start_Dt

Eff_End_Dt

Loctr_Sbtyp_Cd

Addr_Sbtyp_Cd

Elctrnc_Addr_Sbtyp_Cd

Dom_Root_Cd

Dwlng_Type_Cd

Cnsus_Blk_Id_Geo

Postal_Cd_Id

Street_Drctn_Type_Cd

Street_Sfx_Cd

Loctr_Id,Eff_Start_Dt

CREATE SET TABLE EDMSIT01_T3D_FND.DIM_PARTY_LOCTR ,NO FALLBACK ,

NO BEFORE JOURNAL,

NO AFTER JOURNAL,

CHECKSUM = DEFAULT,

DEFAULT MERGEBLOCKRATIO

(

Party_Id INTEGER NOT NULL,

Loctr_Seq_Num SMALLINT NOT NULL,

Eff_Start_Dt DATE FORMAT 'YYYY-MM-DD' NOT NULL,

Eff_End_Dt DATE FORMAT 'YYYY-MM-DD' NOT NULL DEFAULT DATE '9999-12-31' COMPRESS (DATE '9999-12-31'),

RM_Ofcl_Phn_Num_Loctr_Id INTEGER,

RM_Home_PhnNum_Loctr_Id INTEGER,

RM_Ofcl_Fax_Num_Loctr_Id INTEGER,

RM_Persl_Mobl_Num_Loctr_Id INTEGER,

RM_Rsdnl_Addr_Loctr_Id INTEGER,

RM_Busn_Addr_Loctr_Id INTEGER,

RM_Comml_Addr_Loctr_Id INTEGER,

RM_Ofcl_Email_Addr_Loctr_Id INTEGER,

CIM_Cntct_Addr_Loctr_Id INTEGER,

CIM_Busn_Ofcl_Phn_Num_Loctr_Id INTEGER,

CIM_Persl_Mobl_Num_Loctr_Id INTEGER,

UCF_Rec_Load_Dt DATE FORMAT 'YYYY-MM-DD' NOT NULL DEFAULT DATE ,

UCF_Rec_End_Dt DATE FORMAT 'YYYY-MM-DD' NOT NULL DEFAULT DATE '9999-12-31' COMPRESS (DATE '9999-12-31'),

UCF_Rec_Deltd_Flg BYTEINT NOT NULL COMPRESS (0 ,1 ),

UCF_Proc_Id INTEGER NOT NULL DEFAULT 0 ,

UCF_File_Id INTEGER NOT NULL DEFAULT 0 ,

UCF_Trfm_Id INTEGER NOT NULL DEFAULT 0 )

PRIMARY INDEX ( Party_Id ,Loctr_Seq_Num ,Eff_Start_Dt );

Statistics are defined on the below columns.

Party_Id

Loctr_Seq_Num

Eff_Start_Dt

Eff_End_Dt

Party_Id,Loctr_Seq_Num,Eff_Start_Dt

Party_Id,RM_Ofcl_Phn_Num_Loctr_Id

Party_Id,RM_Home_PhnNum_Loctr_Id

Party_Id,RM_Ofcl_Fax_Num_Loctr_Id

Party_Id,RM_Persl_Mobl_Num_Loctr_Id

Party_Id,RM_Rsdnl_Addr_Loctr_Id

Party_Id,RM_Busn_Addr_Loctr_Id

Party_Id,RM_Comml_Addr_Loctr_Id

Party_Id,RM_Ofcl_Email_Addr_Loctr_Id

Party_Id,CIM_Cntct_Addr_Loctr_Id

Party_Id,CIM_Busn_Ofcl_Phn_Num_Loctr_Id

Party_Id,CIM_Persl_Mobl_Num_Loctr_Id

 

CREATE SET TABLE EDMSIT01_T2D_MDL.EDM_CLNDR_DT ,NO FALLBACK ,

NO BEFORE JOURNAL,

NO AFTER JOURNAL,

CHECKSUM = DEFAULT,

DEFAULT MERGEBLOCKRATIO

(

Clndr_Dt DATE FORMAT 'YYYY-MM-DD' NOT NULL,

Clndr_Dt_Chr CHAR(8) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,

Clndr_Day_Of_Wk BYTEINT NOT NULL,

Clndr_Day_Of_Mth BYTEINT NOT NULL,

Clndr_Day_Of_Qtr BYTEINT NOT NULL,

Clndr_Day_Of_Yr SMALLINT NOT NULL,

Clndr_Wk_Of_Mth BYTEINT NOT NULL,

Clndr_Wk_Of_Qtr BYTEINT NOT NULL,

Clndr_Wk_Of_Yr BYTEINT NOT NULL,

Clndr_Mth_Of_Qtr BYTEINT NOT NULL,

Clndr_Mth_Of_Yr BYTEINT NOT NULL,

Clndr_Qtr_Of_Yr BYTEINT NOT NULL,

Clndr_Yr SMALLINT NOT NULL,

Clndr_Yr_TH SMALLINT NOT NULL,

Clndr_Day_Nm VARCHAR(35) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,

Clndr_Day_Desc VARCHAR(100) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,

Clndr_Wk_Nm VARCHAR(35) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,

Clndr_Wk_Desc VARCHAR(100) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,

Clndr_Wk_Start_Dt DATE FORMAT 'YYYY-MM-DD' NOT NULL,

Clndr_Wk_End_Dt DATE FORMAT 'YYYY-MM-DD' NOT NULL,

Clndr_Mth_Nm VARCHAR(35) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,

Clndr_Mth_Desc VARCHAR(100) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,

Clndr_Mth_Start_Dt DATE FORMAT 'YYYY-MM-DD' NOT NULL,

Clndr_Mth_End_Dt DATE FORMAT 'YYYY-MM-DD' NOT NULL,

Clndr_Qtr_Nm VARCHAR(35) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,

Clndr_Qtr_Desc VARCHAR(100) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,

Clndr_Qtr_Start_Dt DATE FORMAT 'YYYY-MM-DD' NOT NULL,

Clndr_Qtr_End_Dt DATE FORMAT 'YYYY-MM-DD' NOT NULL,

Clndr_Yr_Nm VARCHAR(35) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,

Clndr_Yr_Desc VARCHAR(100) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,

Clndr_Yr_Start_Dt DATE FORMAT 'YYYY-MM-DD' NOT NULL,

Clndr_Yr_End_Dt DATE FORMAT 'YYYY-MM-DD' NOT NULL,

Clndr_Day_Nm_TH VARCHAR(35) CHARACTER SET UNICODE NOT CASESPECIFIC NOT NULL,

Clndr_Day_Desc_TH VARCHAR(100) CHARACTER SET UNICODE NOT CASESPECIFIC NOT NULL,

Clndr_Wk_Nm_TH VARCHAR(35) CHARACTER SET UNICODE NOT CASESPECIFIC NOT NULL,

Clndr_Wk_Desc_TH VARCHAR(100) CHARACTER SET UNICODE NOT CASESPECIFIC NOT NULL,

Clndr_Wk_Start_Dt_TH DATE FORMAT 'YYYY-MM-DD' NOT NULL,

Clndr_Wk_End_Dt_TH DATE FORMAT 'YYYY-MM-DD' NOT NULL,

Clndr_Mth_Nm_TH VARCHAR(35) CHARACTER SET UNICODE NOT CASESPECIFIC NOT NULL,

Clndr_Mth_Desc_TH VARCHAR(100) CHARACTER SET UNICODE NOT CASESPECIFIC NOT NULL,

Clndr_Mth_Start_Dt_TH DATE FORMAT 'YYYY-MM-DD' NOT NULL,

Clndr_Mth_End_Dt_TH DATE FORMAT 'YYYY-MM-DD' NOT NULL,

Clndr_Qtr_Nm_TH VARCHAR(35) CHARACTER SET UNICODE NOT CASESPECIFIC NOT NULL,

Clndr_Qtr_Desc_TH VARCHAR(100) CHARACTER SET UNICODE NOT CASESPECIFIC NOT NULL,

Clndr_Qtr_Start_Dt_TH DATE FORMAT 'YYYY-MM-DD' NOT NULL,

Clndr_Qtr_End_Dt_TH DATE FORMAT 'YYYY-MM-DD' NOT NULL,

Clndr_Yr_Nm_TH VARCHAR(35) CHARACTER SET UNICODE NOT CASESPECIFIC NOT NULL,

Clndr_Yr_Desc_TH VARCHAR(100) CHARACTER SET UNICODE NOT CASESPECIFIC NOT NULL,

Clndr_Yr_Start_Dt_TH DATE FORMAT 'YYYY-MM-DD' NOT NULL,

Clndr_Yr_End_Dt_TH DATE FORMAT 'YYYY-MM-DD' NOT NULL,

Day_Of_Clndr INTEGER NOT NULL,

Clndr_Days_In_Mth BYTEINT NOT NULL,

Clndr_Days_In_Qtr BYTEINT NOT NULL,

Clndr_Days_In_Yr SMALLINT NOT NULL,

Unix_Dt DECIMAL(18,6) NOT NULL)

UNIQUE PRIMARY INDEX UPI_GREGORIAN_DATE ( Clndr_Dt )

INDEX ( Clndr_Dt_Chr );

Statistics are defined on the below columns

Clndr_Dt

Clndr_Dt_Chr

The below query is taking long time to execute. Any kind of performance improvement suggestion is welcomed. Please help me out.

 

SELECT

DP.Party_Id,

ECD.Clndr_Dt,

DL.Soi_Num AS RM_Cust_Res_Soi_Num,

DL.Street_Nm AS RM_Cust_Res_Street_Nm ,

DL.Moo_Num AS RM_Cust_Res_Moo_Num,

DL.Addr_Ln_1_Txt AS RM_Cust_Res_Addr_Ln_1_Txt,

DL.Addr_Ln_2_Txt AS RM_Cust_Res_Addr_Ln_2_Txt,

DL.Addr_Ln_3_Txt AS RM_Cust_Res_Addr_Ln_3_Txt,

DL.Tambol_Nm_TH AS RM_Cust_Res_Tambol_Nm_TH,

DL.Amphur_Nm_TH AS RM_Cust_Res_Amphur_Nm_TH,

DL.Province_Nm_TH AS RM_Cust_Res_Province_Nm_TH,

DL.Country_Nm_TH AS RM_Cust_Res_Country_Nm_TH,

DL.Postal_Cd_Id AS RM_Cust_Res_Postal_Cd_Id,

DL2.Phn_Num AS RM_Home_Phn_Num,

DL3.Elctrnc_Addr_Txt AS Customer_electronic_Address,

DL4.Phn_Num AS RM_Off_Phn_Num,

DL5.Phn_Num AS RM_Mobl_Phn_Num,

DL6.Phn_Num AS CIM_Off_Phn_Num,

DL7.Phn_Num AS CIM_Mobl_Phn_Num,

DL8.Addr_Ln_1_Txt AS RM_Cust_Bus_Addr_Ln_1_Txt,

DL8.Addr_Ln_2_Txt AS RM_Cust_Bus_Addr_Ln_2_Txt,

DL8.Addr_Ln_3_Txt AS RM_Cust_Bus_Addr_Ln_3_Txt,

DL8.Tambol_Nm_TH AS RM_Cust_Bus_Tambol_Nm_TH,

DL8.Amphur_Nm_TH AS RM_Cust_Bus_Amphur_Nm_TH,

DL8.Province_Nm_TH AS RM_Cust_Bus_Province_Nm_TH,

DL8.Country_Nm_TH AS RM_Cust_Bus_Country_Nm_TH,

DL8.Postal_Cd_Id AS RM_Cust_Bus_Postal_Cd_Id,

DL9.Addr_Ln_1_Txt AS CIM_Cust_Con_Addr_Ln_1_Txt,

DL9.Addr_Ln_2_Txt AS CIM_Cust_Con_Addr_Ln_2_Txt,

DL9.Addr_Ln_3_Txt AS CIM_Cust_Con_Addr_Ln_3_Txt,

DL9.Province_Nm_TH AS CIM_Cust_Con_Province_Nm_TH,

DL9.Country_Nm_TH AS CIM_Cust_Con_Country_Nm_TH,

DL9.Postal_Cd_Id AS CIM_Cust_Con_Postal_Cd_Id

FROM

EDMSIT01_T3V_FND.DIM_PARTY DP

INNER JOIN EDMSIT01_T2V_MDL.EDM_CLNDR_DT ECD

ON ECD.Clndr_Dt BETWEEN DP.Eff_Start_Dt AND DP.Eff_End_Dt

LEFT JOIN EDMSIT01_T3V_FND.DIM_PARTY_LOCTR DPL

ON DPL.Party_Id = DP.Party_Id

AND ECD.Clndr_Dt BETWEEN DPL.Eff_Start_Dt AND DPL.Eff_End_Dt

LEFT JOIN EDMSIT01_T3V_FND.DIM_LOCTR DL

ON DL.Loctr_Id = DPL.RM_Rsdnl_Addr_Loctr_Id

AND ECD.Clndr_Dt BETWEEN DL.Eff_Start_Dt AND DL.Eff_End_Dt

LEFT JOIN EDMSIT01_T3V_FND.DIM_LOCTR DL2

ON DL2.Loctr_Id = DPL.RM_Home_PhnNum_Loctr_Id

AND ECD.Clndr_Dt BETWEEN DL2.Eff_Start_Dt AND DL2.Eff_End_Dt

LEFT JOIN EDMSIT01_T3V_FND.DIM_LOCTR DL3

ON DL3.Loctr_Id = DPL.RM_Ofcl_Email_Addr_Loctr_Id

AND ECD.Clndr_Dt BETWEEN DL3.Eff_Start_Dt AND DL3.Eff_End_Dt

LEFT JOIN EDMSIT01_T3V_FND.DIM_LOCTR DL4

ON DL4.Loctr_Id = DPL.RM_Ofcl_Phn_Num_Loctr_Id

AND ECD.Clndr_Dt BETWEEN DL4.Eff_Start_Dt AND DL4.Eff_End_Dt

LEFT JOIN EDMSIT01_T3V_FND.DIM_LOCTR DL5

ON DL5.Loctr_Id = DPL.RM_Persl_Mobl_Num_Loctr_Id

AND ECD.Clndr_Dt BETWEEN DL5.Eff_Start_Dt AND DL5.Eff_End_Dt

LEFT JOIN EDMSIT01_T3V_FND.DIM_LOCTR DL6

ON DL6.Loctr_Id = DPL.CIM_Busn_Ofcl_Phn_Num_Loctr_Id

AND ECD.Clndr_Dt BETWEEN DL6.Eff_Start_Dt AND DL6.Eff_End_Dt

LEFT JOIN EDMSIT01_T3V_FND.DIM_LOCTR DL7

ON DL7.Loctr_Id = DPL.CIM_Persl_Mobl_Num_Loctr_Id

AND ECD.Clndr_Dt BETWEEN DL7.Eff_Start_Dt AND DL7.Eff_End_Dt

LEFT JOIN EDMSIT01_T3V_FND.DIM_LOCTR DL8

ON DL8.Loctr_Id = DPL.RM_Busn_Addr_Loctr_Id

AND ECD.Clndr_Dt BETWEEN DL8.Eff_Start_Dt AND DL8.Eff_End_Dt

LEFT JOIN EDMSIT01_T3V_FND.DIM_LOCTR DL9

ON DL9.Loctr_Id = DPL.CIM_Cntct_Addr_Loctr_Id

AND ECD.Clndr_Dt BETWEEN DL9.Eff_Start_Dt AND DL9.Eff_End_Dt

;