Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I need to combine two fields Last Name, First Name?
[PhrLName], | [PhrFName], | |
Winkel | Bob | |
Smith | John | |
To be | ||
Winkel, Bob | ||
Smith, John |
LOAD [PhrNPI], |
[PhrLName], |
[PhrFName], |
SQL SELECT "PhrNPI", |
"PhrLName", |
"PhrFName", |
LOAD [PhrNPI],
| ||
[PhrFName], | ||
SQL SELECT "PhrNPI", | ||
"PhrLName", | ||
"PhrFName", |
[PhrLName]&' ' & [PhrFName] as FullName
Fname&', '&Lname as fullname
In the script you can do as below:
Data:
Load
Lastname &',' &Firstname as Name;
LOAD * Inline [
Lastname,Firstname
Winkel,Bob
Smith,John
];
LOAD [PhrNPI],
| ||
[PhrFName], | ||
SQL SELECT "PhrNPI", | ||
"PhrLName", | ||
"PhrFName", |
I have just tried this action and am receiving an error message, the name fields are the same type, unsure why this happened???
SQL##f - SqlState: 37000, ErrorCode: 402, ErrorMsg: [Microsoft][ODBC SQL Server Driver][SQL Server]The data types nvarchar and varchar are incompatible in the '&' operator.
SQL SELECT "ACCT_NUM",
"ACCT_STTS_CTGY_CD",
"AUDT_ID",
"CMDTY_CD",
"DATA_SRC_CD",
"EMPE_FRST_NM",
"EMPE_ID",
"EMPE_LST_NM",
"EMPE_FRST_NM" & ' ' & "EMPE_LST_NM" as "CitiFullName",
"ETL_CYC_DT",
"HRCH_LVL_1_CD",
"HRCH_LVL_1_NM",
"HRCH_LVL_2_CD",
"HRCH_LVL_2_NM",
"HRCH_LVL_3_CD",
"HRCH_LVL_3_NM",
"ITEM_DSCR",
"LN_ITEM_DSCR",
MCC,
"MCC_CTGY",
"MCC_DSCR",
"PRCH_HRCH_NM",
"TCKT_NUM",
"TRAN_AMT",
"TRAN_CD_GRP",
"TRAN_CUR_CD",
"TRAN_DB_CR_IND",
"TRAN_DT",
"TRAN_TYP_DSCR",
"VND_CITY_NM",
"VND_CNTRY_CD",
"VND_NM",
"VND_ST_CD",
"VND_ZIP_CD"
FROM "DMBA_SIU".dbo."T_CITICARD_CHRG_PRST"
You may use a preceding load on top of the SQL query and in the preceding load you can use your logic to create the new field.
LOAD
"ACCT_NUM",
"ACCT_STTS_CTGY_CD",
"AUDT_ID",
"CMDTY_CD",
"DATA_SRC_CD",
"EMPE_FRST_NM",
"EMPE_ID",
"EMPE_LST_NM",
"EMPE_FRST_NM" & ' ' & "EMPE_LST_NM" as "CitiFullName",
"ETL_CYC_DT",
"HRCH_LVL_1_CD",
"HRCH_LVL_1_NM",
"HRCH_LVL_2_CD",
"HRCH_LVL_2_NM",
"HRCH_LVL_3_CD",
"HRCH_LVL_3_NM",
"ITEM_DSCR",
"LN_ITEM_DSCR",
MCC,
"MCC_CTGY",
"MCC_DSCR",
"PRCH_HRCH_NM",
"TCKT_NUM",
"TRAN_AMT",
"TRAN_CD_GRP",
"TRAN_CUR_CD",
"TRAN_DB_CR_IND",
"TRAN_DT",
"TRAN_TYP_DSCR",
"VND_CITY_NM",
"VND_CNTRY_CD",
"VND_NM",
"VND_ST_CD",
"VND_ZIP_CD";
SQL SELECT "ACCT_NUM",
"ACCT_STTS_CTGY_CD",
"AUDT_ID",
"CMDTY_CD",
"DATA_SRC_CD",
"EMPE_FRST_NM",
"EMPE_ID",
"EMPE_LST_NM",
"ETL_CYC_DT",
"HRCH_LVL_1_CD",
"HRCH_LVL_1_NM",
"HRCH_LVL_2_CD",
"HRCH_LVL_2_NM",
"HRCH_LVL_3_CD",
"HRCH_LVL_3_NM",
"ITEM_DSCR",
"LN_ITEM_DSCR",
MCC,
"MCC_CTGY",
"MCC_DSCR",
"PRCH_HRCH_NM",
"TCKT_NUM",
"TRAN_AMT",
"TRAN_CD_GRP",
"TRAN_CUR_CD",
"TRAN_DB_CR_IND",
"TRAN_DT",
"TRAN_TYP_DSCR",
"VND_CITY_NM",
"VND_CNTRY_CD",
"VND_NM",
"VND_ST_CD",
"VND_ZIP_CD"
FROM "DMBA_SIU".dbo."T_CITICARD_CHRG_PRST";