Proc SQL stimer;
Connect to DB2 (&applogin);
Create table TRN_FACT as
(Select distinct *,put(PST50_ACCT_NUM,z17.) as pst50_acct_nbr
from Connection to DB2
(SELECT distinct PST50_ACCT_NUM,
PST50_POST_DT,
PST50_POST_SEQ,
PST50_TRAN_CD_EX,
PST50_AMT,
pst50_DB_CR_IND,
DEP_ACCT_ID
FROM REGE.V_FT_PST50_TRN_FACT
/*where PST50_AMT = 100*/
where (PST50_POST_DT BETWEEN &beg_mth6. and &Last_Mth.)/*6 month period*/
with ur));
Disconnect from DB2;
Quit;
Proc SQL;
Connect to DB2 (&applogin);
Create table t_sess as
(Select distinct *
from Connection to DB2
(Select distinct ACCT_NBR, /*as sess_acct_nbr,/*char17/*get the count for the total opened*/
/*BAL_VAL_PNT_CNT,*/
ACCT_OPN_AMT,
CST_CTR_CD,
CUST_FULL_NM,
EMP_NBR,
OPN_BAL_DT,
SESS_DT,
PROD_CD,
LOB_CD,
ROLE_CD,
ROLE_NM,
SEQ_NBR,
UNT_VAL_PNT_CNT,
bi_as_of_dt
from RAZ_SRC.T_SESS_DTL
where (ACCT_OPN_AMT = 100)
and (OPN_BAL_DT between &beg_mth6. and &Last_Mth.)
and (SESS_DT between &beg_mth6. and &end_mth3.)
and (ACCT_OPN_AMT NOT IN (0))
/*and EMP_NBR = '200545'*/
/*and (UNT_VAL_PNT_CNT +BAL_VAL_PNT_CNT > 0 )*/
with ur));
Disconnect from DB2;
Quit;
I want to do an inner join from the TRN_FACT table PST50_ACCT_NUM to the ACCT_NBR in the T_SESS table. At issue is that the PST50_ACCT_NUM is numeric and the ACCT_NBR is char. Currently I bring in both datasets separately however both are quite huge and I get timeout errors.
In the TRN_FACT table I defined the PST50_ACCT_NUM as a character.(put(PST50_ACCT_NUM,z17.) as pst50_acct_nbr)
Is there a way to define and use it to join the T_Sess table within the same pass through routine.
... View more
