Column addition in $vTableName Table

[kartik]

I wanted to take the V_codename column from T1_final table to $vTableName Table.

How can i left join the T1_final table in $vTableName ?

here is the table. I don't know on the basis of what key I can do join.

Don't focus on section 1

Section 1-

Booking:
select CSPM_CHRG_SALES_EMP_NO,to_char(TO_DATE(book.BKNG_DATE,'YYYYMMDD') ,'Mon-RRRR') "Booking Month",
(CASE dlr.DORG_SALE_DISTRICT WHEN 'S' THEN 'SOUTH' WHEN 'E' THEN 'EAST' WHEN 'W' THEN 'WEST' WHEN 'N' THEN 'NORTH' ELSE '' END) AS REGION,
book.BKNG_DLR_NO,
nvl(dlr.DORG_TRADE_DSCTN,dlr.DORG_ORG_DSCTN) TRADE_NAME,
city.CMM_CODE_NAME DEALER_CITY,
enq.EQRY_ENQRY_NO,
enq.EQRY_PRESENT_CAR_YN,
TO_DATE(enq.EQRY_DATE,
'YYYYMMDD') AS EQRY_DATE
, eq_st.CMM_CODE_NAME EQRY_STAT
,(case when EQRY_STAT not in ('55','57','59','85','86','60','80') then null else EQRY_CLSE_DATE end )EQRY_CLSE_DATE
,enq.EQRY_SRC_CODE
,enq.EQRY_CTGR_CODE
,ENSC_NAME EQRY_SUB_SRC_CODE
,cd.CSPM_CUST_NO
,cd.CSPM_CUST_FULL_NAME
,cd.M_MOBL_PHONE_NO
,cd.CSPM_EMAIL
,cu_ty.CMM_CODE_NAME CUSTOMER_TYPE
,mdl.MODL_FULL_DSCTN MODEL
,var.VRNT_GRP_DSCTN ENGINE_TYPE
,tmn.CMM_CODE_NAME TRANSMISSION
,zz.SPEC_TRANS TRANSMISSION1
,emp.EMPM_EMP_NAME KEC
,emp.EMPM_EMP_NO KEC_Code
,adr.ADDR
,adr.PIN_CODE
,adr.CITY_CODE
,adr.STCD_CODE,
EQRF_ADDR1 booking_add1,EQRF_ADDR2 booking_add2,EQRF_ADDR3 booking_add3,EQRF_STCD booking_state,EQRF_CITY booking_city,EQRF_PIN_CODE booking_pin
//,NULL AS REGION
,enq.EQRY_EXCHG_YN AS EXCHANGE_REQUIRED
,enq.EQRY_EXCHG_MODEL_CODE // added by kartik...
,enq.EQRY_MAKER_CODE
,enq.EQRY_EXCHG_SRC_CODE

