Datatype missmatch between two tables

Q1983 — Thu, 13 Feb 2020 15:27:54 GMT

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.

Re: Datatype missmatch between two tables

Krueger — Thu, 13 Feb 2020 15:49:19 GMT

Since your using SQL Passthrough convert it within the passthrough and do the join here. There is the CAST() and CONVERT() functions so you can modify whichever field you choose. If you have example's of the fields it would be easier to show you.

So you would basically do:

join b on a.charfield = cast(b.numericvar as varchar(17));

topic Re: Datatype missmatch between two tables in SAS Programming

Datatype missmatch between two tables

Re: Datatype missmatch between two tables