Vishal's stuff

 SELECT SRC_KEY_ASSET_ID , count(*) number_of_duplicates  FROM z4_rlob_sit4.asset WHERE SRC_SYSTEM_CODE='DM_RTL_SLO'   AND SNAPSHOT_DT BETWEEN 20210930 AND 20231130 GROUP BY SRC_KEY_ASSET_ID  HAVING count(*) > 1; SELECT *  FROM z4_rlob_sit4.asset WHERE SRC_KEY_ASSET_ID = '500000018553749-20231026-23191828890-20231026 14:22:09'   AND SRC_SYSTEM_CODE='DM_RTL_SLO'   AND SNAPSHOT_DT=20231031; SELECT * FROM z4_rlob_sit4.asset WHERE SRC_KEY_ASSET_ID IN (     SELECT SRC_KEY_ASSET_ID     FROM z4_rlob_sit4.asset     WHERE SRC_SYSTEM_CODE='DM_RTL_SLO'     AND SNAPSHOT_DT BETWEEN 20210930 AND 20231130     GROUP BY SRC_KEY_ASSET_ID     HAVING count(*) > 1 ); WITH Duplicates AS (     SELECT SRC_KEY_ASSET_ID     FROM z4_rlob_sit4.asset     WHERE SRC_SYSTEM_CODE='DM_RTL_SLO'     AND SNAPSHOT_DT BETWEEN 20210930 AND 20231130     GROUP BY SRC_KEY_ASSET_ID...

 USE ${RLOB_WORK_DATABASE}; SET hivevar:RLOB_MONTH_YM=CAST(${RLOB_MONTH_END}/100 as INT); SET hivevar:MULT_SYS_LOOKUP_PATH = '${RLOB_LOOKUPS_PATH}/ref_multimap_keyvalue/src_system=MULT_SYS'; SET hivevar:SOL_LEGAL_NAME =  CASE      WHEN NOT empty_null(NAME_OF_CONTACT) AND upper_trim(NAME_OF_CONTACT) <> 'SELF EMPLOYED' THEN upper_trim(parse_name(NAME_OF_CONTACT))     WHEN NOT empty_null(SOLICITOR_LAWYER_NAME) THEN upper_trim(parse_name(SOLICITOR_LAWYER_NAME))     ELSE NULL END ; SET hivevar:MACRO_PARTY =  ( SELECT *, RANK() OVER (PARTITION BY SRC_KEY_PARTY_ID ORDER BY SRC_SYSTEM_CODE DESC, SNAPSHOT_DT DESC) AS MAX_SNAPSHOT_DT FROM (     SELECT SRC_KEY_PARTY_ID, PARTY_ID, SNAPSHOT_DT, SRC_SYSTEM_CODE     FROM ${RLOB_DATABASE}.PARTY      WHERE SRC_SYSTEM_CODE IN ('${RLOB_SOURCE_SYSTEM_CODE}')     AND SNAPSHOT_DT < ${RLOB_SNAPSHOT_DATE}          UNION ALL   ...

 SET hivevar:MACRO_CALENDAR_YEAR_TO_DATE = ( CASE      WHEN upper_trim_std(ampl.LOAN_TYPE) = 'MORTGAGE' THEN      CASE         WHEN upper_trim_std(ampl.SERVICING_STATUS) <> 'DISCHARGED' THEN             nvl(ampl.ytd_reg_prin,0)         WHEN upper_trim_std(ampl.SERVICING_STATUS) = 'DISCHARGED' THEN             CASE                 WHEN year_month_day_time(${RLOB_SNAPSHOT_DATE}) - year_month_day_time(ampl.effective_date_of_discharge_org) > 0 THEN 0                                 ELSE nvl(ampl.last_mth_prin_paid,0)             END     END END )  ; SET hivevar:MACRO_CALENDAR_YEAR_TO_DATE1 = (  CASE      WHEN upper_trim_std(ampl.LOAN_TYPE) = 'MORTGAGE' THEN...

SELECT [ACCOUNT_MPL.MORTGAGE_BAL] INTO [TEMP.MORTGAGE_BAL] FROM [ACCOUNT_MPL] WHERE [ACCOUNT_MPL.appl_num] = [appl_num] AND [ACCOUNT_MPL.snapshot_month] = DATEADD(MONTH, -1, CURRENT_DATE); IF UPPER(TRIM([V_SERVICING.LOAN_TYPE])) = 'MORTGAGE' THEN     IF UPPER(TRIM([V_MORTGAGE_ACCOUNT_MANAGEMENT.SERVICING_STATUS])) <> 'DISCHARGED' THEN         ASSIGN NVL([V_SERVICING.YTD_PREPAY_MADE], 0) TO [TEMP.CAL_YR_TO_DT_ADD_PAID_AMT];     ELSE         IF (YEAR(CURRENT_DATE) - YEAR([V_MORTGAGE_ACCOUNT_MANAGEMENT.EFFECTIVE_DATE_OF_DISCHARGE_ORG])) > 0 THEN             ASSIGN 0 TO [TEMP.CAL_YR_TO_DT_ADD_PAID_AMT];         ELSE             ASSIGN NVL([V_SERVICING.YTD_PREPAY_MADE], 0) +                     (NVL([V_MORTGAGE_ACCOUNT_MANAGEMENT.CURRENT_PRINCIPAL_BAL], 0) -         ...

IF UPPER(TRIM([V_SERVICING.LOAN_TYPE])) = 'MORTGAGE' THEN     IF UPPER(TRIM([V_MORTGAGE_ACCOUNT_MANAGEMENT.SERVICING_STATUS])) = 'DISCHARGED' AND         (YEAR(CURRENT_DATE) - YEAR([V_MORTGAGE_ACCOUNT_MANAGEMENT.EFFECTIVE_DATE_OF_DISCHARGE_ORG])) > 0 THEN         ASSIGN 0 TO [TEMP.CAL_YR_TO_DT_INTRST_PAID_AMT];     ELSE         ASSIGN NVL([V_SERVICING.YTD_INT_PAID],0) TO [TEMP.CAL_YR_TO_DT_INTRST_PAID_AMT];     END IF; END IF; ASSIGN [TEMP.CAL_YR_TO_DT_INTRST_PAID_AMT] TO [ACCOUNT_MPL.CAL_YR_TO_DT_INTRST_PAID_AMT];

 Convert source element [V_MORTGAGE_ACCOUNT_MANAGEMENT.SERVICING_STATUS] &  [V_SERVICING.LOAN_TYPE] to upper case and trim leading and trailing whitespace. IF [V_SERVICING.LOAN_TYPE] = 'MORTGAGE' then   IF  [V_MORTGAGE_ACCOUNT_MANAGEMENT.SERVICING_STATUS] = 'DISCHARGED' AND IF (year component of current snapshot period - year component of [V_MORTGAGE_ACCOUNT_MANAGEMENT.effective_date_of_discharge_org]) > 0 then          Assign 0 to [TEMP.CAL_YR_TO_DT_INTRST_PAID_AMT]    ELSE           Assign nvl([V_SERVICING.ytd_int_paid],0) to [TEMP.CAL_YR_TO_DT_INTRST_PAID_AMT]    END IF END IF Assign [TEMP.CAL_YR_TO_DT_INTRST_PAID_AMT] to [ACCOUNT_MPL.CAL_YR_TO_DT_INTRST_PAID_AMT] Convert source element [V_MORTGAGE_ACCOUNT_MANAGEMENT.SERVICING_STATUS] & [V_SERVICING.LOAN_TYPE] to upper case and trim leading and trailing whitespace. Based on appl_num join to ACCOUNT_MPL and for the immediately precedin...

-- Assuming [ACCOUNT_MPL.SNAPSHOT_MONTH] is a date column representing the snapshot month -- Fetching the CAL_YR_TO_DT_PRIN_PAID_AMT for the immediately preceding snapshot month SELECT     A1.APPL_NUM,     A1.CAL_YR_TO_DT_PRIN_PAID_AMT AS last_mth_prin_paid FROM     ACCOUNT_MPL A1 JOIN     ACCOUNT_MPL A2 ON A1.APPL_NUM = A2.APPL_NUM WHERE     A1.SNAPSHOT_MONTH = DATEADD(MONTH, -1, CURRENT_DATE) -- Current snapshot month     AND A2.SNAPSHOT_MONTH = DATEADD(MONTH, -2, CURRENT_DATE); -- Immediately preceding snapshot month

 -- Sample monthly delta extract of ACCOUNT_MPL data  -- from EDH2.0 TARGET data source for mortgage performance. -- Subquery to extract Target accounts -- for the current snapshot period. with target_extract as (     select distinct         -- Key elements         am.mortgage_num                  -- Base extract filter elements.         -- Include here for debugging even if they         -- are not needed to populate the RLOB entity.        ,current_principal_bal        ,effective_date_of_discharge_org        ,line_of_credit_bal        ,mortgage_product          ,am.proc_dt_drvd        ,servicing_status        ,sub_product1                 -- Remaining ...

 #!/bin/sh #Keep 7days data into main table and drop older partition. set -x if [ $# -ne 2 ]; then   echo 'Invalid number of arguments --  <1.ENV> <2.Load_type>'  exit 2 else   echo Argument passed correctly. Script Now Running for Environment: $1 fi DIRNAME=`dirname $0` PWD=`pwd` echo PWD is $PWD echo DIRNAME is $DIRNAME if [ "$DIRNAME" = "." ]; then    export SCRIPT=$PWD ; else    export SCRIPT=$DIRNAME ; fi hive_table_name=creb_timer_full echo hive table name is : $hive_table_name script_edh=${SCRIPT%/*} echo script path is : $script_edh DB_DIR=${script_edh%/*} echo DB_DIR is :  $DB_DIR db=${DB_DIR##*/} echo database is :$db z2db=edh_50060_PSH_rb_$1 echo z2 db is : $z2db echo Selecting First three bytes of argument to match environment name... ENV=`echo $1 | cut -c 1-3` case "$ENV" in          "sit") export  connect_string="!connect jdbc:hive2://edh2dev.cloud1.cibc.com:10005/;principal=hive/_H...