,enq.EQRY_EXPCT_PRICE
,enq.EQRY_QUOTD_PRICE
,enq.EQRY_P_MODL_YEAR
,enq.EQRY_EVAL_YN
,enq.EQRY_EXCH_DN
,enq.EQRY_FINAL_BP
,enq.EQRY_DLR_NO
// ,CMM_GRP_CODE
// ,CMM_CODE
// ,DECODE(EQRY_EXCHG_MODEL_CODE, NULL, 'N','', 'N','Y') AS EXCHANGE_REQUIRED
,ecr.CMM_CODE_NAME EXISTING_CAR
,NVL(enq.EQRY_FNCE_YN,'N') AS FINANCE_REQUIRED
// ,enq.EQRY_FNCE_YN AS FINANCE_REQUIRED
,book.BKNG_NO
,TO_DATE(book.BKNG_DATE,'YYYYMMDD') Booking_Date
,book.BKNG_AMT
,NULL AS BOOKING_STATUS
,(Select LISTAGG(RR.RCPT_NO, ',') WITHIN GROUP (ORDER BY RR.RCPT_NO)
From DMSDBA.SAL_SARCPT_TB RR
WHERE RR.RCPT_DLR_NO=book.BKNG_DLR_NO
and RR.RCPT_BKNG_NO =book.BKNG_NO
) RCPT_NO
,(Select LISTAGG(RR.RCPT_AMT, ',') WITHIN GROUP (ORDER BY RR.RCPT_AMT)
From DMSDBA.SAL_SARCPT_TB RR
WHERE RR.RCPT_DLR_NO=book.BKNG_DLR_NO
and RR.RCPT_BKNG_NO =book.BKNG_NO
) RCPT_AMT
,(Select LISTAGG(DDD.CMM_CODE_NAME, ',') WITHIN GROUP (ORDER BY DDD.CMM_CODE_NAME)
From DMSDBA.SAL_SARCPT_TB RR
left join DMSDBA.CMM_CMCD_TB DDD on DDD.CMM_CODE=RR.RCPT_TYPE
And DDD.CMM_GRP_CODE= 'HSA0006'
WHERE RR.RCPT_DLR_NO=book.BKNG_DLR_NO
and RR.RCPT_BKNG_NO =book.BKNG_NO
) PAYMENT_TYPE
,ss.EQRF_RGST_FULL_NAME REGISTRATION_NAME
,enq.EQRY_SR_NUM_REAL
,(CASE WHEN enq.EQRY_SRC_FLG = 'MOB' THEN 'MYSALES' WHEN LENGTH(enq.EQRY_SR_NUM_REAL) > 0 THEN 'ONLINE' ELSE 'DMS' END ) BOOKING_TYPE
,var.VRNT_GRP_DSCTN GRP_VRNT_CD
,clr.COLR_FULL_DSCTN EXT_COLOR_NAME
,intclr.INTCL_INT_COLOR_DESC INT_COLOR_NAME
,FNCR_DSCTN FINANCIER
,mop.CMM_CODE_NAME MODE_OF_PURCHASE
,TO_CHAR(book.BKNG_CRTE_DTIME,'yyyy/mm/dd') as BOOKING_CREATION_DATE
,TO_CHAR(book.BKNG_CRTE_DTIME,'hh24:mm')as BOOKING_CREATION_TIME
,TO_CHAR(book.BKNG_UPDT_DTIME,'yyyy/mm/dd') as BOOKING_UPDATE_DATE
,TO_CHAR(book.BKNG_UPDT_DTIME,'hh24:mm') as BOOKING_UPDATE_TIME
,(select to_char(TSTDRV_DATE,'YYYYMMDD') from DMSDBA.CRM_STDMST_TB where TSTDRV_STAT='02' and TSTDRV_ENQ_NO=EQRY_ENQRY_NO and TSTDRV_DLR_NO=EQRY_DLR_NO and rownum<=1) as test_drive_date
,(select REQST_TYPE from DMSDBA.CRM_IGSRRQ_IR where IF_DLR_CODE=EQRY_DLR_NO And SR_NUM_REAL=EQRY_SR_NUM_REAL and rownum=1) as reqst_type
,EQRY_CTGR_CODE as Enquiry_Category,EQCR_QTY as vehicle_booked_count
//DMSDBA.FNC_GET_CODE_NAME('DSO0012',EQRY_CTGR_CODE,'en') Enquiry_Category,EQCR_QTY as vehicle_booked_count
,EQRY_CLSE_DATE AS LOSS_DATE,EQRY_CLSE_RMRKS as LOSS_REMARK,(select ENCL_GRP_NAME from DMSDBA.sal_sdencl_tb where ENCL_CODE=EQRY_CLSE_TYPE and rownum=1) as LOSS_REASON,
(select ENCL_NAME from DMSDBA.sal_sdencl_tb where ENCL_CODE=EQRY_CLSE_TYPE and rownum=1) as LOSS_DUETO,(select ENCL_NAME from DMSDBA.sal_sdencl_tb where ENCL_CODE=EQRY_CLS_VAL and rownum=1) as LOSS_DUETO_detail,
(SELECT A.EMPM_EMP_NO||';'||A.EMPM_EMP_NAME||';'||decode(EMPM_DEL_GB,'Y','inactive','Active')
FROM DMSDBA.CMM_CAEMPM_TB A INNER JOIN DMSDBA.SAL_SCTLAS_TB B
ON A.EMPM_DLR_NO = B.DLR_NO
AND A.EMPM_CORP_NO = B.CORP_NO
AND A.EMPM_CMPN_NO = B.CMPN_NO
AND A.EMPM_EMP_NO = B.SC_EMP_NO
INNER JOIN DMSDBA.SAL_DLRTEM_TB C
ON B.CORP_NO = C.CORP_NO
AND B.CMPN_NO = C.CMPN_NO
AND B.DLR_NO = C.DLR_NO
AND B.TEAM_CODE = C.TEAM_CODE
WHERE B.DLR_NO = eqry_dlr_no
and B.SC_EMP_NO=EQRY_EMP_NO and rownum=1) as Team_leader,
(SELECT COUNT(*) FROM DMSDBA.SAL_REBOOKING_TB WHERE DLR_NO=book.BKNG_DLR_NO AND BKNG_NO=book.BKNG_NO) REBKNG_CNT
,(SELECT TO_CHAR(TO_DATE(BKNG_DATE,'YYYYMMDD'),'DD/MM/YYYY') From ( Select ROW_NUMBER() OVER( ORDER BY BKNG_DATE DESC) AS RNUM,BKNG_DATE From DMSDBA.SAL_REBOOKING_TB WHERE BKNG_NO=book.BKNG_NO AND DLR_NO=book.BKNG_DLR_NO ORDER BY BKNG_DATE DESC) Where RNUM=1) RCNT_REBKNG_DT
,MOB.MAIL_CRTE_DATE MOB_EMAIL_DATE
,DMS.MAIL_CRTE_DATE DMS_EMAIL_DATE
, NVL(MOB.MAIL_COUNT,0) MOB_DOCK,
var.VRNT_SPEC_CODE
, NVL(DMS.MAIL_COUNT,0) DMS_DOCK

