Scribd
Upload
51 views7 pages

Account T24

The document contains an SQL query that selects data from multiple tables and columns. It selects over 100 data fields including account details, transaction details, balances, customer information, and other financial data. The data is extracted from XML records and tables using various functions to retrieve values from specific XML tags and table columns.

Uploaded by

Tiến Nam
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
51 views7 pages

Account T24

The document contains an SQL query that selects data from multiple tables and columns. It selects over 100 data fields including account details, transaction details, balances, customer information, and other financial data. The data is extracted from XML records and tables using various functions to retrieve values from specific XML tags and table columns.

Uploaded by

Tiến Nam
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
You are on page 1/ 7

SELECT a.RECID, a.XMLRECORD "THE_RECORD",a.

RECID "ACCOUNT_NUMBER"
,a.RECID "ACCT_ID"
,a.RECID "SOURCE_ID"
,extractValue(b.XMLRECORD,'/row/c23[position()=1]') "SECTOR"
,extractValue(b.XMLRECORD,'/row/c26[position()=1]') "INDUSTRY"
,extractValue(b.XMLRECORD,'/row/c27[position()=1]') "TARGET"
,extractValue(b.XMLRECORD,'/row/c30[position()=1]') "RESIDENCE"
,a.RECID "ACCOUNT_NO"
,extractValue(a.XMLRECORD,'/row/c1[position()=1]') "CUSTOMER_NO"
,extractValue(a.XMLRECORD,'/row/c8[position()=1]') "PRODCCY"
,tafjsubstrings(a.RECID, '9', '4') "TELLER_ID"
,extractValue(c.XMLRECORD,'/row/c1[position()=1]') "ACCT_ENT_TODAY"
,tafjcat(extractValue(a.XMLRECORD,'/row/
c21[position()=1]'),extractValue(a.XMLRECORD,'/row/c8[position()=1]')) "GRP_CCY"
,tafjcat(tafjcat(extractValue(a.XMLRECORD,'/row/c21[position()=1]'),'-'),a.RECID)
"GRP_AC_NO"
,extractValue(d.XMLRECORD,'/row/c33[position()=1]') "COUNTRY_RISK"
,extractValue(a.XMLRECORD,'/row/c1[position()=1]') "CUSTOMER"
,extractValue(a.XMLRECORD,'/row/c2[position()=1]') "CATEGORY"
,extractValue(a.XMLRECORD,'/row/c2[position()=1]') "PRODCAT"
,extractValue(a.XMLRECORD,'/row/c3[position()=1]') "ACCOUNT_TITLE_1"
,extract(a.XMLRECORD,'/row/c3') "ACCOUNT_TITLE_1_3"
,extractValue(a.XMLRECORD,'/row/c4[position()=1]') "ACCOUNT_TITLE_2"
,extract(a.XMLRECORD,'/row/c4') "ACCOUNT_TITLE_2_4"
,extractValue(a.XMLRECORD,'/row/c5[position()=1]') "SHORT_TITLE"
,extract(a.XMLRECORD,'/row/c5') "SHORT_TITLE_5"
,extractValue(a.XMLRECORD,'/row/c6[position()=1]') "MNEMONIC"
,extractValue(a.XMLRECORD,'/row/c7[position()=1]') "POSITION_TYPE"
,extractValue(a.XMLRECORD,'/row/c8[position()=1]') "CURRENCY"
,extractValue(a.XMLRECORD,'/row/c9[position()=1]') "CURRENCY_MARKET"
,extractValue(a.XMLRECORD,'/row/c10[position()=1]') "LIMIT_REF"
,extractValue(a.XMLRECORD,'/row/c11[position()=1]') "ACCOUNT_OFFICER"
,extractValue(a.XMLRECORD,'/row/c12[position()=1]') "OTHER_OFFICER"
,extract(a.XMLRECORD,'/row/c12') "OTHER_OFFICER_12"
,extractValue(a.XMLRECORD,'/row/c13[position()=1]') "POSTING_RESTRICT"
,extract(a.XMLRECORD,'/row/c13') "POSTING_RESTRICT_13"
,extractValue(a.XMLRECORD,'/row/c14[position()=1]') "RECONCILE_ACCT"
,extractValue(a.XMLRECORD,'/row/c15[position()=1]') "INTEREST_LIQU_ACCT"
,extractValue(a.XMLRECORD,'/row/c16[position()=1]') "INTEREST_COMP_ACCT"
,extractValue(a.XMLRECORD,'/row/c17[position()=1]') "INT_NO_BOOKING"
,extractValue(a.XMLRECORD,'/row/c18[position()=1]') "REFERAL_CODE"
,extract(a.XMLRECORD,'/row/c18') "REFERAL_CODE_18"
,extractValue(a.XMLRECORD,'/row/c19[position()=1]') "WAIVE_LEDGER_FEE"
,extractValue(a.XMLRECORD,'/row/c20[position()=1]') "WELCOME_PACK"
,extractValue(a.XMLRECORD,'/row/c20[@m="2"][1]') "BIC_MODE_OPS"
,extractValue(a.XMLRECORD,'/row/c20[@m="3"][1]') "BIC_NBC_SECTOR"
,extractValue(a.XMLRECORD,'/row/c20[@m="4"][1]') "BIC_PURPOSE"
,extract(a.XMLRECORD,'/row/c20[@m="4"]') "BIC_PURPOSE_20"
,extractValue(a.XMLRECORD,'/row/c20[@m="5"][1]') "BIC_CHANNEL"
,extractValue(a.XMLRECORD,'/row/c20[@m="6"][1]') "LOAN_SECTOR"
,extractValue(a.XMLRECORD,'/row/c20[@m="7"][1]') "LOAN_SUB_SECTOR"
,extractValue(a.XMLRECORD,'/row/c20[@m="8"][1]') "RESTRUCTURE_FLG"
,extractValue(a.XMLRECORD,'/row/c20[@m="9"][1]') "BIC_MAIN_INCOME"
,extractValue(a.XMLRECORD,'/row/c20[@m="10"][1]') "BIC_SUB_INCOME"
,extractValue(a.XMLRECORD,'/row/c20[@m="11"][1]') "BIC_RES_DATE"
,extractValue(a.XMLRECORD,'/row/c20[@m="12"][1]') "BIC_RES_MDATE"
,extractValue(a.XMLRECORD,'/row/c20[@m="13"][1]') "BIC_RES_REMARK"
,extractValue(a.XMLRECORD,'/row/c20[@m="14"][1]') "BIC_NOTE"
,extractValue(a.XMLRECORD,'/row/c20[@m="15"][1]') "BIC_MEMBER_ENQ"
,extractValue(a.XMLRECORD,'/row/c21[position()=1]') "CONDITION_GROUP"
,extractValue(a.XMLRECORD,'/row/c22[position()=1]') "INACTIV_MARKER"
,extractValue(a.XMLRECORD,'/row/c23[position()=1]') "OPEN_ACTUAL_BAL"
,extractValue(a.XMLRECORD,'/row/c24[position()=1]') "OPEN_CLEARED_BAL"
,extractValue(a.XMLRECORD,'/row/c25[position()=1]') "ONLINE_ACTUAL_BAL"
,extractValue(a.XMLRECORD,'/row/c26[position()=1]') "ONLINE_CLEARED_BAL"
,extractValue(a.XMLRECORD,'/row/c27[position()=1]') "WORKING_BALANCE"
,extractValue(a.XMLRECORD,'/row/c28[position()=1]') "DATE_LAST_CR_CUST"
,extractValue(a.XMLRECORD,'/row/c29[position()=1]') "AMNT_LAST_CR_CUST"
,extractValue(a.XMLRECORD,'/row/c30[position()=1]') "TRAN_LAST_CR_CUST"
,extractValue(a.XMLRECORD,'/row/c31[position()=1]') "DATE_LAST_CR_AUTO"
,extractValue(a.XMLRECORD,'/row/c32[position()=1]') "AMNT_LAST_CR_AUTO"
,extractValue(a.XMLRECORD,'/row/c33[position()=1]') "TRAN_LAST_CR_AUTO"
,extractValue(a.XMLRECORD,'/row/c34[position()=1]') "DATE_LAST_CR_BANK"
,extractValue(a.XMLRECORD,'/row/c35[position()=1]') "AMNT_LAST_CR_BANK"
,extractValue(a.XMLRECORD,'/row/c36[position()=1]') "TRAN_LAST_CR_BANK"
,extractValue(a.XMLRECORD,'/row/c37[position()=1]') "DATE_LAST_DR_CUST"
,extractValue(a.XMLRECORD,'/row/c38[position()=1]') "AMNT_LAST_DR_CUST"
,extractValue(a.XMLRECORD,'/row/c39[position()=1]') "TRAN_LAST_DR_CUST"
,extractValue(a.XMLRECORD,'/row/c40[position()=1]') "DATE_LAST_DR_AUTO"
,extractValue(a.XMLRECORD,'/row/c41[position()=1]') "AMNT_LAST_DR_AUTO"
,extractValue(a.XMLRECORD,'/row/c42[position()=1]') "TRAN_LAST_DR_AUTO"
,extractValue(a.XMLRECORD,'/row/c43[position()=1]') "DATE_LAST_DR_BANK"
,extractValue(a.XMLRECORD,'/row/c44[position()=1]') "AMNT_LAST_DR_BANK"
,extractValue(a.XMLRECORD,'/row/c45[position()=1]') "TRAN_LAST_DR_BANK"
,extractValue(a.XMLRECORD,'/row/c46[position()=1]') "CAP_DATE_CHARGE"
,extract(a.XMLRECORD,'/row/c46') "CAP_DATE_CHARGE_46"
,extractValue(a.XMLRECORD,'/row/c47[position()=1]') "CAP_DATE_CR_INT"
,extract(a.XMLRECORD,'/row/c47') "CAP_DATE_CR_INT_47"
,extractValue(a.XMLRECORD,'/row/c48[position()=1]') "CAP_DATE_C2_INT"
,extract(a.XMLRECORD,'/row/c48') "CAP_DATE_C2_INT_48"
,extractValue(a.XMLRECORD,'/row/c49[position()=1]') "CAP_DATE_DR_INT"
,extract(a.XMLRECORD,'/row/c49') "CAP_DATE_DR_INT_49"
,extractValue(a.XMLRECORD,'/row/c50[position()=1]') "CAP_DATE_D2_INT"
,extract(a.XMLRECORD,'/row/c50') "CAP_DATE_D2_INT_50"
,extractValue(a.XMLRECORD,'/row/c51[position()=1]') "CAP_BACK_VALUE"
,extractValue(a.XMLRECORD,'/row/c52[position()=1]') "ACCR_CHG_CATEG"
,extract(a.XMLRECORD,'/row/c52') "ACCR_CHG_CATEG_52"
,extractValue(a.XMLRECORD,'/row/c53[position()=1]') "ACCR_CHG_TRANS"
,extract(a.XMLRECORD,'/row/c53') "ACCR_CHG_TRANS_53"
,extractValue(a.XMLRECORD,'/row/c54[position()=1]') "ACCR_CHG_AMOUNT"
,extract(a.XMLRECORD,'/row/c54') "ACCR_CHG_AMOUNT_54"
,extractValue(a.XMLRECORD,'/row/c55[position()=1]') "ACCR_CHG_SUSP"
,extract(a.XMLRECORD,'/row/c55') "ACCR_CHG_SUSP_55"
,extractValue(a.XMLRECORD,'/row/c56[position()=1]') "ACCR_CR_CATEG"
,extract(a.XMLRECORD,'/row/c56') "ACCR_CR_CATEG_56"
,extractValue(a.XMLRECORD,'/row/c57[position()=1]') "ACCR_CR_TRANS"
,extract(a.XMLRECORD,'/row/c57') "ACCR_CR_TRANS_57"
,extractValue(a.XMLRECORD,'/row/c58[position()=1]') "ACCR_CR_AMOUNT"
,extract(a.XMLRECORD,'/row/c58') "ACCR_CR_AMOUNT_58"
,extractValue(a.XMLRECORD,'/row/c59[position()=1]') "ACCR_CR_SUSP"
,extract(a.XMLRECORD,'/row/c59') "ACCR_CR_SUSP_59"
,extractValue(a.XMLRECORD,'/row/c60[position()=1]') "ACCR_CR2_CATEG"
,extract(a.XMLRECORD,'/row/c60') "ACCR_CR2_CATEG_60"
,extractValue(a.XMLRECORD,'/row/c61[position()=1]') "ACCR_CR2_TRANS"
,extract(a.XMLRECORD,'/row/c61') "ACCR_CR2_TRANS_61"
,extractValue(a.XMLRECORD,'/row/c62[position()=1]') "ACCR_CR2_AMOUNT"
,extract(a.XMLRECORD,'/row/c62') "ACCR_CR2_AMOUNT_62"
,extractValue(a.XMLRECORD,'/row/c63[position()=1]') "ACCR_CR2_SUSP"
,extract(a.XMLRECORD,'/row/c63') "ACCR_CR2_SUSP_63"
,extractValue(a.XMLRECORD,'/row/c64[position()=1]') "ACCR_DR_CATEG"
,extract(a.XMLRECORD,'/row/c64') "ACCR_DR_CATEG_64"
,extractValue(a.XMLRECORD,'/row/c65[position()=1]') "ACCR_DR_TRANS"
,extract(a.XMLRECORD,'/row/c65') "ACCR_DR_TRANS_65"
,extractValue(a.XMLRECORD,'/row/c66[position()=1]') "ACCR_DR_AMOUNT"
,extract(a.XMLRECORD,'/row/c66') "ACCR_DR_AMOUNT_66"
,extractValue(a.XMLRECORD,'/row/c67[position()=1]') "ACCR_DR_SUSP"
,extract(a.XMLRECORD,'/row/c67') "ACCR_DR_SUSP_67"
,extractValue(a.XMLRECORD,'/row/c68[position()=1]') "ACCR_DR2_CATEG"
,extract(a.XMLRECORD,'/row/c68') "ACCR_DR2_CATEG_68"
,extractValue(a.XMLRECORD,'/row/c69[position()=1]') "ACCR_DR2_TRANS"
,extract(a.XMLRECORD,'/row/c69') "ACCR_DR2_TRANS_69"
,extractValue(a.XMLRECORD,'/row/c70[position()=1]') "ACCR_DR2_AMOUNT"
,extract(a.XMLRECORD,'/row/c70') "ACCR_DR2_AMOUNT_70"
,extractValue(a.XMLRECORD,'/row/c71[position()=1]') "ACCR_DR2_SUSP"
,extract(a.XMLRECORD,'/row/c71') "ACCR_DR2_SUSP_71"
,extractValue(a.XMLRECORD,'/row/c72[position()=1]') "CONSOL_KEY"
,extractValue(a.XMLRECORD,'/row/c73[position()=1]') "INT_LIQU_TYPE"
,extract(a.XMLRECORD,'/row/c73') "INT_LIQU_TYPE_73"
,extractValue(a.XMLRECORD,'/row/c74[position()=1]') "INT_LIQU_ACCT"
,extract(a.XMLRECORD,'/row/c74') "INT_LIQU_ACCT_74"
,extractValue(a.XMLRECORD,'/row/c75[position()=1]') "INT_LIQ_CCY"
,extract(a.XMLRECORD,'/row/c75') "INT_LIQ_CCY_75"
,extractValue(a.XMLRECORD,'/row/c76[position()=1]') "PASSBOOK"
,extractValue(a.XMLRECORD,'/row/c77[position()=1]') "START_YEAR_BAL"
,extractValue(a.XMLRECORD,'/row/c78[position()=1]') "OPENING_DATE"
,extractValue(a.XMLRECORD,'/row/c79[position()=1]') "VALUE_DATE"
,extract(a.XMLRECORD,'/row/c79') "VALUE_DATE_79"
,extractValue(a.XMLRECORD,'/row/c80[position()=1]') "CREDIT_MOVEMENT"
,extract(a.XMLRECORD,'/row/c80') "CREDIT_MOVEMENT_80"
,extractValue(a.XMLRECORD,'/row/c81[position()=1]') "DEBIT_MOVEMENT"
,extract(a.XMLRECORD,'/row/c81') "DEBIT_MOVEMENT_81"
,extractValue(a.XMLRECORD,'/row/c82[position()=1]') "VALUE_DATED_BAL"
,extract(a.XMLRECORD,'/row/c82') "VALUE_DATED_BAL_82"
,extractValue(a.XMLRECORD,'/row/c83[position()=1]') "CONTINGENT_BAL_CR"
,extractValue(a.XMLRECORD,'/row/c84[position()=1]') "CONTINGENT_BAL_DR"
,extractValue(a.XMLRECORD,'/row/c85[position()=1]') "OPEN_CATEGORY"
,extractValue(a.XMLRECORD,'/row/c86[position()=1]') "OPEN_VAL_DATED_BAL"
,extractValue(a.XMLRECORD,'/row/c87[position()=1]') "ACCT_CREDIT_INT"
,extract(a.XMLRECORD,'/row/c87') "ACCT_CREDIT_INT_87"
,extractValue(a.XMLRECORD,'/row/c88[position()=1]') "ACCT_DEBIT_INT"
,extract(a.XMLRECORD,'/row/c88') "ACCT_DEBIT_INT_88"
,extractValue(a.XMLRECORD,'/row/c89[position()=1]') "LINK_TO_LIMIT"
,extractValue(a.XMLRECORD,'/row/c90[position()=1]') "CLOSURE_DATE"
,extractValue(a.XMLRECORD,'/row/c91[position()=1]') "LOCKED_WITH_LIMIT"
,extractValue(a.XMLRECORD,'/row/c92[position()=1]') "CHARGE_ACCOUNT"
,extractValue(a.XMLRECORD,'/row/c93[position()=1]') "CHARGE_CCY"
,extractValue(a.XMLRECORD,'/row/c94[position()=1]') "CHARGE_MKT"
,extractValue(a.XMLRECORD,'/row/c95[position()=1]') "INTEREST_CCY"
,extractValue(a.XMLRECORD,'/row/c96[position()=1]') "INTEREST_MKT"
,extractValue(a.XMLRECORD,'/row/c97[position()=1]') "CON_CHARGE_ACCR"
,extractValue(a.XMLRECORD,'/row/c98[position()=1]') "CON_INTEREST_ACCR"
,extractValue(a.XMLRECORD,'/row/c99[position()=1]') "ALT_ACCT_TYPE"
,extract(a.XMLRECORD,'/row/c99') "ALT_ACCT_TYPE_99"
,extractValue(a.XMLRECORD,'/row/c100[position()=1]') "ALT_ACCT_ID"
,extract(a.XMLRECORD,'/row/c100') "ALT_ACCT_ID_100"
,extractValue(a.XMLRECORD,'/row/c101[position()=1]') "PREMIUM_TYPE"
,extract(a.XMLRECORD,'/row/c101') "PREMIUM_TYPE_101"
,extractValue(a.XMLRECORD,'/row/c102[position()=1]') "CAP_DATE_PRM"
,extract(a.XMLRECORD,'/row/c102') "CAP_DATE_PRM_102"
,extractValue(a.XMLRECORD,'/row/c103[position()=1]') "PREMIUM_FREQ"
,extract(a.XMLRECORD,'/row/c103') "PREMIUM_FREQ_103"
,extractValue(a.XMLRECORD,'/row/c104[position()=1]') "APR"
,extractValue(a.XMLRECORD,'/row/c105[position()=1]') "JOINT_HOLDER"
,extract(a.XMLRECORD,'/row/c105') "JOINT_HOLDER_105"
,extractValue(a.XMLRECORD,'/row/c106[position()=1]') "RELATION_CODE"
,extract(a.XMLRECORD,'/row/c106') "RELATION_CODE_106"
,extractValue(a.XMLRECORD,'/row/c107[position()=1]') "JOINT_NOTES"
,extract(a.XMLRECORD,'/row/c107') "JOINT_NOTES_107"
,extractValue(a.XMLRECORD,'/row/c108[position()=1]') "ALLOW_NETTING"
,extractValue(a.XMLRECORD,'/row/c109[position()=1]') "LEDG_RECO_WITH"
,extract(a.XMLRECORD,'/row/c109') "LEDG_RECO_WITH_109"
,extractValue(a.XMLRECORD,'/row/c110[position()=1]') "STMT_RECO_WITH"
,extract(a.XMLRECORD,'/row/c110') "STMT_RECO_WITH_110"
,extractValue(a.XMLRECORD,'/row/c111[position()=1]') "OUR_EXT_ACCT_NO"
,extractValue(a.XMLRECORD,'/row/c112[position()=1]') "RECO_TOLERANCE"
,extractValue(a.XMLRECORD,'/row/c113[position()=1]') "PENDING_ID"
,extract(a.XMLRECORD,'/row/c113') "PENDING_ID_113"
,extractValue(a.XMLRECORD,'/row/c114[position()=1]') "TOTAL_PENDING"
,extract(a.XMLRECORD,'/row/c114') "TOTAL_PENDING_114"
,extractValue(a.XMLRECORD,'/row/c115[position()=1]') "STOCK_CONTROL_TYPE"
,extractValue(a.XMLRECORD,'/row/c116[position()=1]') "SERIAL_NO_FORMAT"
,extractValue(a.XMLRECORD,'/row/c117[position()=1]') "AUTO_PAY_ACCT"
,extractValue(a.XMLRECORD,'/row/c118[position()=1]') "ORIG_CCY_PAYMENT"
,extractValue(a.XMLRECORD,'/row/c119[position()=1]') "AUTO_REC_CCY"
,extract(a.XMLRECORD,'/row/c119') "AUTO_REC_CCY_119"
,extractValue(a.XMLRECORD,'/row/c120[position()=1]') "ORIGINAL_ACCT"
,extract(a.XMLRECORD,'/row/c120') "ORIGINAL_ACCT_120"
,extractValue(a.XMLRECORD,'/row/c121[position()=1]') "FROM_DATE"
,extract(a.XMLRECORD,'/row/c121') "FROM_DATE_121"
,extractValue(a.XMLRECORD,'/row/c122[position()=1]') "LOCKED_AMOUNT"
,extract(a.XMLRECORD,'/row/c122') "LOCKED_AMOUNT_122"
,extractValue(a.XMLRECORD,'/row/c123[position()=1]') "DISPO_OFFICER"
,extractValue(a.XMLRECORD,'/row/c124[position()=1]') "DISPO_EXEMPT"
,extractValue(a.XMLRECORD,'/row/c125[position()=1]') "TAX_SUSPEND"
,extractValue(a.XMLRECORD,'/row/c126[position()=1]') "TAX_AT_SETTLE"
,extractValue(a.XMLRECORD,'/row/c127[position()=1]') "ICA_MAIN_ACCOUNT"
,extractValue(a.XMLRECORD,'/row/c128[position()=1]') "ICA_DISTRIB_RATIO"
,extractValue(a.XMLRECORD,'/row/c129[position()=1]') "ICA_MAIN_ACCT_IND"
,extractValue(a.XMLRECORD,'/row/c130[position()=1]') "ICA_DISTRIB_TYPE"
,extractValue(a.XMLRECORD,'/row/c131[position()=1]') "ICA_POST_INTEREST"
,extractValue(a.XMLRECORD,'/row/c132[position()=1]') "ICA_MAIN_RATIO"
,extractValue(a.XMLRECORD,'/row/c133[position()=1]') "ICA_NEW_MAIN_ACC"
,extractValue(a.XMLRECORD,'/row/c134[position()=1]') "ICA_START_DATE"
,extractValue(a.XMLRECORD,'/row/c135[position()=1]') "ICA_ADD_REMOVE"
,extractValue(a.XMLRECORD,'/row/c136[position()=1]') "ICA_BACK_VALUE"
,extractValue(a.XMLRECORD,'/row/c137[position()=1]') "ICA_MAIN_ACCT"
,extract(a.XMLRECORD,'/row/c137') "ICA_MAIN_ACCT_137"
,extractValue(a.XMLRECORD,'/row/c138[position()=1]') "ICA_MAIN_DATE"
,extract(a.XMLRECORD,'/row/c138') "ICA_MAIN_DATE_138"
,extractValue(a.XMLRECORD,'/row/c139[position()=1]') "LIQUIDATION_MODE"
,extractValue(a.XMLRECORD,'/row/c140[position()=1]') "OVERDUE_STATUS"
,extractValue(a.XMLRECORD,'/row/c141[position()=1]') "HVT_FLAG"
,extractValue(a.XMLRECORD,'/row/c142[position()=1]') "SINGLE_LIMIT"
,extractValue(a.XMLRECORD,'/row/c143[position()=1]') "CONTINGENT_INT"
,extractValue(a.XMLRECORD,'/row/c144[position()=1]') "ALL_IN_ONE_PRODUCT"
,extractValue(a.XMLRECORD,'/row/c145[position()=1]') "ER_VALUE_DATE"
,extract(a.XMLRECORD,'/row/c145') "ER_VALUE_DATE_145"
,extractValue(a.XMLRECORD,'/row/c146[position()=1]') "ER_BALANCE"
,extract(a.XMLRECORD,'/row/c146') "ER_BALANCE_146"
,extractValue(a.XMLRECORD,'/row/c147[position()=1]') "EP_BALANCE"
,extract(a.XMLRECORD,'/row/c147') "EP_BALANCE_147"
,extractValue(a.XMLRECORD,'/row/c148[position()=1]') "SB_GROUP_ID"
,extractValue(a.XMLRECORD,'/row/c149[position()=1]') "OPEN_AVAILABLE_BAL"
,extractValue(a.XMLRECORD,'/row/c150[position()=1]') "AVAILABLE_DATE"
,extract(a.XMLRECORD,'/row/c150') "AVAILABLE_DATE_150"
,extractValue(a.XMLRECORD,'/row/c151[position()=1]') "AV_AUTH_DB_MVMT"
,extract(a.XMLRECORD,'/row/c151') "AV_AUTH_DB_MVMT_151"
,extractValue(a.XMLRECORD,'/row/c152[position()=1]') "AV_NAU_DB_MVMT"
,extract(a.XMLRECORD,'/row/c152') "AV_NAU_DB_MVMT_152"
,extractValue(a.XMLRECORD,'/row/c153[position()=1]') "AV_AUTH_CR_MVMT"
,extract(a.XMLRECORD,'/row/c153') "AV_AUTH_CR_MVMT_153"
,extractValue(a.XMLRECORD,'/row/c154[position()=1]') "AV_NAU_CR_MVMT"
,extract(a.XMLRECORD,'/row/c154') "AV_NAU_CR_MVMT_154"
,extractValue(a.XMLRECORD,'/row/c155[position()=1]') "AVAILABLE_BAL"
,extract(a.XMLRECORD,'/row/c155') "AVAILABLE_BAL_155"
,extractValue(a.XMLRECORD,'/row/c156[position()=1]') "FORWARD_MVMTS"
,extract(a.XMLRECORD,'/row/c156') "FORWARD_MVMTS_156"
,extractValue(a.XMLRECORD,'/row/c157[position()=1]') "CREDIT_CHECK"
,extractValue(a.XMLRECORD,'/row/c158[position()=1]') "AVAILABLE_BAL_UPD"
,extractValue(a.XMLRECORD,'/row/c159[position()=1]') "CONSOLIDATE_ENT"
,extractValue(a.XMLRECORD,'/row/c160[position()=1]') "MAX_SUB_ACCOUNT"
,extractValue(a.XMLRECORD,'/row/c161[position()=1]') "MASTER_ACCOUNT"
,extractValue(a.XMLRECORD,'/row/c162[position()=1]') "LOCK_INC_THIS_MVMT"
,extractValue(a.XMLRECORD,'/row/c163[position()=1]') "CLOSED_ONLINE"
,extractValue(a.XMLRECORD,'/row/c164[position()=1]') "NEXT_AF_DATE"
,extractValue(a.XMLRECORD,'/row/c165[position()=1]') "NEXT_ACCT_CAP"
,extractValue(a.XMLRECORD,'/row/c166[position()=1]') "NEXT_EXP_DATE"
,extractValue(a.XMLRECORD,'/row/c167[position()=1]') "DATE_LAST_UPDATE"
,extractValue(a.XMLRECORD,'/row/c168[position()=1]') "NEXT_STMT_DATE"
,extract(a.XMLRECORD,'/row/c168') "NEXT_STMT_DATE_168"
,extractValue(a.XMLRECORD,'/row/c169[position()=1]') "EXPOSURE_DATES"
,extract(a.XMLRECORD,'/row/c169') "EXPOSURE_DATES_169"
,extractValue(a.XMLRECORD,'/row/c170[position()=1]') "PORTFOLIO_NO"
,extract(a.XMLRECORD,'/row/c170') "PORTFOLIO_NO_170"
,extractValue(a.XMLRECORD,'/row/c171[position()=1]') "SHADOW_ACCOUNT"
,extractValue(a.XMLRECORD,'/row/c172[position()=1]') "FWD_ENTRY_HOLD"
,extract(a.XMLRECORD,'/row/c172') "FWD_ENTRY_HOLD_172"
,extractValue(a.XMLRECORD,'/row/c173[position()=1]') "FIRST_AF_DATE"
,extractValue(a.XMLRECORD,'/row/c174[position()=1]') "CASH_POOL_GROUP"
,extract(a.XMLRECORD,'/row/c174') "CASH_POOL_GROUP_174"
,extractValue(a.XMLRECORD,'/row/c175[position()=1]') "OPEN_ASSET_TYPE"
,extractValue(a.XMLRECORD,'/row/c176[position()=1]') "LAST_COM_CHG_DATE"
,extractValue(a.XMLRECORD,'/row/c177[position()=1]') "IC_CHARGE_ID"
,extractValue(a.XMLRECORD,'/row/c178[position()=1]') "IC_NEXT_CAP_DATE"
,extractValue(a.XMLRECORD,'/row/c179[position()=1]') "IC_PRODUCT"
,extract(a.XMLRECORD,'/row/c179') "IC_PRODUCT_179"
,extractValue(a.XMLRECORD,'/row/c180[position()=1]') "IC_LST_PROD_CAP"
,extract(a.XMLRECORD,'/row/c180') "IC_LST_PROD_CAP_180"
,extractValue(a.XMLRECORD,'/row/c181[position()=1]') "ARRANGEMENT_ID"
,extractValue(a.XMLRECORD,'/row/c182[position()=1]') "ACC_DEB_LIMIT"
,extract(a.XMLRECORD,'/row/c182') "ACC_DEB_LIMIT_182"
,extractValue(a.XMLRECORD,'/row/c183[position()=1]') "MANDATE_APPL"
,extract(a.XMLRECORD,'/row/c183') "MANDATE_APPL_183"
,extractValue(a.XMLRECORD,'/row/c184[position()=1]') "MANDATE_REG"
,extract(a.XMLRECORD,'/row/c184') "MANDATE_REG_184"
,extractValue(a.XMLRECORD,'/row/c185[position()=1]') "MANDATE_RECORD"
,extract(a.XMLRECORD,'/row/c185') "MANDATE_RECORD_185"
,extractValue(a.XMLRECORD,'/row/c186[position()=1]') "DR_ADJ_AMOUNT"
,extractValue(a.XMLRECORD,'/row/c187[position()=1]') "DR2_ADJ_AMOUNT"
,extractValue(a.XMLRECORD,'/row/c188[position()=1]') "CR_ADJ_AMOUNT"
,extractValue(a.XMLRECORD,'/row/c189[position()=1]') "CR2_ADJ_AMOUNT"
,extractValue(a.XMLRECORD,'/row/c190[position()=1]') "EVENT"
,extract(a.XMLRECORD,'/row/c190') "EVENT_190"
,extractValue(a.XMLRECORD,'/row/c191[position()=1]') "FIELD"
,extract(a.XMLRECORD,'/row/c191') "FIELD_191"
,extractValue(a.XMLRECORD,'/row/c192[position()=1]') "OPERAND"
,extract(a.XMLRECORD,'/row/c192') "OPERAND_192"
,extractValue(a.XMLRECORD,'/row/c193[position()=1]') "VALUE"
,extract(a.XMLRECORD,'/row/c193') "VALUE_193"
,extractValue(a.XMLRECORD,'/row/c194[position()=1]') "MV_ALERT_RES6"
,extract(a.XMLRECORD,'/row/c194') "MV_ALERT_RES6_194"
,extractValue(a.XMLRECORD,'/row/c195[position()=1]') "MV_ALERT_RES5"
,extract(a.XMLRECORD,'/row/c195') "MV_ALERT_RES5_195"
,extractValue(a.XMLRECORD,'/row/c196[position()=1]') "MV_ALERT_RES4"
,extract(a.XMLRECORD,'/row/c196') "MV_ALERT_RES4_196"
,extractValue(a.XMLRECORD,'/row/c197[position()=1]') "MV_ALERT_RES3"
,extract(a.XMLRECORD,'/row/c197') "MV_ALERT_RES3_197"
,extractValue(a.XMLRECORD,'/row/c198[position()=1]') "MV_ALERT_RES2"
,extract(a.XMLRECORD,'/row/c198') "MV_ALERT_RES2_198"
,extractValue(a.XMLRECORD,'/row/c199[position()=1]') "MV_ALERT_RES1"
,extract(a.XMLRECORD,'/row/c199') "MV_ALERT_RES1_199"
,extractValue(a.XMLRECORD,'/row/c200[position()=1]') "REQUEST_ID"
,extract(a.XMLRECORD,'/row/c200') "REQUEST_ID_200"
,extractValue(a.XMLRECORD,'/row/c201[position()=1]') "ACCOUNTING_COMPANY"
,extractValue(a.XMLRECORD,'/row/c202[position()=1]') "REF_DATA_ITEM"
,extract(a.XMLRECORD,'/row/c202') "REF_DATA_ITEM_202"
,extractValue(a.XMLRECORD,'/row/c203[position()=1]') "REF_DATA_VALUE"
,extract(a.XMLRECORD,'/row/c203') "REF_DATA_VALUE_203"
,extractValue(a.XMLRECORD,'/row/c204[position()=1]') "CLOSURE_REASON"
,extractValue(a.XMLRECORD,'/row/c205[position()=1]') "CLOSURE_NOTES"
,extract(a.XMLRECORD,'/row/c205') "CLOSURE_NOTES_205"
,extractValue(a.XMLRECORD,'/row/c206[position()=1]') "SAM_ID_HIST"
,extractValue(a.XMLRECORD,'/row/c207[position()=1]') "PARENT_ACCOUNT"
,extractValue(a.XMLRECORD,'/row/c208[position()=1]') "MULTI_CURRENCY"
,extractValue(a.XMLRECORD,'/row/c209[position()=1]') "EXTERNAL_POSTING"
,extractValue(a.XMLRECORD,'/row/c210[position()=1]') "PARENT_BV_DATE"
,extractValue(a.XMLRECORD,'/row/c211[position()=1]') "ALLOWED_BV_DATE"
,extractValue(a.XMLRECORD,'/row/c212[position()=1]') "SECONDARY_LIMIT_AMT"
,extractValue(a.XMLRECORD,'/row/c213[position()=1]') "CREDIT_CHK_TXN_TYPE"
,extractValue(a.XMLRECORD,'/row/c214[position()=1]') "CREDIT_CHK_CONDITION"
,extractValue(a.XMLRECORD,'/row/c215[position()=1]') "BALANCE_CONVERSION_MKT"
,extractValue(a.XMLRECORD,'/row/c216[position()=1]') "LIMIT_KEY"
,extractValue(a.XMLRECORD,'/row/c217[position()=1]') "REDUCING_LIMIT"
,extractValue(a.XMLRECORD,'/row/c218[position()=1]') "LIMIT_PROC_TYPE"
,extractValue(a.XMLRECORD,'/row/c219[position()=1]') "FA_STATUS"
,extractValue(a.XMLRECORD,'/row/c220[position()=1]') "EMERGENCY_BLOCK"
,extractValue(a.XMLRECORD,'/row/c221[position()=1]') "EMERGENCY_REASON"
,extractValue(a.XMLRECORD,'/row/c222[position()=1]') "RISK_STAGE"
,extractValue(a.XMLRECORD,'/row/c223[position()=1]') "RESERVED_23"
,extractValue(a.XMLRECORD,'/row/c224[position()=1]') "RESERVED_22"
,extractValue(a.XMLRECORD,'/row/c225[position()=1]') "RESERVED_21"
,extractValue(a.XMLRECORD,'/row/c226[position()=1]') "RESERVED_20"
,extractValue(a.XMLRECORD,'/row/c227[position()=1]') "RESERVED_19"
,extractValue(a.XMLRECORD,'/row/c228[position()=1]') "RESERVED_18"
,extractValue(a.XMLRECORD,'/row/c229[position()=1]') "RESERVED_17"
,extractValue(a.XMLRECORD,'/row/c230[position()=1]') "RESERVED_16"
,extractValue(a.XMLRECORD,'/row/c231[position()=1]') "RESERVED_15"
,extractValue(a.XMLRECORD,'/row/c232[position()=1]') "RESERVED_14"
,extractValue(a.XMLRECORD,'/row/c233[position()=1]') "RESERVED_13"
,extractValue(a.XMLRECORD,'/row/c234[position()=1]') "RESERVED_12"
,extractValue(a.XMLRECORD,'/row/c235[position()=1]') "RESERVED_11"
,extractValue(a.XMLRECORD,'/row/c236[position()=1]') "RESERVED_10"
,extractValue(a.XMLRECORD,'/row/c237[position()=1]') "RESERVED_9"
,extractValue(a.XMLRECORD,'/row/c238[position()=1]') "RESERVED_8"
,extractValue(a.XMLRECORD,'/row/c239[position()=1]') "RESERVED_7"
,extractValue(a.XMLRECORD,'/row/c240[position()=1]') "RESERVED_6"
,extractValue(a.XMLRECORD,'/row/c241[position()=1]') "RESERVED_5"
,extractValue(a.XMLRECORD,'/row/c242[position()=1]') "RESERVED_4"
,extractValue(a.XMLRECORD,'/row/c243[position()=1]') "RESERVED_3"
,extractValue(a.XMLRECORD,'/row/c244[position()=1]') "RESERVED_2"
,extractValue(a.XMLRECORD,'/row/c245[position()=1]') "RESERVED_1"
,extractValue(a.XMLRECORD,'/row/c246[position()=1]') "OVERRIDE"
,extract(a.XMLRECORD,'/row/c246') "OVERRIDE_246"
,extractValue(a.XMLRECORD,'/row/c247[position()=1]') "RECORD_STATUS"
,extractValue(a.XMLRECORD,'/row/c248[position()=1]') "CURR_NO"
,extractValue(a.XMLRECORD,'/row/c249[position()=1]') "INPUTTER"
,extract(a.XMLRECORD,'/row/c249') "INPUTTER_249"
,extractValue(a.XMLRECORD,'/row/c250[position()=1]') "DATE_TIME"
,extract(a.XMLRECORD,'/row/c250') "DATE_TIME_250"
,extractValue(a.XMLRECORD,'/row/c251[position()=1]') "AUTHORISER"
,extractValue(a.XMLRECORD,'/row/c252[position()=1]') "CO_CODE"
,extractValue(a.XMLRECORD,'/row/c253[position()=1]') "DEPT_CODE"
,extractValue(a.XMLRECORD,'/row/c254[position()=1]') "AUDITOR_CODE"
,extractValue(a.XMLRECORD,'/row/c255[position()=1]') "AUDIT_DATE_TIME"
FROM
"FHO1_ACCOUNT" a
LEFT OUTER JOIN "FHO1_ACCT_ENT_TODAY" c
ON c.RECID = a.RECID
LEFT OUTER JOIN "FBNK_CUSTOMER" b
ON b.RECID = extractValue(a.XMLRECORD,'/row/c1[position()=1]')
LEFT OUTER JOIN "FBNK_CURRENCY" d
ON d.RECID = extractValue(a.XMLRECORD,'/row/c8[position()=1]')
WHERE extractValue(a.XMLRECORD,'/row/c1[position()=1]') = 15990
;

You might also like