from DMSDBA.SAL_SOBKNG_TB book
inner join DMSDBA.cmm_cadorg_tb dlr on dlr.dorg_DLR_NO=book.BKNG_DLR_NO
and dlr.dorg_CORP_NO=book.BKNG_CORP_NO
and dlr.dorg_CMPN_NO =book.BKNG_CMPN_NO
left join DMSDBA.CRM_SCEQRY_TB enq on enq.EQRY_DLR_NO=book.BKNG_DLR_NO
and enq.EQRY_CORP_NO=book.BKNG_CORP_NO
and enq.EQRY_CMPN_NO =book.BKNG_CMPN_NO
and enq.EQRY_ENQRY_NO=book.BKNG_ENQRY_NO
left join DMSDBA.cmm_cmcd_tb city on city.CMM_CODE=dlr.DORG_CITY_CODE
and city.CMM_GRP_CODE= 'DC00014'
and city.USED_YN= 'Y'
left join DMSDBA.cmm_cmcd_tb eq_st on eq_st.CMM_CODE=enq.EQRY_STAT
and eq_st.CMM_GRP_CODE= 'DSO0002'
and eq_st.USED_YN= 'Y'
left join DMSDBA.CRM_SDENSC_TB yy on yy.ENSC_CODE = enq.EQRY_SUB_SRC_CODE
and ENSC_GRP_CODE = 'DSO0001'
left join DMSDBA.CRM_CDCSPM_TB cd on cd.CSPM_CMPN_NO=enq.EQRY_CMPN_NO
and cd.CSPM_CORP_NO=enq.EQRY_CORP_NO
and cd.CSPM_DLR_NO=enq.EQRY_DLR_NO
and cd.CSPM_CUST_NO=enq.EQRY_CUST_NO
left join DMSDBA.cmm_cmcd_tb cu_ty on cu_ty.CMM_CODE=cd.CSPM_CUST_TYPE
and cu_ty.CMM_GRP_CODE= 'DSS0005'
and cu_ty.USED_YN= 'Y'
left join DMSDBA.CRM_SCEQCR_TB eqr on eqr.EQCR_DLR_NO=enq.EQRY_DLR_NO
and eqr.EQCR_CORP_NO=enq.EQRY_CORP_NO
and eqr.EQCR_CMPN_NO=enq.EQRY_CMPN_NO
and eqr.EQCR_ENQRY_NO=enq.EQRY_ENQRY_NO
left join DMSDBA.SAL_SSMODL_TB mdl on mdl.MODL_MODEL_CODE=eqr.EQCR_MODEL_CODE
left join DMSDBA.SAL_SSVRNT_TB var on var.VRNT_MODEL_CODE=eqr.EQCR_MODEL_CODE
And var.VRNT_CODE=eqr.EQCR_VRNT_CODE
and var.VRNT_CORP_NO=eqr.EQCR_CORP_NO
and var.VRNT_CMPN_NO=eqr.EQCR_CMPN_NO
left join DMSDBA.CMM_CMCD_TB tmn on tmn.CMM_CODE=enq.EQRY_TRNSM_TYP
and tmn.CMM_GRP_CODE= 'DRC0102'
left join DMSDBA.SAL_SISPEC_TB zz on zz.SPEC_CODE=var.VRNT_SPEC_CODE
left join DMSDBA.cmm_caempm_tb emp on emp.EMPM_EMP_NO=enq.EQRY_EMP_NO
left join DMSDBA.CRM_CDCUAD_TB adr on adr.cmpn_no=cd.CSPM_CMPN_NO
and adr.corp_no=cd.CSPM_CORP_NO
and adr.dlr_no=cd.CSPM_DLR_NO
and adr.cust_no=cd.CSPM_CUST_NO
and adr.USED_YN = 'Y'
left join DMSDBA.CMM_CMCD_TB ecr on ecr.CMM_CODE=enq.EQRY_MAKER_CODE
and ecr.CMM_GRP_CODE= 'UCB0009'
LEFT JOIN DMSDBA.SAL_SCEQRF_TB ss ON ss.EQRF_ENQRY_NO=enq.EQRY_ENQRY_NO
And ss.EQRF_DLR_NO=enq.EQRY_DLR_NO
left join DMSDBA.SAL_SSCOLR_TB clr on clr.COLR_CORP_NO=enq.EQRY_CORP_NO
and clr.COLR_CMPN_NO=enq.EQRY_CMPN_NO
and clr.COLR_CODE=eqr.EQCR_COLOR_CODE
and clr.COLR_STAT = 'Y'
left join DMSDBA.SAL_SSINTCL_TB intclr on intclr.INTCL_CMPN_NO=eqr.EQCR_CMPN_NO
AND intclr.INTCL_CORP_NO=eqr.EQCR_CORP_NO
AND intclr.INTCL_MODEL_CODE=eqr.EQCR_MODEL_CODE
AND intclr.USED_YN = 'Y'
AND intclr.INTCL_EXT_COLOR_CODE=eqr.EQCR_COLOR_CODE
AND intclr.INTCL_INT_COLOR_CODE = eqr.EQCR_INT_COLOR_CODE
and intclr.INTCL_SPEC_CODE=var.VRNT_SPEC_CODE
left join DMSDBA.SAL_SSFNCR_TB fncr on fncr.FNCR_CORP_NO=book.BKNG_CORP_NO
and fncr.FNCR_CMPN_NO=book.BKNG_CMPN_NO
and fncr.FNCR_ACTV_YN='Y'
and fncr.FNCR_BANK_CODE=book.BKNG_FNCER_CODE
left join DMSDBA.CMM_CMCD_TB mop on mop.CMPN_NO=book.BKNG_CMPN_NO
and mop.CORP_NO=book.BKNG_CORP_NO
and mop.CMM_CODE=book.BKNG_FNCE_TYPE
and mop.CMM_GRP_CODE= 'DSA0001'
left join DMSDBA.SAL_MAIL_TB DMS on
DMS.MAIL_DLR_NO= bkng_dlr_no and DMS.MAIL_ENQRY_NO=BKNG_ENQRY_NO and
DMS.MAIL_SUBJECT = 'Docket' and DMS.MAIL_SRC_FLG='DMS'
left join DMSDBA.SAL_MAIL_TB MOB on
MOB.MAIL_DLR_NO= bkng_dlr_no and MOB.MAIL_ENQRY_NO=BKNG_ENQRY_NO and
MOB.MAIL_SUBJECT = 'Docket' and MOB.MAIL_SRC_FLG='MOB'

where dlr.DORG_SCTN_TYPE='S';

Section 2 -

$(vTableName):
LOAD *,
BKNG_DLR_NO & BKNG_NO AS Join_KEY,
"EQRY_MAKER_CODE" & '|' & "EQRY_EXCHG_MODEL_CODE" as Comp_key,
EQRY_EXCHG_SRC_CODE as key
// "CMM_GRP_CODE" &'|'& "CMM_CODE" as key


Resident Booking;
DROP TABLE Booking;

/******************************CRTE_DTIME***************************************/
LEFT JOIN ($(vTableName))

// CUSMTAG:
LOAD "CUST_DLR_NO" & "CUST_BOOKNG_NO" AS Join_KEY,
MAX(FLOOR("CRTE_DTIME")) AS "ORDER DATE",
'SOQ ORDER' As ORDER_TYPE
WHERE NOT ISNULL("CUST_DLR_NO") and NOT ISNULL("CUST_BOOKNG_NO")
GROUP BY("CUST_DLR_NO" & "CUST_BOOKNG_NO");
SQL SELECT "CUST_DLR_NO",
"CUST_BOOKNG_NO",
"CRTE_DTIME"
FROM DMSDBA."SAL_CUSMTAG_TB";

Concatenate

// KMICUSMTAG:
LOAD "CUST_DLR_NO" & "CUST_BOOKNG_NO" AS Join_KEY,
MAX(FLOOR("CRTE_DTIME")) AS "ORDER DATE",
'KMI Order' As ORDER_TYPE
WHERE NOT ISNULL("CUST_DLR_NO") and NOT ISNULL("CUST_BOOKNG_NO")
GROUP BY("CUST_DLR_NO" & "CUST_BOOKNG_NO");
SQL SELECT "CUST_DLR_NO",
"CUST_BOOKNG_NO",
"CRTE_DTIME"
FROM DMSDBA."SAL_KMICUSMTAG_TB";

/******************************************************************************/

Left Join($(vTableName))

uc_maker:
LOAD "MAKE_MAKER_CODE" AS "EQRY_MAKER_CODE",
"MAKE_FULL_DSCTN" as OEM;
SQL SELECT "MAKE_MAKER_CODE",
"MAKE_FULL_DSCTN"
FROM DMSDBA."SAL_UCMAKE_TB";

left join ($(vTableName))
Uc_model:
LOAD
"MODL_MAKER_CODE" & '|' & "MODL_MODEL_CODE" AS Comp_key,
"MODL_MAKER_CODE",
"MODL_MODEL_CODE",
"MODL_FULL_DSCTN" as MODEL;
SQL SELECT "MODL_MAKER_CODE",
"MODL_MODEL_CODE",
"MODL_FULL_DSCTN"
FROM DMSDBA."SAL_UCMODL_TB";

Section 3-

T1:

load
"CMPN_NO" &'|'& "CORP_NO" &'|'& "CMM_GRP_CODE" &'|'& "CMM_CODE" as key,
"CMPN_NO",
"CORP_NO",
"CMM_GRP_CODE",
"CMM_CODE",
"CMM_CODE_NAME";
SQL SELECT "CMPN_NO",
"CORP_NO",
"CMM_GRP_CODE",
"CMM_CODE",
"CMM_CODE_NAME"
FROM DMSDBA."CMM_CMCD_TB"

where

"CMPN_NO" = 'K'

AND "CORP_NO" = 'A10VA'

AND "CMM_GRP_CODE"= 'DSC0006'

AND "CMM_CODE"= '01';

left join(T1)

T2:

LOAD
"CMPN_NO" &'|'& "CORP_NO" &'|'& "CMM_GRP_CODE" &'|'& "CMM_CODE" as key,
"CMPN_NO",
"CORP_NO",
"CMM_GRP_CODE",
"CMM_CODE",
"LANG_CODE",
"CMM_CODE_TXT";
SQL SELECT "CMPN_NO",
"CORP_NO",
"CMM_GRP_CODE",
"CMM_CODE",
"LANG_CODE",
"CMM_CODE_TXT"
FROM DMSDBA."CMM_CMCDLG_TB"
where "LANG_CODE" ='en'

;

// left join ($(vTableName))
T1_final:

load
// "CMM_CODE_TXT",
// "CMM_CODE_NAME",
key ,
// CMM_GRP_CODE,
// CMM_CODE,
// Date(Date#('20221202'), "DD/MM/YYYY") as Date,
Date(20221202,'DD/MM/YYYY') as [DATE],
Date(floor(57268)) as floor_,
if(isnull("CMM_CODE_TXT"),("CMM_CODE_NAME"),"CMM_CODE_TXT") as V_codename
Resident T1;
drop table T1;
exit script;